asked on

The datatable.Rows.Contains() method does not find primary key

The datatable.Rows.Contains() method does not find the primary key value in the datatable and it definitely exists in the table.
I populated a datatable with a single column of values using a datareader, then set the PrimaryKey to column(0), the only column in the DataTable. The values are there, for sure (64 rows X 1 column), in the datatable, but the statement

If dtProdImportFields.Rows.Contains("ProductName") Then
'do something
End If

does not work at all (the Contains function completely ignores the value passed to it Or I am not using it correctly). The string "ProductName" is in the datatable and it is the PrimaryKey column and all values are unique, so I am stumped as to what may be going on.

Attached, is the code that generates the datatable (I think the problem may be that the PrimaryKey is somehow not being generated - though there are no errors)

Any ideas where I'm going wrong here?

Protected Sub GetProdImportFields(ByRef dtbl As DataTable)
        Dim sqlstring As String = "SELECT SSFieldName FROM ProdImportFields"
        Using connection As New SqlConnection(DBconnstr)
            connection.Open()
            Using command As New SqlCommand(sqlstring, connection)
                Using reader As SqlDataReader = command.ExecuteReader()
                    dtbl.Load(reader, LoadOption.OverwriteChanges)
                End Using
            End Using
        End Using
        dtbl.PrimaryKey = New DataColumn() {dtbl.Columns(0)} 'this appears correct
    End Sub

Open in new window

VBRocks

Use the Find method instead:

Dim value as String = "ProductName" 'Set this to the value to search for

Dim row as DataRow = dtProdImportFields.Rows.Find(value)

If row IsNot Nothing Then
'do something
End If

VBRocks

Here's an example:

Dim table As New DataTable()
table.Columns.Add("Col1")
table.PrimaryKey = New DataColumn() {table.Columns("Col1")}

'Add rows
For i As Int16 = 1 To 25
table.Rows.Add(i)
Next

'Search Col1 for a value of "15"
Dim row As DataRow = table.Rows.Find("15")
If row IsNot Nothing Then
MsgBox(row.Item("Col1") & " found")
End If

table.Dispose()

bd9000

ASKER

That does not work either. I think there is something wrong with the way I set the primary key

VBRocks

Try my example... Does it work? (I know it does)

And it looks like you are setting the Primary Key correctly.

A question I have is, Is "ProductName" a value that is in a row, or is that a Column you are trying to
search?

bd9000

ASKER

I tried it without success. Has to be something else.
I triple checked the datatable with the following loop:
Me.GetProdImportFields(Me.dtProdImportFields)
Dim rcPIF As Int16 = Me.dtProdImportFields.Rows.Count
Dim ccPIF As Int16 = Me.dtProdImportFields.Columns.Count
For Each drr As DataRow In Me.dtProdImportFields.Rows
For cc As Integer = 0 To ccPIF - 1
Dim cell As String = drr(cc).ToString
TextBox1.Text += cell & vbcrlf
Next
Next

The data is there, but neither Find nor Contains works at all (I've never used them before, so I thought there may be some trick to it, besides the requirement for a PrimaryKey). Puzzling, is that if I take away the PrimaryKey generation it lets me know during compile time that the table does not have one. Since there are no errors, I can't explain why it won't work.

bd9000

ASKER

"ProductName" is a value of a row (the first row, in this case)

ASKER CERTIFIED SOLUTION

VBRocks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bd9000

ASKER

Dim view As DataView = dtProdImportFields.DefaultView
view.RowFilter = "SSFieldName LIKE '%" & value & "%'"
If view.Count > 0 Then
MsgBox("Item found")
Else
MsgBox("Could not find item")
End If

This also works:
Dim view As DataView = dtProdImportFields.DefaultView
view.RowFilter = "SSFieldName ='" & value & "'"
If view.Count > 0 Then
MsgBox("Item found")
Else
MsgBox("Could not find item")
End If

Looks like a bug in the FW (the machine is running DotNet ver 3.5) is preventing Find & Contains from working.

VBRocks

Yes, you can look for an exact value using "=". Sometimes it's easier to use "LIKE", because it's
more flexible.

Glad you got it working!