Using LIKE to perform a database match

[Nathan Sokalski]

I am trying to do a database search using LIKE using the following code:


Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSearch.Click

If Page.IsValid Then

Dim logictype As String

Dim keywords As String()

Dim papdatabase As New DataSet

Dim myconnection As System.Data.OleDb.OleDbConnection = New
OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &
Server.MapPath("papresenters.mdb"))

Dim cmdSelect As New System.Data.OleDb.OleDbCommand

Dim dataadapterSelect As New System.Data.OleDb.OleDbDataAdapter

cmdSelect.Connection = myconnection

dataadapterSelect.SelectCommand = cmdSelect

If radAll.Checked Then logictype = " AND " Else logictype = " OR "

cmdSelect.CommandText = "SELECT
members.organization,members.artist,artists.email,artists.website FROM
members,artists WHERE "

If Not chkAnyDate.Checked Then

cmdSelect.CommandText &= "#" & calTimeFrame.SelectedDate.ToShortDateString()
& "#>timeframestart AND #" & calTimeFrame.SelectedDate.ToShortDateString() &
"#<timeframeend OR "

cmdSelect.CommandText &= "#" &
calTimeFrame2.SelectedDate.ToShortDateString() & "#>timeframestart AND #" &
calTimeFrame2.SelectedDate.ToShortDateString() & "#<timeframeend"

End If

If txtArtist.Text <> "" Then

cmdSelect.CommandText &= logictype & "artist='" & txtArtist.Text & "'"

End If

keywords = txtKeyword.Text.Split(",".ToCharArray)

For Each word As String In keywords

word = word.Trim()

cmdSelect.CommandText &= logictype & "organization LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "county LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "artist LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "genre LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "comments LIKE '%" & word & "%'"

Next

dataadapterSelect.Fill(papdatabase)

datResults.DataSource = papdatabase

datResults.DataBind()

Me.Controls.Add(New LiteralControl("The page is valid!"))

Else

Me.Controls.Add(New LiteralControl("The page is not valid!"))

End If

End Sub


This gives me a syntax error in the query expression containing the LIKE's.
What is it that I am doing wrong here? Thanks.

 

[Douglas J. Steele]

One obvious thing is that if chkAnyDate.Checked is False, then you're going
to end up with either WHERE OR or WHERE AND.

Other than that, have you examined what's contained in cmdSelect.CommandText
to determine the error?

 

[Nathan Sokalski]

I believe I have fixed that problem now, but I am recieving an error when
calling the Fill method. This error is as follows:

Server Error in '/PAPresenters' Application.
--------------------------------------------------------------------------------

No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.

Source Error:

Line 107: cmdSelect.CommandText &= logictype & "comments LIKE
'%" & word & "%'"
Line 108: Next
Line 109: dataadapterSelect.Fill(papdatabase)
Line 110: datResults.DataSource = papdatabase
Line 111: datResults.DataBind()

Source File: c:\inetpub\wwwroot\PAPresenters\search.aspx.vb Line: 109

Stack Trace:

[OleDbException (0x80040e10): No value given for one or more required
parameters.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
+41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
+92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
+69
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
PAPresenters.search.btnSearch_Click(Object sender, EventArgs e) in
c:\inetpub\wwwroot\PAPresenters\search.aspx.vb:109
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1273



--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET
Version:1.1.4322.2032



The associated code, as it currently appears, is shown below. I would use
the variation of the Fill method that involves entering the source table,
but I do not know how to do that when I am using 2 tables as my source of
data (members and artists). Thanks.


Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSearch.Click

If Page.IsValid Then

Dim logictype As String

Dim keywords As String()

Dim papdatabase As New DataSet

Dim myconnection As System.Data.OleDb.OleDbConnection = New
OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &
Server.MapPath("papresenters.mdb"))

Dim cmdSelect As New System.Data.OleDb.OleDbCommand

Dim dataadapterSelect As New System.Data.OleDb.OleDbDataAdapter

cmdSelect.Connection = myconnection

dataadapterSelect.SelectCommand = cmdSelect

If radAll.Checked Then logictype = " AND " Else logictype = " OR "

cmdSelect.CommandText = "SELECT
members.organization,members.artist,artists.email,artists.website FROM
members,artists WHERE 1=1 "

If Not chkAnyDate.Checked Then

cmdSelect.CommandText &= logictype & "#" &
calTimeFrame.SelectedDate.ToShortDateString() & "#>timeframestart AND #" &
calTimeFrame.SelectedDate.ToShortDateString() & "#<timeframeend OR "

cmdSelect.CommandText &= "#" &
calTimeFrame2.SelectedDate.ToShortDateString() & "#>timeframestart AND #" &
calTimeFrame2.SelectedDate.ToShortDateString() & "#<timeframeend"

End If

