CODEWITHSUNDEEP
vb.netdatatablefindms-access-2010
user3850146
user3850146

Reputation: 97

Find Row in Access DataTable

I need to see if a row exists in a DataTable where a field in the table (plan_code) matches a variable (strPlanCode). If it finds a match, it then obtains the value from another field in that row; if not, it writes the row to an error list. The direction I started with was to set up a DataTable like this:

Using daProduct As New OleDbDataAdapter("SELECT * FROM [product]", con)
    Dim cbProduct = New OleDbCommandBuilder(daProduct)
    cbExtract.QuotePrefix = "["
    cbExtract.QuoteSuffix = "]"
    Dim dtProduct = New DataTable
    daProduct.Fill(dtProduct)

But no methods from there seem to work and I'm wondering if I shouldn't have gone down the DataAdapter/DataTable path.

Some of the ones I've tried are:

strSearch = "plan_code = " & strPlanCode
intProdRow = dtProduct.Select(strSearch)

and

intProdRow = dtProduct.SelectCommand(strSearch)

But none of these get a result and/or will compile.

The old code, which used ODBC to connect to an SQL Anywhere DB, looks like this:

ls_command = "select * from product"
selectCMD = New OdbcCommand(ls_command, connectDB) 
selectCMD.CommandTimeout = 30 
productDA.SelectCommand = selectCMD
productDA.Fill(clp_valDS, "product")
porductTBL = clp_valDS.Tables("product") 
productTBL.PrimaryKey = New DataColumn() {productTBL.Columns("plan_code")}

productDR = productTBL.Rows.Find(ls_plan_code)
If (productDR Is Nothing) Then
    ls_error_message = "Plan Code " + ls_plan_code + " not found"
    GoTo ErrorHandler
Else
    ls_secondary_guar = productDR("secondary_guar")
End If

Upvotes: 0

Views: 138

Answers (1)

Wizengamot
Wizengamot

Reputation: 150

I would use the following approach: 

For Each row As System.Data.DataRow In dtProduct.Rows
   if not row.item("plan_code") is DBNull.Value then
      If row.Item("plan_code").ToString = strPlanCode Then
         'do what you want with the matching row.
      End If
   end if
Next

Upvotes: 1

Related Questions