Multiple search fields in Datatable

[bchaney]

is there a way i can get my datatable to have multiple constraints that are attached to a unique id? an example of what i'm talking about is if i have a unique machine but it's on many lines, can i show that somehow in a datatable? the reason i'm asking is that i have code that sorts the data by line number and shows the relevant machines in a combo box based on the line selection out of another combo box. i'm having to input the data into the datatable (as a starting point for the data base) and it's very tedious. i'm entering machines that have the same name in multiple rows because the line number is different. i can't figure out how i can add multliple values into my line number column so that it will sort properly when queried.

an example of what i'm asking with regard to the datatable is as follows:
Machine LineNumber
slicer Line 1, Line 2, Line 4
conveyor belt Line 1, Line 6

So that when called to fill the combo box, the correct one is called.

The code i'm using to sort right now is as follows:

Private Sub linecbo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles linecbo.SelectedIndexChanged
        Dim cline As String
        Dim mycon As SqlCeConnection
        Dim mycmd As New SqlCeCommand
        Dim myDA As New SqlCeDataAdapter
        Dim myDS As New DataSet
        Dim myDT As New DataTable

        machinecbo.Enabled = True
        cline = linecbo.SelectedValue
        mycon = GetConnect()
        mycon.Open()
        mycmd = mycon.CreateCommand

        mycmd.CommandText = "SELECT Machine, LineNumber FROM machine WHERE LineNumber = '" & Trim(cline) & "' ORDER BY Machine"
        myDA.SelectCommand = mycmd
        myDA.Fill(myDS, "machine")
        myDT = myDS.Tables("machine")

        machinecbo.DataSource = myDT
        machinecbo.DisplayMember = "Machine"
        machinecbo.ValueMember = "LineNumber"
        machinecbo.SelectedIndex = -1

    End Sub

any help with this will be greatly appreciated. i've tried multiple things and i can't seem to get it right.

Thanks

[bchaney]

if this will help in resolving this thread, i've redone my datatable that is for this problem. i have the machine names in the rows and for columns i have all the areas the machines could possibly be in. All of the fields where they intersect i made to be bit fields (true or false). so i modified my select string to look at a variable where statement which i'm trying to set equal to the string "True". it's not working. i'm thinking that my select string is wrong. Can anyone take a look at it and see if this is what is causing my problem now?

Private Sub linecbo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles linecbo.SelectedIndexChanged
        Dim cline As String
        Dim mycon As SqlCeConnection
        Dim mycmd As New SqlCeCommand
        Dim myDA As New SqlCeDataAdapter
        Dim myDS As New DataSet
        Dim myDT As New DataTable

        machinecbo.Enabled = True
        cline = linecbo.SelectedValue
        mycon = GetConnect()
        mycon.Open()
        mycmd = mycon.CreateCommand

        mycmd.CommandText = [B]"SELECT Machine FROM machine WHERE '" & Trim(cline) & "' = '" & "'True'" & "' ORDER BY Machine"[/B]
        myDA.SelectCommand = mycmd
        myDA.Fill(myDS, "machine")
        myDT = myDS.Tables("machine")

        machinecbo.DataSource = myDT
        machinecbo.DisplayMember = "Machine"
        machinecbo.ValueMember = "Machine"
        machinecbo.SelectedIndex = -1

    End Sub

[bchaney]

Can anyone please check my code? i've made these following changes and it seems to be working. i just need to know if there are any unforseen problems with the way i wrote my code. i had to encapsulate the entire routine in an if statement because on form close if the user didn't change the linecbo index then the select string becomes nothing which is kinda hard for the myDA to fill with.

Here are my changes:

Private Sub linecbo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles linecbo.SelectedIndexChanged
        Dim cline As String
        Dim mycon As SqlCeConnection
        Dim mycmd As New SqlCeCommand
        Dim myDA As New SqlCeDataAdapter
        Dim myDS As New DataSet
        Dim myDT As New DataTable
        
        If linecbo.SelectedIndex <> -1 Then
            machinecbo.Enabled = True
            cline = linecbo.SelectedValue
            mycon = GetConnect()
            mycon.Open()
            mycmd = mycon.CreateCommand

            mycmd.CommandText = "SELECT Machine, [" & cline & "] FROM machine WHERE [" & cline & "] = 'True' ORDER BY Machine"
            myDA.SelectCommand = mycmd
            myDA.Fill(myDS, "machine")
            myDT = myDS.Tables("machine")

            machinecbo.DataSource = myDT
            machinecbo.DisplayMember = "Machine"
            machinecbo.ValueMember = "Machine"
            machinecbo.SelectedIndex = -1
        End If

    End Sub

Can someone look over this and see if it looks sound?

Thanks

[bchaney]

The above works. I've tested it and ran the application.