creating an sql search from 2 comboboxes

[supersal666]

Hi does anyone know how to creating an sql search from 2 comboboxes in vb.net.

I have an access database with one table and need to be able to do a search depending on what the user selects in the 2 comboboxes then display the results in a listbox.

Has anyone got any similar code that would help

Thanks

 

[jmcilhinney]

You should create a command at the class level, e.g.:

Private connection As New OleDbConnection("connection string here")
Private command As New OleDbCommand("SELECT * FROM MyTable WHERE Column1 = @Column1 AND Column2 = @Column2", Me.connection)

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With Me.command.Parameters
.Add("@Column1", OleDbType.VarChar, 50)
.Add("@Column2", OleDbType.Integer)
End With
End Sub

Now you can simply set the parameter values and execute the query whenever you like, e.g.

With Me.command.Parameters
    .Item("@Column1").Value = Me.ComboBox1.SelectedValue
    .Item("@Column2").Value = Me.ComboBox2.SelectedValue
End With

Dim table As New DataTable

Me.connection.Open()

Using reader As OleDbDataReader = Me.command.ExecuteReader(CommandBehavior.CloseConnection)
    table.Load(reader)
End Using

 

[cjard]

in .net 2 you can have the ide create most of that donkeywork for you. read the dw2 link in my signature, section on "Creating a Form to Search Data"