• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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

My datatable is filled and I have a listbox of part numbers.  I'd like to select rows from the datatable that correspond to my part number list only.  I'm assuming Datatable select method is the best approach, but I'm not sure where to start.

Thanks,
H
0
hollertrek51
Asked:
hollertrek51
  • 12
  • 11
1 Solution
 
Wayne Taylor (webtubbs)AstronautCommented:
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
0
 
CodeCruiserCommented:
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
0
 
hollertrek51Author Commented:
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 & "'")

Open in new window

parts.jpeg
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
CodeCruiserCommented:
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
0
 
hollertrek51Author Commented:
Hey Code Cruiser,

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

Jon
0
 
CodeCruiserCommented:
The filter string will be same as the one you were using in select method

dTable.DefaultView.RowFilter = "partnum = '" & PartNumberList.SelectedValue & "'"
0
 
hollertrek51Author Commented:
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
0
 
CodeCruiserCommented:
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.
0
 
hollertrek51Author Commented:
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
0
 
CodeCruiserCommented:
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?
0
 
CodeCruiserCommented:
Also set a breakpoint and see what is being returned in PartNumberList.SelectedValue
0
 
hollertrek51Author Commented:
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.

?????  :-(
0
 
CodeCruiserCommented:
That is the problem. Try some other property such as SelectedItem
0
 
hollertrek51Author Commented:
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.
0
 
CodeCruiserCommented:
To load the full grid when all the listbox items are selected, i think this is not the right approach.
0
 
hollertrek51Author Commented:
Could a loop be used to add each selection to the grid?
0
 
CodeCruiserCommented:
May be but why not remove the filter if all the filter items are selected?
0
 
hollertrek51Author Commented:
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
0
 
CodeCruiserCommented:
Glad to help :-)
0
 
hollertrek51Author Commented:
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.
0
 
CodeCruiserCommented:
Right. You can still filter the grid when more than one part number is selected but the filter would get quite long.

Dim Filter As String
If Listbox1.SelectedIndices.Count = 1 Then
   Filter = "PartNum = '" & listbox1.SelectedItem & "'"
ElseIf ListBox1.SelectedIndices.Count > 1 Then
   Filter = "Partnum IN ('"
   For i as integer = 0 to Listbox1.SelectedIndices.Count - 1
         Filter &= listbox1.Item(Listbox1.SelectedIndices(i)) & "', '"
   Next
   Filter = Filter.Trim("'").Trim().Trim(",").Trim()
   Filter &= ")"
End If
dTable.DefaultView.RowFilter = Filter
0
 
hollertrek51Author Commented:
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


0
 
hollertrek51Author Commented:
CodeCruiser is extremely thorough, knowledgeable, and responsive to my questions. This was a great help to me.
0
 
CodeCruiserCommented:
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'...
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now