If txtArtist.Text <> "" Then

cmdSelect.CommandText &= logictype & "artist='" & txtArtist.Text & "'"

End If

keywords = txtKeyword.Text.Split(",".ToCharArray)

For Each word As String In keywords

word = word.Trim()

cmdSelect.CommandText &= logictype & "organization LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "county LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "artist LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "genre LIKE '%" & word & "%'"

cmdSelect.CommandText &= logictype & "comments LIKE '%" & word & "%'"

Next

dataadapterSelect.Fill(papdatabase)

datResults.DataSource = papdatabase

datResults.DataBind()

Me.Controls.Add(New LiteralControl("The page is valid!"))

Else

Me.Controls.Add(New LiteralControl("The page is not valid!"))

End If

End Sub


I appreciate any help you can give, or an good sources of references.
Thanks.

 

[Paul Clement]

¤ I believe I have fixed that problem now, but I am recieving an error when
¤ calling the Fill method. This error is as follows:
¤
¤ Server Error in '/PAPresenters' Application.
¤ --------------------------------------------------------------------------------
¤
¤ No value given for one or more required parameters.
¤ Description: An unhandled exception occurred during the execution of the
¤ current web request. Please review the stack trace for more information
¤ about the error and where it originated in the code.
¤

¤
¤ cmdSelect.CommandText &= logictype & "county LIKE '%" & word & "%'"
¤

Just a guess but is the actual column name supposed to be "county" (note the missing "r") or
"country"?

Any column names that cannot be identified are considered parameters of the query.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Nathan Sokalski]

No, the column name really is county, NOT country. Is there something
special I have to do when I am searching in 2 tables at once? In case my
code was too strange (or maybe poorly written, since I have had to learn a
lot of it myself), my basic goal was to allow a user to search a database
using the fields mentioned as well as keywords separated by commas. The only
difference between this and all the other pages I have written that use
databases is the SELECT statement for this one searches in 2 tabes rather
than 1. I thought I created the SQL correctly, but I must be missing
something somewhere, but I am not sure where to look since the error doesn't
say much more than that something is wrong. Thanks.

 

[Guest]

Nathan,

I might be way off base here, but- assuming I'm reading the code correctly
(ALERT! Big Assumption!!)- logictype is either ' AND ' or ' OR ' through this
entire proc. If it is ' AND ', e.g., won't this result in a where clause that
looks like:

WHERE 1 = 1 AND organization LIKE '%<value of word1>%'
AND county LIKE '%<value of word1>%'
AND artist LIKE '%<value of word1>%'
AND genre LIKE '%<value of word1>%'
AND comments LIKE '%<value of word1>%'
AND organization LIKE '%<value of word2>%'
AND county LIKE '%<value of word2>%'
AND artist LIKE '%<value of word2>%'
AND genre LIKE '%<value of word2>%'
AND comments LIKE '%<value of word2>%'
etc. for each keyword in the set?

Will this ever return any data?

 

[Nathan Sokalski]

You're probably right that the logictype inside the keyword area should
always be OR, but that still doesn't explain why I get an error. Is there
something wrong with my syntax?

 

[Ron Allen]

Nathan,
This looks like it should be
WHERE 1 = 1 AND (organization LIKE '%<value of word1>%'
AND county LIKE '%<value of word1>%'
AND artist LIKE '%<value of word1>%'
AND genre LIKE '%<value of word1>%'
AND comments LIKE '%<value of word1>%' )
OR (organization LIKE '%<value of word2>%'
AND county LIKE '%<value of word2>%'
AND artist LIKE '%<value of word2>%'
AND genre LIKE '%<value of word2>%'
AND comments LIKE '%<value of word2>%')
with the word searches seperated by OR otherwise you won't get any matches
(unless one of the words is a substring of the other). Of course the AND
clauses should be replaced with ORs if the logictype is differend.
Since this is going to a jet database try getting the command text in the
debugger and pasting it into a new query in Access and look for any errors
there. You may have to change the wildcards depending on your Access
settings.

Ron Allen

 

[Nathan Sokalski]

Thank you for that suggestion. After trying a few queries in Access I
realized what was causing my error, I had accidentally used the wrong table
name for one of the fields. After correcting this, my code now runs with no
errors but I cannot get it to return any results when I am using a JOIN (it
does return results if I perform the query on just one table).

 

[Ron Allen]

Nathan,
Make sure you aren't overqualifying something. Possibly you are using
some of your fields in the join columns which may cut things off early.

Ron Allen

Thank you for that suggestion. After trying a few queries in Access I
realized what was causing my error, I had accidentally used the wrong
table name for one of the fields. After correcting this, my code now runs
with no errors but I cannot get it to return any results when I am using a
JOIN (it does return results if I perform the query on just one table).

------------------snip-----------