Locate records in a DataTable using Primary Key

[bruel1999]

Hey people,
I have a dataset called PP4DBDataSet and a table called Table1 within it. This table has a PK called 'Complaint code'. How do I search for and display records using the PK?
All help appreciated,
Thanks

[dday9]

Bind a BindingSource to the DataTable and the set the Filter property to where the Complaint code equals the value that you're searching. That would look something like this:

BindingSource.Filter = String.Format("complaint_code = '{0}'", <some value here>)

[jmcilhinney]

Bind a BindingSource to the DataTable and the set the Filter property to where the Complaint code equals the value that you're searching. That would look something like this:

BindingSource.Filter = String.Format("complaint_code = '{0}'", <some value here>)

Just keep in mind that that is going to hide every other record too, which may not be what you want. If you want the actual DataRow then you should call Rows.Find on the DataTable. You can also call Find on the BindingSource to get the index of the record, allowing for any sorting and filtering, and then assign that to the Position property. That will select the appropriate record without hiding any others.

[bruel1999]

And how can I display some fields of the record?

[bruel1999]

I used this to assign the row to a variable 'thisrow'

Dim thisrow As PP4DBDataSet.Table1Row
thisrow = PP4DBDataSet1.Table1.FindByComplaint_Code(textbox1.text)

textbox1 is the textbox where the user enters the PK whose record they want to view.
how do I display some fields of 'thisrow' record?
thanks

[bruel1999]

I used this to assign the row to a variable 'thisrow'

Dim thisrow As PP4DBDataSet.Table1Row
thisrow = PP4DBDataSet1.Table1.FindByComplaint_Code(textbox1.text)

textbox1 is the textbox where the user enters the PK whose record they want to view.
how do I display some fields of 'thisrow' record?
thanks

okay, this isn't working. sorry :P
It's returning a null value. why?

[jmcilhinney]

Is that table already bound to your UI?

[bruel1999]

yes.

[jmcilhinney]

yes.

In that case, I would recommend calling the Find method of the BindingSource. It's not as good as the method you're calling from the point of view that it requires you to specify the column by name as a String rather than the ID column being implicit in the method itself, but it's easier from the point of view that it will give you the index of the matching row, which you can then simply assign to the Position property of the BindingSource to make that row the Current. That said, if you know for a fact that the DataRows in the DataTable are in the same order as the DataRowViews in the BindingSource, i.e. you haven't applied any sorting or filtering, then you can use the DataRow to find its index in the DataTable and then assign that to the Position.

[jmcilhinney]

okay, this isn't working. sorry :P
It's returning a null value. why?

There is no row with that PK value.

[bruel1999]

There is no row with that PK value.

no, there is.

[jmcilhinney]

no, there is.

If there was a row in your DataTable with a Complaint_Code that matched the value you were providing then it would be returned by that method. If the method returns Nothing then there is no matching row.

[bruel1999]

In that case, I would recommend calling the Find method of the BindingSource.

Dim bs As New BindingSource
        bs.DataSource = PP4DBDataSet.Table1
        Dim found As Integer = bs.Find("Complaint Code", TextBox1.Text)
        MsgBox(found)

what am I doing wrong?

The messagebox is just to test whether the code is returning the right value, and it isn't. Whatever the text in textbox1 is, the value of found is always -1

[dday9]

It could be the fact that you have whitespace in your column name. Try wrapping the column name in square brackets:

bs.Find("[Complaint Code]", TextBox1.Text)

[bruel1999]

Nope. "DataMember property '[Complaint Code]' cannot be found on the DataSource."

[jmcilhinney]

Nope. "DataMember property '[Complaint Code]' cannot be found on the DataSource."

Then you have no such column. You need to check your table column names.

[bruel1999]

there is.

[jmcilhinney]

there is.

That looks rather like a screen shot of Access. That's irrelevant. When you call Find on a BindingSource, you're providing the name of a PropertyDescriptor. In this case, that maps to ColumnName of a DataColumn in the Columns property of the DataTable bound to your BindingSource. If you're getting that error message then there is no such column.

[bruel1999]

yeah, it is. The table which I've connected to m project.

[jmcilhinney]

yeah, it is.

No, it's not. If there was a column with that name then you wouldn't be getting that error message. Open your DataSet in the designer and take a screen shot of that and post it.

[jmcilhinney]

OK, I think I might know what the issue is. If you have bound the BindingSource in the designer then the DataSource will actually your DataSet rather than your DataTable. It may be that you need to qualify the column name with the table name in this particular case. I'll do a bit of testing and see what I can see but, if you want to try that in the mean time for yourself then you can, i.e. use something like:

myBindingSource.Find("TableName.ColumnName", value)

For future reference, I would strongly recommend NOT putting spaces in column names or the like. It's not the issue specifically in this case but it just makes life harder by adding confusion at least. Just use names like "ComplaintCode", exactly as you would in VB code.

[bruel1999]

myBindingSource.Find("TableName.ColumnName", value)

okay. I'll try this

[bruel1999]

Open your DataSet in the designer and take a screen shot of that and post it.

is this what you wanted?

[bruel1999]

well?