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
Dim sql As StringBuilder = New StringBuilder("SELECT * FROM books WHERE ")
Dim AndRequired As Boolean = False
Dim cmd As OleDbCommand = New OleDbCommand()
cmd.Connection = con
If aa.Length > 0 Then
sql.Append("author = ?")
cmd.Parameters.Add("@author", OleDbType.VarChar, 30).Value = aa
AndRequired = True
End If
If bb.Length > 0 Then
If AndRequired Then sql.Append(" AND ")
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
End If
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()
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