Datagrid with search capabilities, how to implement it?

[Guest]

I have a data application that has been working for years on MS Access and VBA.
In my application there are several grids with search capabilities (based on
the ADO recordset.find method) which are provided by a separate form that
allows:
a) To type and/or select search criteria from a combo box
b) To select the field where the criteria will be searched
c) To select the type of match desired (the criteria could be found at the
begining of the field, in any part of the field or should match the whole
field). I use wildcards to build the search criteria. For example:
strCriteria = "[" & strField & "] Like '" & strCrit & "*'"
d) To find the first matching record (Find First). Regardless of the grid
sorting order, the cursor stops on the first matching record to the user's
eyes.
e) To find all other matching records (Find Next), using the ADO
recordset.bookmark property to set the starting record for the find action.

I am trying to upgrade my application to SQL Server 2005 and Visual basic
..Net 2005.

In my VB .Net 2005 application:
I have a datagridview with a datatable as datasource.
To reproduce the search capabilities:
I tried to use the datatable.defaultview.find method.
The problem here is that this dataview.find method does not support the
use of wildcards or searching from a position other than the begining of the
dataview.

I thought that the datatable.select method could help, and it does, but I
still find limitations:
I use the datatable.select method with wildcards in the search criteria to
get a datarow array of matching rows.
Then I can traverse the datarow array and, for each datarow in the array,
I retrieve the whole content of the searched column
I use this content with the datatable.dataview.find method to get the
index of the matching row.
I use a currencymanager to select the desired row in the datagridview.

The limitations with this approach are:
If the searched column is not a primary key, there could be several
datarows with exactly the same value in that column. In this case, the
datatable.dataview.find method will never get to the second or third matching
datarows, it will always stop at the first one. The Find Next action does not
work.
In order to use the datatable.dataview.find method I have to set the
datatable.dataview.sort property and in doing this I risk changing the
datagridview sorting to the eyes of the user, and I am sure the user will not
like it.

To solve the afore mentioned limitations I thought about retrieving the
primary key values for each row in the datarow array and use the primary
key(1) with the datatable.dataview.find method. Now the Find Next action
works fine. But...
I have to set the datatable.dataview.sort property to the primary key,
affecting the grid sorting to the eyes of the user.

After 4 or 5 days of searching, reading, trying and failing, I am running
out of ideas to solve this problem, can anyone help?

Thanks,

(1) I had to recreate the primary key, even though the table in the database
has it already defined. The datareader does not retrieve the primery key
property of the table. Should I use a dataset instead of a datareader?

 

[Cor Ligthert [MVP]]

Sergio,

Probably databinding will do the trick for you and than you are ready with
the inbuild functionality.

http://www.vb-tips.com/default.aspx?ID=5f4a0f68-a3b6-4fc8-8aff-587f730fa118

The sample is very simple just to see the approach.

If you by instance set the same datasource to a combobox and to a
datagridview, than the binding will forever select the same row.

Be aware that you use instead of the sample because of your sorting question
forever the datatable.defaultview or another from the datatable created new
dataview.

(I did not do this in this sample to keep it simple, probably I will change
this soon, because that I am writing this forever)

I hope this gives some idea's

Cor

 

[Guest]

Dear Cor,

Thank you for your answer, I tried your solution, but my problem still stands:

Your solution based on a shared databinding between the datagridview and the
combobox does not perform a real search, it just synchronizes both controls.
Besides, it leaves me still with two problems:

1) I cannot "search" for partial matches.
Let's say I have three rows that, in a given column of the datagridview,
have the following values:

0005698
1000569
1249357

If I wanted to find all rows having the string "000", your solution will
find only the first row and miss the second one. I would be able to find both
if I can search inside of the column for a string like '*000*', it is all
rows having anything to the left of the string '000' and anything to the
right of it, including the empty string in both sides.

2) I cannot find repeated values. If I have repeated values on the same
column in different rows of the datagridview (the column is not a primary
key), your solution will never find other row than the first one.

Let's say I have three rows that, in a given column of the datagridview,
have the following values:

0005698
1000569
0005698

If I wanted to fin all rows having the string "0005698", your solution will
never find row three, only the first one will be found. Even using the
combobox properties AutoCompleteMode and AutoCompleteSource, if the values
are far appart, they won't show in the combobox list .

