how to search DataTable for specific record?

Question

Hi,

I have a windows form with 10 text fields and 1 combobox.

When the user selects a record in the combo-box I want to find that record in my form datatable variable (called dtBranches) then populate my 10 textfields from the datarow.

I tried this:

Dim dr As System.Data.DataRow
If mSortCode > 0 Then
    dr = dtBranches.Select("SortCode='" & mSortCode & "'")
    Me.txtBranch.Text = dr("Branch").ToString()
    Me.txtBankName.Text = dr("BankName").ToString()
    Me.txtBranchTitle.Text = dr("BranchTitle").ToString()
    Me.txtReference.Text = dr("Ref").ToString
    Me.txtAddr1.Text = dr("Address1").ToString
    Me.txtAddr2.Text = dr("Address2").ToString
    Me.txtAddr3.Text = dr("Address3").ToString
    Me.txtPostCode.Text = dr("PostCode").ToString
    Me.txtTelNo.Text = dr("TelephoneNumber").ToString
    Me.txtTown.Text = dr("Town").ToString
    Me.txtTelNo.Text = dr("TelephoneNumber").ToString
end if

but can't get it to compile...

What's the correct and best way to do this please?

thanks

Philip

score 11 · Accepted Answer · answered Jun 28 '13 at 14:14

11

DataTable.Select returns an array of DataRows. You need to declare an array to receive the result

Dim dr() As System.Data.DataRow

Of course then you need to check if you have rows returned and address the first row in the array

dr = dtBranches.Select("SortCode='" & mSortCode & "'")
If dr.Length > 0 Then
    Me.txtBranch.Text = dr(0)("Branch").ToString()
    Me.txtBankName.Text = dr(0)("BankName").ToString()
    ...... and so on ...

answered Jun 28 '13 at 14:14

Steve

213,761
22
232
286

thanks, it works, but the combo box using `dtBranches` is empty after running the `Select` command. How do I reset it so the combo box isn't affected? – Our Man in Bananas Jun 28 '13 at 15:12
This code should not affect the combo in any way. Using Select on the dtBranches doesn't change the content of the table. Are you sure that there is not other code that changes the dtBranches table content? – Steve Jun 28 '13 at 15:17
thanks, I was using `Reset` at the end of my populate code to remove the filter - now I am doing **dtBranches.Select("")** – Our Man in Bananas Jun 28 '13 at 15:33

score 2 · Answer 2 · answered Jun 28 '13 at 14:23

2

I would use Linq-ToDataSet and the strongly typed Field method instead:

Dim matches = From row In dtBranches
              Let SortCode = row.Field(Of String)("SortCode")
              Where SortCode = mSortCode
If matches.Any() Then
    Dim row = matches.First()
    Me.txtBranch.Text = row.Field(Of String)("Branch")
    Me.txtBankName.Text = row.Field(Of String)("BankName")
    Me.txtBranchTitle.Text = row.Field(Of String)("BranchTitle")
    Me.txtReference.Text = row.Field(Of String)("Ref")
    Me.txtAddr1.Text = row.Field(Of String)("Address1")
    Me.txtAddr2.Text = row.Field(Of String)("Address2")
    Me.txtAddr3.Text = row.Field(Of String)("Address3")
    Me.txtPostCode.Text = row.Field(Of String)("PostCode")
    Me.txtTelNo.Text = row.Field(Of String)("TelephoneNumber")
    Me.txtTown.Text = row.Field(Of String)("Town")
Else
    MesageBox.Show("SortCode not found.")
End If

If you want to compare case-insensitively, replace the Where above with:

Where StringComparer.OrdinalIgnoreCase.Equals(SortCode, mSortCode)

By the way, you are assigning the telephone number twice.

answered Jun 28 '13 at 14:23

Tim Schmelter

450,073
74
686
939

@Time: Thanks for your help, but how do I get the first line to compile - it says 'Where' variable is not decalared and 'row' variable is not declared. I am using .NET 3.5 in VB.NET Winforms – Our Man in Bananas Jun 28 '13 at 14:41
@Time: Thanks for your help, but (after adding correct references to **System.Data.DataSetExtensions **) I get this error `Public member 'Any' on type 'EnumerableRowCollection(Of VB$AnonymousType_0(Of DataRow,String))' not found.` on the line `If matches.Any()` ... how can I fix that please – Our Man in Bananas Jun 28 '13 at 14:57
1

You have to add `Using System.Linq`, if you have upgraded to 3.5 you might have to add the reference to the `System.Core.dll`. – Tim Schmelter Jun 28 '13 at 15:03
I added the reference, and in my procedure I put `Using System.Linq`. At the end of my procedure, I put `End Using`. But I get a compile error **System.Linq is a namespace and cannot be used as an expression**. How do I resolve that please? – Our Man in Bananas Jun 28 '13 at 15:18
1

You dont have to add the using in your method which is used to dispose objects. Sorry, I have mixed C# and VB.NET here. In VB it is Imports System.Linq at the Top of the file. – Tim Schmelter Jun 28 '13 at 16:09

how to search DataTable for specific record?

2 Answers2