Datatable Select Method and a Listbox - How to filter the dt with listbox items?

The DataTable.Select method returns an array of DataRow objects which match the specified filter. Below is an example.

    Dim dataRows() as DataRow = MyDataTable.Select("FieldName = '" & ListBox1.SelectedValue & "'")

The returns an array of DataRows where the field 'FieldName' equals the selected value of the listbox.

Wyane

Another option is to use the DataView

dTable.DefaultView.RowFilter = "your filter string"

For i As Integer = 0 to dTable.DefaultView.Count - 1
      Messagebox.Show dTable.DefaultView.Item(i).Item(1)
Next

Hey Wyane,

Are you suggesting a literal array?  I have the following code.  Do I go from here to a new datatable which then allows me to populate a dataGridView?  A dataGridView is my final goal.

Jon

PartNumberList.SelectionMode = SelectionMode.MultiExtended

Dim x As Integer
' Loop through all items the ListBox.
For x = 0 To PartNumberList.Items.Count - 1
     PartNumberList.SetSelected(x, True)
Next x


Dim dataRows() As DataRow = dtPart.Select("partnum = '" & PartNumberList.SelectedValue & "'")

Select allOpen in new window

parts.jpeg

Even though your query is not directed at me, using the DataView would be easier if you want to display the filtered results in the grid. So

dTable.DefaultView.RowFilter = "your filter string"
datagridview1.DataSource = dTable.DefaultView

Hey Code Cruiser,

Fair enough, but what would my filter string look like?  Would it be "PartNumberList.SelectedValue" precisely?

Jon

The filter string will be same as the one you were using in select method

dTable.DefaultView.RowFilter = "partnum = '" & PartNumberList.SelectedValue & "'"

So what I'm I missing? I'm generating columns that look like this image.

dtPart.DefaultView.RowFilter = "partnum = '" & PartNumberList.SelectedValue & "'"
            dvgOriginalAssy.DataSource = dtPart.DefaultView

            If Not dtPart Is Nothing Then dtPart.Dispose()

            dtPart = New DataTable
            dtPart.Columns.Add("partnum")
            dtPart.Columns.Add("partdescription")
            dtPart.Columns.Add("PartOpr")
            dtPart.Columns.Add("mtlpartnum1")
            dtPart.Columns.Add("mtlpartdescription1")
            dtPart.Columns.Add("mtlsearchword1")
            dtPart.Columns.Add("partDesigner")
            dtPart.Columns.Add("partVendor")


            dvgOriginalAssy.DataSource = dtPart
            dvgOriginalAssy.AutoGenerateColumns = True
            dvgOriginalAssy.ReadOnly = True

columns.jpeg

Why do you have this code in there?

            If Not dtPart Is Nothing Then dtPart.Dispose()

            dtPart = New DataTable
            dtPart.Columns.Add("partnum")
            dtPart.Columns.Add("partdescription")
            dtPart.Columns.Add("PartOpr")
            dtPart.Columns.Add("mtlpartnum1")
            dtPart.Columns.Add("mtlpartdescription1")
            dtPart.Columns.Add("mtlsearchword1")
            dtPart.Columns.Add("partDesigner")
            dtPart.Columns.Add("partVendor")


            dvgOriginalAssy.DataSource = dtPart
            dvgOriginalAssy.AutoGenerateColumns = True
            dvgOriginalAssy.ReadOnly = True






Let me mention that the RowFilter is applied on the datatable AFTER the datatable is configured and populated.

Obviously that code is already structured in dtPart.  That's gone now, a bad idea.

Here's my SQL string  query.

        dtPart = New DataTable
        daOriginalPart = New System.Data.OleDb.OleDbDataAdapter("SELECT partnum, partdescription, PartOpr, mtlpartnum1, mtlpartdescription1, mtlsearchword1, partVendor, partDesigner FROM Part", cnOriginalAssy)
        daOriginalPart.Fill(dtPart)

dtPart in filled on the form level. Next comes this:

dtPart.DefaultView.RowFilter = "partnum = '" & PartNumberList.SelectedValue & "'"
            dvgOriginalAssy.DataSource = dtPart.DefaultView

And you dont see any rows in the grid right? Does the part numbers match exactly? If you use that partnumber in a query directly, do you get any rows?

Also set a breakpoint and see what is being returned in PartNumberList.SelectedValue

The part number does match exactly.  I use the part number directly in the query and it showed up in the grid. And I can populate the entire dtPart table in the grid.  But I get nothing when I use PartNumberList.SelectedValue.  I tried "partnum = '" & PartNumberList.SelectedValue & "'" in a messagebox and I only got the "partnum =" portion.

?????  :-(

That is the problem. Try some other property such as SelectedItem

SelectedItem works fine - finally.  I've tried other properties to get multiple selections, but I can still only do one part from the list at a time. If I select the entire listbox I get nothing in the grid.  Anything obvious? I am getting closer.

To load the full grid when all the listbox items are selected, i think this is not the right approach.

Could a loop be used to add each selection to the grid?

May be but why not remove the filter if all the filter items are selected?

That worked very well my friend.  So it seems I can select 1 or all, but nothing in between.  Though I could use other types of filters. At any rate, it works and I get it. Thank you for be so patient and sticking with me.

Regards,
Jon

Glad to help :-)

The grid view is still showing the entire dtPart datatable.  But the list of items is only a portion of dtPart.  So I still have to equate the PartNumberList with the default view.

That would have taken me a long time solve.  But it works like a charm.  I'm not sure exactly what Partnum IN means.  I'm thinking you're creating a string, the parts in the listbox, that you want in dtPart.  This is a great learning experience for me.

Thanks again.  :-D

CodeCruiser is extremely thorough, knowledgeable, and responsive to my questions. This was a great help to me.

Glad to help :-)

The loop creates the filter string like this
partnum in('part1', 'part2', 'part3'...)


This is similar to partnum='part1' or partnum='part2'...