ListBoxes Select one list box as opposed to all listboxes

Hi Chill,

Many many thanks for your detailed reply.I can see the logic in your code but after several hours of attempting to re-arrange my code around your code I keep coming up with the following errors.

Error 1 Type 'OleDbType' is not defined.
Error 2 Local variable 'sql' is already declared in the current block.
Error 3 Type 'OleDbCommand' is not defined.
Error 8 Type 'OleDbDataAdapter' is not defined.

Here is the latest attempt at the code,I am not too sure if I should include my old sql string,but the times I have I get even more errors any hints much appreciated.

Imports System.Text


Public Class Form1


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As New OleDb.OleDbConnection

        Dim dbProvider As String
        Dim dbSource As String

        Dim ds As New DataSet

        Dim tables As DataTableCollection = ds.Tables

        Dim source1 As New BindingSource()

        Dim da As New OleDb.OleDbDataAdapter
        Dim tt As New OleDbType
        Dim sql As String



        Dim aa As String = authorList.Text

        Dim bb As String = publisherList.Text

        Dim cc As String = yearpublishedList.Text

        Dim dd As String = genreList.Text
        'Initialise a string builder with the first part of the query
        Dim sql As StringBuilder = New StringBuilder("SELECT * FROM books WHERE ")
        'This Boolean will be set to True if we have added a filter already
        Dim AndRequired As Boolean = False
        'We will be adding parameters to this Command
        Dim cmd As OleDbCommand = New OleDbCommand()

        cmd.Connection = con

        'Do we have anything in aa (authorList.Text)
        If aa.Length > 0 Then
            'Add the filter to the SQL
            sql.Append("author = ?")

            'Add the value into the parameter list
            cmd.Parameters.Add("@author", OleDbType.VarChar, 30).Value = aa
            'Change the numbers in the parameter to the sizes of your columns

            'Flag that we have already added a filter
            AndRequired = True
        End If

        'Next check ...
        If bb.Length > 0 Then
            'If we have already added a filter then we need the " AND "
            If AndRequired Then sql.Append(" AND ")
            'The rest is the same as for aa
            sql.Append("publisher = ?")
            cmd.Parameters.Add("@publisher", OleDbType.VarChar, 30).Value = bb
            AndRequired = True
        End If
        If cc.Length > 0 Then
            If AndRequired Then sql.Append(" AND ")
            sql.Append("yearpublished = ?")
            cmd.Parameters.Add("@yearpublished", OleDbType.VarChar, 4).Value = cc
            AndRequired = True
        End If
        If dd.Length > 0 Then
            If AndRequired Then sql.Append(" AND ")
            sql.Append("genre = ?")
            cmd.Parameters.Add("@genre", OleDbType.VarChar, 30).Value = dd
            AndRequired = True  'Not strictly necessary but you might want to add more stuff later so put it in anyway
        End If

        'Let the Command know the sql we've constructed - note the .ToString IS required
        cmd.CommandText = sql.ToString()
        da = New OleDbDataAdapter()
        da.SelectCommand = cmd
        da.Fill(ds, "Authors")


        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

        dbSource = "Data Source = C:\Documents and Settings\Administrator\Desktop\Authors.accdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()



        'sql = "SELECT * FROM books WHERE author = '" & aa & "' AND publisher = '" & bb & "' AND yearpublished = '" & cc & "' AND genre = '" & dd & "' "





        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "Authors")


        Dim view1 As New DataView(tables(0))

        source1.DataSource = view1

        DataGridView1.DataSource = view1

        DataGridView1.Refresh()











        con.Close()



    End Sub

End Class