If you can find a way to solve this, I would really appreciate it,

Thank you again,

 

[Cor Ligthert [MVP]]

Sergio,

You can find it, you can even do autocomplete in the combobox. However the
DataGrid as no multiselect. (As the Combobox has as well not, than you would
have to use the Listbox).

The search functionality you will have to build yourself in the last
control. (I have made for somebody once an autocomplete listbox. That sample
I can search for you if you want).

I hope that this gives some ideas

Cor

 

[Vasco Veiga [MS]]

Would using DataView.FindRows instead of DataView.FindRow help ?
That would return more than one DataViewRow.


A brute force (bad perf) solution (i'm not very familiar with the bindings
arch) could be:

private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable("t");

dt.Columns.Add("c1");
dt.Columns.Add("c2");

dt.PrimaryKey = new DataColumn[] { dt.Columns["c1"] };

dt.Rows.Add(new object[] { "abcd", "bcda" });
dt.Rows.Add(new object[] { "bbcd", "abcda" });
dt.Rows.Add(new object[] { "cbcd", "bcda" });
dt.Rows.Add(new object[] { "dbcd", "bacda" });

dataGridView1.DataSource = dt;
}

private void textBox1_TextChanged(object sender, EventArgs e)
{
SelectRows( ((TextBox)sender).Text );
}

private void SelectRows(string RowFilter)
{
DataTable dt = (DataTable)dataGridView1.DataSource;

try
{
DataRow[] rs = dt.Select(RowFilter);
if (rs.Length == 0)
return;

foreach (DataGridViewRow r in dataGridView1.SelectedRows)
r.Selected = false;

System.Diagnostics.Trace.WriteLine(rs.Length);

foreach( DataRow r in rs )
foreach (DataGridViewRow gr in dataGridView1.Rows)
if (gr.DataBoundItem != null &&
((DataRowView)gr.DataBoundItem).Row.Equals(r))
dataGridView1.Rows[gr.Index].Selected = true;
}
catch (Exception e)
{
System.Diagnostics.Trace.WriteLine(e.ToString());
}
}

-- VV [MS]

I have a data application that has been working for years on MS Access and
VBA.
In my application there are several grids with search capabilities (based
on
the ADO recordset.find method) which are provided by a separate form that
allows:
a) To type and/or select search criteria from a combo box
b) To select the field where the criteria will be searched
c) To select the type of match desired (the criteria could be found at the
begining of the field, in any part of the field or should match the whole
field). I use wildcards to build the search criteria. For example:
strCriteria = "[" & strField & "] Like '" & strCrit & "*'"
d) To find the first matching record (Find First). Regardless of the grid
sorting order, the cursor stops on the first matching record to the user's
eyes.
e) To find all other matching records (Find Next), using the ADO
recordset.bookmark property to set the starting record for the find
action.

I am trying to upgrade my application to SQL Server 2005 and Visual basic
.Net 2005.

In my VB .Net 2005 application:
I have a datagridview with a datatable as datasource.
To reproduce the search capabilities:
I tried to use the datatable.defaultview.find method.
The problem here is that this dataview.find method does not support the
use of wildcards or searching from a position other than the begining of
the
dataview.

I thought that the datatable.select method could help, and it does, but I
still find limitations:
I use the datatable.select method with wildcards in the search criteria to
get a datarow array of matching rows.
Then I can traverse the datarow array and, for each datarow in the array,
I retrieve the whole content of the searched column
I use this content with the datatable.dataview.find method to get the
index of the matching row.
I use a currencymanager to select the desired row in the datagridview.

The limitations with this approach are:
If the searched column is not a primary key, there could be several
datarows with exactly the same value in that column. In this case, the
datatable.dataview.find method will never get to the second or third
matching
datarows, it will always stop at the first one. The Find Next action does
not
work.
In order to use the datatable.dataview.find method I have to set the
datatable.dataview.sort property and in doing this I risk changing the
datagridview sorting to the eyes of the user, and I am sure the user will
not
like it.

To solve the afore mentioned limitations I thought about retrieving the
primary key values for each row in the datarow array and use the primary
key(1) with the datatable.dataview.find method. Now the Find Next action
works fine. But...
I have to set the datatable.dataview.sort property to the primary key,
affecting the grid sorting to the eyes of the user.

