HOW TO: Locate a Specified DataRow by Using the Find Method of DataRowCollection in Visual Basic .NET (305561)

back to the top

Additional Information About the Find Method

The Find method queries the primary key column of the DataTable, and locates the record that exactly matches the value that is passed in the search criteria. If the DataTable does not have a primary key, a MissingPrimaryKeyException is raised. The Find method has the following limitations:

• The Find method is not case sensitive.
• You cannot use Wildcard characters in the Find method. For example, when you use the following code, you may not find all the rows starting with Ch. Instead, you receive the row with the primary key value Ch%.

          myDataRow = myDataTable.Rows.Find("Ch%")
  

The Find method returns a DataRow that matches the search criteria. When no match is found, an empty DataRow object is returned. Using Is Nothing, you can detect the results of the Find method.

back to the top

Sample Code

1. Create a new Visual Basic .NET Console Application project. By default, Module1.vb is created.
2. Replace the existing code in Module1.vb with the following code:

   Imports System.Data
   Imports System.Data.SqlClient
   
   Module Module1
   
      Sub Main()
         ' Open connection.
         Dim cn As SqlConnection
         cn = New SqlConnection("User ID=sa;Initial Catalog=pubs;Data Source=mySQLServerName")
   
         ' Create DataAdaptor.
         Dim da As SqlDataAdapter
         da = New SqlDataAdapter("Select * from Employee", cn)
   
         ' Create a Dataset.
         Dim ds As DataSet
         ds = New DataSet()
   
         ' Get Schema such as primary key from the database.
         da.FillSchema(ds, SchemaType.Mapped, "Employee")
   
         'Get the data from database.
         da.Fill(ds, "Employee")
   
         ' DataRow to collect result of Find method.
         Dim dr As DataRow
   						
         ' Find Employee with specified ID.
         Console.WriteLine("Searching for Employee ID = KFJ64308F")
         dr = ds.Tables("Employee").Rows.Find("KFJ64308F")
         ' Check whether the search returned any result.
         If Not (dr Is Nothing) Then
            Console.WriteLine("First Name is : {0}", dr("fname"))
         Else
            Console.WriteLine("No Matching Rows Found.")
         End If
   
         ' Try to use wildcard character.
         Console.WriteLine("Searching for all Employees with Employee ID starting with K (Wildcard K%)")
         dr = ds.Tables("Employee").Rows.Find("K%")
         ' Check whether the search returned any result.
         If Not (dr Is Nothing) Then
            Console.WriteLine("First Name is : {0}", dr("fname"))
         Else
            Console.WriteLine("No Matching Rows found with specified search criteria.")
         End If
         Console.ReadLine()
      End Sub
   
   End Module
   

3. Modify the connection string as appropriate for your environment.
4. On the Debug menu, click Start to run the application.

back to the top

Troubleshoot

The MissingPrimaryKeyException exception is thrown when you try to access a row in a table that does not have a primary key column.

For more information about MissingPrimaryKeyException, see the following Microsoft Developer Network (MSDN) Web site:back to the top