Add a lot of criteria in query

[MAGICIANMERLIN]

Hi, i have a form which has a 1 datagrid and 12 label & textbox(like NAME, SURNAME, NATIONALITY, JOB etc..)and some buttons .I want to get data from database with one or more criteria (User if fill 2 criteria like NAME and SURNAME my query must be where NAME=txtname.text AND SURNAME=txtsurname.text) OR (user maybe fill only one criteria like NAME my query must be where NAME=txtname.text ) or (user maybe want to fill 8 or 6 or all criteria) and if user dont fill any criteria and click get button, query must be (Select * from ANABILGI) thats all..
I try to write a code below but unfortunately it was'nt worked.Would you somebody help me please.

Thanks a lot

PrivateSub withcriteria()

Dim param AsString = "ADI= txtad.Text & (param1) & SOYADI=txtsoyad.text & (param2)& BABAADI=txtbabaad.text & (param3)& ANAADI=txtanaad.text & (param4)& DYERI=txtdogumyer.text & (param5)& DTARIHI=txtdogumtar.text & (param6)& UYRUK=txtuyruk.text & (param7)& MESLEK=txtmeslek.text & (param8)& DIN=txtdin.text & (param9)& MEDENIHAL=txtmedenihal.text & (param10)& CINSIYET=txtcinsiyet.text & (param11)& TAHSIL=txttahsil.text"

Dim param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11 AsString

If txtad.Text.Length And txtsoyad.Text.Length And txtbabaad.Text.Length And txtanaad.Text.Length And txtdogumyer.Text.Length And txtdogumtar.Text.Length And txtuyruk.Text.Length And txtmeslek.Text.Length And txtdin.Text.Length And txtmedenihal.Text.Length And txtcinsiyet.Text.Length And txttahsil.Text.Length < 0 Then

Dim da AsNew OleDbDataAdapter("Select * from ANABILGI", baglan)

Dim ds AsNew DataSet

baglan.Open()

da.Fill(ds, "tablo")

DataGrid1.DataSource = ds.Tables("tablo")

baglan.Close()

DataGrid1.Select(DataGrid1.CurrentRowIndex)

Else

If txtad.Text.Length < 0 Then

param1 = "OR"

Else

param1 = "AND"

EndIf

If txtsoyad.Text.Length < 0 Then

param2 = "OR"

Else

param2 = "AND"

EndIf

If txtbabaad.Text.Length < 0 Then

param3 = "OR"

Else

param3 = "AND"

EndIf

If txtanaad.Text.Length < 0 Then

param4 = "OR"

Else

param4 = "AND"

EndIf

If txtdogumyer.Text.Length < 0 Then

param5 = "OR"

Else

param5 = "AND"

EndIf

If txtdogumtar.Text.Length < 0 Then

param6 = "OR"

Else

param6 = "AND"

EndIf

If txtuyruk.Text.Length < 0 Then

param7 = "OR"

Else

param7 = "AND"

EndIf

If txtmeslek.Text.Length < 0 Then

param8 = "OR"

Else

param8 = "AND"

EndIf

If txtdin.Text.Length < 0 Then

param9 = "OR"

Else

param9 = "AND"

EndIf

If txtmedenihal.Text.Length < 0 Then

param10 = "OR"

Else

param10 = "AND"

EndIf

If txtcinsiyet.Text.Length < 0 Then

param11 = "OR"

Else

param11 = "AND"

EndIf

EndIf

Dim da AsNew OleDbDataAdapter("Select * from ANABILGI where (param)", baglan)

Dim ds AsNew DataSet

baglan.Open()

da.Fill(ds, "tablo")

DataGrid1.DataSource = ds.Tables("tablo")

baglan.Close()

DataGrid1.Select(DataGrid1.CurrentRowIndex)

 

[jmcilhinney]

You have several problems.

1. It looks like you put the variable names into the string itself, so they will be taken as literals. You can't do this:

"field1 = txtField1.Text"

You need to do this:

"field1 = '" & txtField1.Text & "'"

2. You shouldn't be creating the whole string like that unless you specifically want to look for records that match the empty fields. You need to build it up bit by bit by checking whether each text box has a value entered and only add a condition for the corresponding field if it does, e.g.

If Me.TextBox1.Text <> String.Empty Then
	sqlString &= "field1 = '" & Me.TextBox1.Text & "'"
End If

3. You cannot randomly put AND and OR into your query and expect it to behave as you think it will. You have to put parentheses around certain blocks to specify the order of evaluation or else it will probably be quite different to what you expect. This:

field1 = 'value1' AND field2 = 'value2' OR field3 = 'value3' AND field4 = 'value4'

will actually be evaluated like this:

(field1 = 'value1' AND field2 = 'value2') OR (field3 = 'value3' AND field4 = 'value4')

which may not be what you want.

4. The way you are trying to use those param strings is all wrong, but if you follow the steps above then you won't need them anyway.