After 4 or 5 days of searching, reading, trying and failing, I am running
out of ideas to solve this problem, can anyone help?

Thanks,

(1) I had to recreate the primary key, even though the table in the
database
has it already defined. The datareader does not retrieve the primery key
property of the table. Should I use a dataset instead of a datareader?

 

[Cor Ligthert [MVP]]

Vasco,

I think that in this sample it is more important that you use a DataGridView
(Net 2.0) instead of a DataGrid. The DataGridView has a multiselect
property, the DataGrid not.

Just my addition

Cor

 

[Vasco Veiga [MS]]

Actually the sample would work almost as is for DataGrid as well. See [1]
for ways to find the
selected rows.
DataGrid supports multiple selection. It's always on (just press control and
select the rows) though there are ways to work around it [2]

--VV [MS]

[1] http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q775q
[2] http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q839q

 

[Guest]

Dear Cor and Vasco,

As I mentioned in my previous message, I already tried using the combobox
properties AutoCompleteMode and AutoCompleteSource and they don't help when
there are many rows between any two matches.

On the other hand, the brute force solution Vasco offers is not viable in my
case because some datagridviews (or datagrids) in my application have more
than 80 thousand rows.

I tried a new approach, but I am still having problems:

Now:
1) I apply the datatable.select method to get the foundRows() datarow array
of matching rows.

2) I use the datatable.rows.indexof property to get the index for each one
of the datarows in the matching datarow array and store them in an array of
integers. I am doing it in a not very orthodox way, but it gives me the
expected results:

Dim intRows() as integer
....
Redim intRows(foundRows.length -1)
For x As Integer = 0 To foundRows.Length - 1
intRows(x) = foundRows(0).Table.Rows.IndexOf(foundRows(x))
Next

3) I assign each index in intRows() to the use the currencymanager.position
property and I can get to each and every one of the matching rows without
changing the sorting of the grid.

This works fine when the datagridview.source.defaultview.sort property is ""
and when its value corresponds to the searched column.

4) I found a way to cover the case when the
datagridview.datasource.defaultview.sort property is set to a column other
than the searched one and having a system.string datatype in ascending order.
In this case I use the datatable.DefaultView.ToTable method to create a new
dataTable in the given order with just one column (the searched one). Then I
apply step 2.

5) When the sorting order is Descendent, I just fill the integer array in
reverse order:

For x As Integer = 0 To foundRows.Length - 1
intRows(x) = foundRows(0).Table.Rows.IndexOf(foundRows(foundRows.Length - 1
- x))
Next

But... The problem is now when the datagridview.datasource.defaultview.sort
property is set to a column with a numeric data type (or a system.string data
type whose actual values have only digits) and the search is made in a column
different from the sorting one.
In this case, I can get to all matching rows, but always as if the sort were
by the searched column (ascending or descending) and not by the numeric one.

Any ideas to solve this?

Thank you,

 

[Cor Ligthert [MVP]]

Sergio,

I did not completely investigate your problem, however if this is the route
you want to go, than would I eliminate the array by adding a column in the
datatable. (very easy and there will be nothing updated that does not
exist). That you can than use in the same way as you do it now. The
advantage will be that it is than as well in your dataview.

I hope this helps,

Cor

 

[Guest]

Cor and Vasco, thank you for your help. I Think I solved it! The binding
context idea you gave me was the key.

Before getting into details, I need your help with other problem:

I have the FindData form as part of my solution, but as a Class Library
(compiled into a dll). My goal is to keep the main exe as small as possible.
My new problem is that, when I call the dll it runs in its own memory
space... is there a way to make it to run in the main application memory
space? Please remember I am using VBasic .Net 2005.

The datagridview search capabilities problem and solution:
We have a datagridview in a form and want to implement search capabilities
for it.

We create a form FindData with the following controls:

Combobox cbWhere
Contains the list of fields of the datagridview.datasource

Combobox cbMatch
The list of options will vary depending on the data type of the field to
search.
For a string field it will contain three options
Start of field
Any part of field
Whole field
This explanation assumes always a string datatype field

Combobox -> cbWhat
Bound to a datatable where all unique typed criteria are stored

Button Find First
Button Find Next
Button Exit

How does it work?

The calling form (the one with the datagridview) uses a datareader to load
the data into a "GridSource" datatable.

