Microsoft KB Archive/308070

Knowledge Base

How to implement a searchable DataGrid by using ADO.NET and Windows Forms

Article ID: 308070

Article Last Modified on 5/13/2007

---

APPLIES TO

• Microsoft Visual Basic .NET 2002 Standard Edition
• Microsoft Visual Basic .NET 2003 Standard Edition
• Microsoft .NET Framework 1.1 Service Pack 1
• Microsoft ADO.NET 1.1

---

This article was previously published under Q308070

For a Microsoft Visual Basic 6.0 version of this article, see 266654.

SUMMARY

This article demonstrates how to search for data in a Windows Form DataGrid control and synchronize the grid with the search results. In this sample, you programmatically retrieve data into a DataView class then bind the DataView to the DataGrid. The sample then allows users to type criteria in a text box and click Find to search for the record and move the pointer to the correct row in the DataGrid. This sample also indicates failure to locate a record.

Description of the Technique

Setting Sort Order and Finding Records

To use the DataView.Find method to find a record, you must set the sort order. In this example, when you click the DataGrid column header, the DataGrid sets the sort order for you. The sample uses syntax to set the sort order programmatically, which is very similar to the SQL ORDER BY syntax.

'Examples: Set the default sort order.
tblAuthors.DefaultView.Sort = "au_id"
tblAuthors.DefaultView.Sort = "au_id" vAuthors.Sort = "au_id DESC"
tblAuthors.DefaultView.Sort = "f_name,l_name"   ' multi-column sort/find
                

Users can also change the sort order visually by clicking column headers in the DataGrid. Thus, when users click Find, they use that column sort order to perform their search.

Note The arrow in the column header indicates the current sort order, and the direction of the arrow indicates whether the order is ascending or descending.

Retrieving Current Sort Order Programmatically

To retrieve the current sort order and update the Label text, read the DataView.Sort property as follows:

dv.Sort 

lblFind.Text = "Enter Search Criteria " & dv.Sort 'Used in this example to set out label.

Positioning the Record Pointer in a DataGrid

In this sample, you use the DataView.Find method to retrieve the record position and update the CurrencyManager object. This synchronizes the row pointer in the DataGrid. If you want to highlight the row beyond this, you can use the DataGrid.Select method.

This sample uses the CurrencyManager object to manipulate currency. You can also use the BindingManagerBase class, which is the base class for the CurrencyManager. All data binding is done through DataView classes. To synchronize your controls and the CurrencyManager, the controls must be bound to the same DataView.

'Search for the record in the DefaultView.
'If found, move the pointer to the correct record in the grid.
Dim i As Integer
i = dv.Find(txtFind.Text) ' Locates record in DefaultView.
                          ' Does not move grid pointer or CM.
If i > dv.Table.Rows.Count Or i < 0 Then
   MsgBox("Record Not found", MsgBoxStyle.Information, "Record Not Found")
Else
   'CM.Position = i  ' Synchronizes Cm and Grid Pointer.
End If

Create the Sample

1. Start Microsoft Visual Studio .NET.
2. Create a new Windows Application in Visual Basic .NET.
3. Place a DataGrid control, a Button control, a Label control, and a TextBox control on the form.
4. In the Properties window, set the properties for the controls as follows:

   Form Control
   Name: frmFind
   Size: 800, 520
   Text: Search Form
   
   Label Control
   Name: lblFind
   AutoSize: True
   Location: 168, 456
   Text: Enter Search Criteria au_id
   
   TextBox Control
   Name: txtFind
   Location: 328, 456
   Size: 216, 20
   Text:
   
   Button Control
   Name: btnFind
   Location: 560, 456
   Size: 75, 32
   Text: Find
   
   DataGrid1 Control
   Name: grdFind
   Location: 16, 8
   Size: 760,432

5. Use the Imports statement on the System.Data.SqlClient namespace so that you are not required to qualify declarations for this namespace later in your code. You must use this statement prior to Public Class frmFind and any other declarations. Add the following code to the frmFind declarations section:

   ' Create namespace(s)
   Imports System.Data.SqlClient

6. Add the following variable declarations to frmFind after Public Class frmFind and before the rest of the code:

   ' Declare variables
   Inherits System.Windows.Forms.Form
   ' Modify this string to correctly connect to your SQL Server.
   Dim con As New SqlConnection("server=YourServerName;uid=YourUserId;" & _
                                "pwd=YourPassword;database=pubs")
   Dim daAuthors As New SqlDataAdapter("Select * From Authors", con)
   Dim ds As New DataSet()
   Dim WithEvents dv As DataView
   Dim CM As CurrencyManager

7. Add the following code to the frmFind_Load event:

   ' Retrieve data from the Authors table.
   daAuthors.Fill(ds, "Authors")
   dv = New DataView(ds.Tables("Authors"))
   ' Bind the data to the DataGrid.
   grdFind.DataSource = dv
   'Set the default sort order.
   dv.Sort = "au_id"
   ' Initialize CurrencyManager to hold an instance of the form's CurrencyManager.
   CM = frmFind.BindingContext(dv)

8. Add the following code to the btnFind_Click event:

   ' Verify that the user typed test to search for.
   If txtFind.Text = "" Then
      MsgBox("Please enter some criteria to find.")
      txtFind.Focus()
   Else
      'Search for the record in the DefaultView.
      'If found, move the pointer to the correct record in the grid.
      Dim i As Integer
      i = dv.Find(txtFind.Text) ' Locates record in DefaultView.
                                ' Does not move grid pointer or CM.
      If i > dv.Table.Rows.Count Or i < 0 Then
         MsgBox("Record Not found", MsgBoxStyle.Information, "Record Not Found")
      Else
         CM.Position = i  ' Synchronizes Cm and Grid Pointer.
      End If
   End If

9. Add the following code to the dv_ListChanged event:

   ' Strip the DESC off the dv.Sort string if the user has set the order to descending.
   ' This allows the Label2.Text to be properly displayed.
   If dv.Sort.Substring((Len(dv.Sort) - 4), 4) = "DESC" Then
      lblFind.Text = "Enter Search Criteria " & dv.Sort.Substring(0, Len(dv.Sort) - 5)
   Else
      lblFind.Text = "Enter Search Criteria " & dv.Sort
   End If

10. In Solution Explorer, select the project to set frmFind as the startup form.
    
    Note By default, the project is named WindowsApplication1. To rename the form, click Properties on the Project menu. In the Startup Object drop-down list box, click frmFind, and then click OK to save your changes.
11. Save and run the application.
12. Click the DataGrid header to change the sort order. Type criteria in the text box, and then click Find. Notice that the record pointer is correctly positioned in the DataGrid.

REFERENCES

For more information about ADO.NET, the DataSet object, the methods in this article, and SQL, visit the following Microsoft Web sites:

ADO.NET for the ADO Programmer
http://msdn2.microsoft.com/en-us/library/ms973217.aspx

BindingManagerBase Class
http://msdn2.microsoft.com/en-us/library/system.windows.forms.bindingmanagerbase(vs.71).aspx

CurrencyManager Class
http://msdn2.microsoft.com/en-us/library/system.windows.forms.currencymanager(vs.71).aspx

PropertyManager Class
http://msdn2.microsoft.com/en-us/library/system.windows.forms.propertymanager(vs.71).aspx

DataView Class
http://msdn2.microsoft.com/en-us/library/system.data.dataview(vs.71).aspx

MSDN Online .NET Developer Center
http://msdn.microsoft.com/net

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

168336 How to open ADO Connection and Recordset objects

Keywords: kbhowtomaster KB308070

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.