how to search DataTable for specific record?
Solution 1
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 ...
Solution 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.
Our Man in Bananas
I am a developer at a Non-Profit organization in London, UK. We mainly work in SQL Server, c#, web-services, and SharePoint, but love using Excel and Excel VBA as well as tinkering with XML/XSLT when the opportunity arises Matt Gemmell: What have you tried? Rubber Duck Debugging How to debug small programs
Updated on June 29, 2020Comments
-
Our Man in Bananas almost 4 years
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