Need help on searching using wildcards

[Wizard83]

Hi there i a newbie in vb.net programming and i having a problem implementing searching using wildcards in the database

i tried using the 1's from here
http://www.vbdotnetforums.com/showthread.php?t=442&highlight=wildcard

however i still facing problems in it.
It keep on giving errors which is

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

What should i do ? is there any samples which i can follow ?

 

[TPM]

Have you tried without the wildcards? (incase it's something else)
I'll look through your code if you post it.

TPM

 

[Wizard83]

Thank TPM,
I managed to solve the problem with wildcards
btw i have another questions

i am using a search which uses 5 combobox to search
i would like to know if there is any method to further enhance the code as if only 3 of the combobox of the 5 combobox is has been selected with values


This is my coding

Dim strSQL As String

strSQL = "SELECT PainType, AreaOfEffect, DurationPerDay, PainOccurance, TypeofSickness FROM Symptoms WHERE GeneralSymptoms1='" & search1 & "' AND GeneralSymptoms2='" & search2 & "' AND GeneralSymptoms3='" & search3 & "' AND GeneralSymptoms4='" & search4 & "' AND GeneralSymptoms5='" & search5 & "'"

Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cnn)

Try
cnn.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader
If reader.Read() Then
Me.TextBox1.Text = reader.GetString(0)
Me.TextBox2.Text = reader.GetString(1)
Me.TextBox3.Text = reader.GetString(2)
Me.TextBox4.Text = reader.GetString(3)
Me.TextBox5.Text = reader.GetString(4)
Me.GBResults.Visible = True
MsgBox("Sickness Found")
Else

Me.TextBox1.ResetText()
Me.TextBox2.ResetText()
Me.TextBox3.ResetText()
Me.TextBox4.ResetText()
Me.TextBox5.ResetText()
MsgBox("No such Sickness Data Found!")
End If
reader.Close()
Finally
Try
cnn.Close()
Catch : End Try
End Try

 

[TPM]

You could use an if statment like this:

If not combobox1.selectedindex = -1 then
strSQL += "GeneralSymptoms1='" & search1 & "'
end if

TPM

 

[Wizard83]

err how do i implement the full of it ?

just add it in front of my codes?

 

[Wizard83]

Anyway thankz TPM for the swift reply

 

[TPM]

Well after you DIM your strSQL string, you'll use the if's to work out which parameters your using to search by. So for each combo that's selected it'll add the select code to your strSQL string. Understand what I'm mean?

TPM

 

[Wizard83]

err not really

kind to explain thankz

 

[TPM]

Maybe if i show you that'll help :

[/color][/color]
[color=#0000ff]Dim[/color][size=2] strSQL [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]String[/color][/size]
[size=2][color=#0000ff]Dim First as boolean = true ' First keeps track of which to use - WHERE..  or  AND...
[/color][/size]
[size=2]strSQL = "SELECT PainType, AreaOfEffect, DurationPerDay, PainOccurance, TypeofSickness FROM Symptoms "[/size]
 
'if combo1 has a selection add the SQL where...
If not combobox1.selectedindex = -1 then   strSQL += "Where GeneralSymptoms1='" & search1 & "'
  [color=#0000ff]First [/color]= false
end if
 
'If combo2 has a selection add the SQL
If not combobox2.selectedindex = -1 then
  if [color=#0000ff]First [/color]then 
	strSQL +=" WHERE"
	[color=#0000ff]First [/color]= false
  Else
	strSQL += " AND"
  end if
  strSQL += " GeneralSymptoms2='" & search2 & "'
end if
 
etc...

 

[Wizard83]

wow thankz this code really helps

 

[Wizard83]

Hmm i found out there is a bug in the code...it works well on the first time of execution however when i add a clear button to clear all the values and rerun the search button it fails to work

my codes are:


Dim search1 As String = Me.CBsymp1.Text
Dim search2 As String = Me.CBsymp2.Text
Dim search3 As String = Me.CBsymp3.Text
Dim search4 As String = Me.CBsymp4.Text
Dim search5 As String = Me.CBsymp5.Text
Dim strSQL As String
Dim First As Boolean = True ' First keeps track of which to use - WHERE.. or AND...
strSQL = "SELECT GeneralSymptoms1, GeneralSymptoms2, GeneralSymptoms3, GeneralSymptoms4, GeneralSymptoms5, PainType, AreaOfEffect, DurationPerDay, PainOccurance, TypeofSickness FROM Symptoms "

If Not CBsymp1.SelectedIndex = -1 Then
strSQL += "Where GeneralSymptoms1='" & search1 & "'"
First = False
End If


If Not CBsymp2.SelectedIndex = -1 Then
If First Then
strSQL += " WHERE"
First = False
Else
strSQL += " AND"
End If
strSQL += " GeneralSymptoms2='" & search2 & "'"
End If

If Not CBsymp3.SelectedIndex = -1 Then
If First Then
strSQL += " WHERE"
First = False
Else
strSQL += " AND"
End If
strSQL += " GeneralSymptoms3='" & search3 & "'"
End If

If Not CBsymp4.SelectedIndex = -1 Then
If First Then
strSQL += " WHERE"
First = False
Else
strSQL += " AND"
End If
strSQL += " GeneralSymptoms4='" & search3 & "'"
End If

If Not CBsymp5.SelectedIndex = -1 Then
If First Then
strSQL += " WHERE"
First = False
Else
strSQL += " AND"
End If
strSQL += " GeneralSymptoms5='" & search3 & "'"
End If

Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cnn)
Try
cnn.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader
If reader.Read() Then

Me.CBsymp1.Text = reader.GetString(0)
Me.CBsymp2.Text = reader.GetString(1)
Me.CBsymp3.Text = reader.GetString(2)
Me.CBsymp4.Text = reader.GetString(3)
Me.CBsymp5.Text = reader.GetString(4)
Me.TextBox1.Text = reader.GetString(5)
Me.TextBox2.Text = reader.GetString(6)
Me.TextBox3.Text = reader.GetString(7)
Me.TextBox4.Text = reader.GetString(8)
Me.TextBox5.Text = reader.GetString(9)
Me.GBResults.Visible = True
MsgBox("Sickness Found")
Else

Me.CBsymp1.ResetText()
Me.CBsymp2.ResetText()
Me.CBsymp3.ResetText()
Me.CBsymp4.ResetText()
Me.CBsymp5.ResetText()
Me.TextBox1.ResetText()
Me.TextBox2.ResetText()
Me.TextBox3.ResetText()
Me.TextBox4.ResetText()
Me.TextBox5.ResetText()
MsgBox("Please enter more fields!")
End If
reader.Close()
Finally
Try
cnn.Close()
Catch : End Try
End Try
cnn.Close()