It binds the datagridview to the GridSource datatable.

Sets the datatable.defaultdataview.Sort property to the "Order By" clause of
the SQL command that retrieves the data. The grid datasource will always have
a value for the sort property.

Creates a currencymanager bound to the same binding context of the
datagridview.datasource, using :
myCurrencyManager = CType(Me.BindingContext(GridSource), CurrencyManager)

Sends to the FindData form New() method:
ByRef, The datagridview.datasource
ByVal, the list of fields that can be searched (binary fields and non
visible fields are not passed).
ByRef, the currencymanager

The FindForm New() method:

Uses the list of fields string to fill the cbWhere combobox

Uses two class scoped variables to:
Point to the datagrid.datasource (a datatable variable)
Point to the currencymanager (a currencymanager variable)

When the user :
Chooses the field to search
Chooses the type of matching desired,
Types or selects the string to search,
And clicks on the Find First button:

The program:

Builds the string criteria using
Select Case cbMatch.Text.ToString

Case "Start of field"
strCriteria = "[" + cbWhere.Text.ToString + "] Like '" +
cbWhat.Text.ToString + "*'"

Case "Any part of field"
strCriteria = "[" + cbWhere.Text.ToString + "] Like '*" +
cbWhat.Text.ToString + "*'"

Case "Whole field"
strCriteria = "[" + cbWhere.Text.ToString + "] = '" +
cbWhat.Text.ToString + "'"

End Select

Uses the datatable.Defaultview.ToTable() method to create a new datatable
that has, physically, the same logic order of the original table:

SortTable = mytblGround.DefaultView.ToTable("SortTable", False,
strColumnName, cbWhere.Text.ToString)

Where "SortTable" is the name of the new datatable.
And "strColumnName, cbWhere.Text.ToString" are the names of the two fields
the new datatable will have (the sorting and the searched fields of the
original table).
If the sort property is set to the searched column, the new table will have
only that one field.

Uses the datatable.Select() method on the SortTable to get a datarow array
of matching rows, with the same sorting of the original table:

foundRows = sortTable.Select(strCriteria,
mytblGround.DefaultView.Sort.ToString)

Stores the matching row indexes in an array of integer, using :

ReDim intRows(foundRows.Length - 1)
For x As Integer = 0 To foundRows.Length - 1
intRows(x) = foundRows(0).Table.Rows.IndexOf(foundRows(x))
Next

Disposes SortTable

Sorts the array to ensure a top-bottom accessing order
Array.Sort(intRows)

Sets the currencymanager.Position property to the first row index. Because
of the shared binding context, the movement is done also in the datagridview.

Each time the user clicks the Find Next button, the currencymanager.Position
property is set to the next matching row index.

If the end of the array of integers is reached, the user receives a "No more
matches found" message.


--
Sergio Torres C.
(505) 897 2041
___________________
http://www.stcsys.com
___________________

 

[Cor Ligthert [MVP]]

Sergio,

If this DLL is to be meant as something you want to reuse than you can do
that.

The DLL that you create in Net is not an overlay on your windowsform
program. It is a class library that is used to build your exe.

Be aware that a Net exe is often small. The actual program code is in the
Net framework.

I hope that this gives an idea.

Cor

 

[Chris A]

I just came across the issue of the inability to do partial searches
using the DataView.find() method. (or through the
DataTable.DefaultView.Find()). I found a solution in copying the
DataGridview Source Table to another temporary DataTable and Truncated
the Column being searched to the length of the Search text. the Find()
method returns an integer of the index found. then use this on the
original DataTable/view.


Dim TempDT As DataTable = MainDT

For i = 0 To SDT.Rows.Count - 1
TempDT.Rows(i).Item(strCol) =
Left(MDT.Rows(i).Item.strCol).ToString, Len(txtFind.Text))
Next

i = TempDT.DefaultView.Find(txtFind.Text)

 

[Guest]

Chris,

Your solution works, but is partial.

What it the table is sorted on a column or columns different from the one
searched? Your solution does not guarantee to find the first ocurrence of the
searched text in the grid...

What if you want to find not the first, but all occurrences of the searched
text?

I think my solution, although more comples, still is valid and covers these
conditions...

Keep the good work...
--
Sergio Torres C.
(505) 897 2041
___________________
http://www.stcsys.com
___________________