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.OLED B.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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

[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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Nathan Sokalski" <nj********@hotmail.com> wrote in message
news:uW**************@TK2MSFTNGP14.phx.gbl...

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.OLED B.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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

[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.ExecuteCommandTextE rrorHandling(Int32 hr)
+41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
+92
System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior,
Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior behavior)
+69
System.Data.OleDb.OleDbCommand.System.Data.IDbComm and.ExecuteReader(CommandBehavior
behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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.IPo stBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection 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.OLED B.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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:O5**************@TK2MSFTNGP10.phx.gbl...

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?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Nathan Sokalski" <nj********@hotmail.com> wrote in message
news:uW**************@TK2MSFTNGP14.phx.gbl...

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.OLED B.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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

[Paul Clement]

On Wed, 6 Jul 2005 22:12:33 -0400, "Nathan Sokalski" <nj********@hotmail.com> wrote:

¤ 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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:5j********************************@4ax.com...

On Wed, 6 Jul 2005 22:12:33 -0400, "Nathan Sokalski"
<nj********@hotmail.com> wrote:

¤ 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)

[Chaim]

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?
--
Chaim
"Nathan Sokalski" wrote:

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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Paul Clement" <Us***********************@swspectrum.com> wrote in message
news:5j********************************@4ax.com...

On Wed, 6 Jul 2005 22:12:33 -0400, "Nathan Sokalski"
<nj********@hotmail.com> wrote:

¤ 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]

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?
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Chaim" <Ch***@discussions.microsoft.com> wrote in message
news:CB**********************************@microsof t.com...

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?
--
Chaim
"Nathan Sokalski" wrote:

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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Paul Clement" <Us***********************@swspectrum.com> wrote in
message
news:5j********************************@4ax.com...

> On Wed, 6 Jul 2005 22:12:33 -0400, "Nathan Sokalski"
> <nj********@hotmail.com> wrote:
>
> ¤ 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)

[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" <nj********@hotmail.com> wrote in message
news:e6**************@TK2MSFTNGP12.phx.gbl...

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?
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Chaim" <Ch***@discussions.microsoft.com> wrote in message
news:CB**********************************@microsof t.com...

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?
--
Chaim
"Nathan Sokalski" wrote:

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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Paul Clement" <Us***********************@swspectrum.com> wrote in
message
news:5j********************************@4ax.com...
> On Wed, 6 Jul 2005 22:12:33 -0400, "Nathan Sokalski"
> <nj********@hotmail.com> wrote:
>
> ¤ 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]

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).
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Ron Allen" <rallen@_nospam_src-us.com> wrote in message
news:OM*************@TK2MSFTNGP14.phx.gbl...

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" <nj********@hotmail.com> wrote in message
news:e6**************@TK2MSFTNGP12.phx.gbl...

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?
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Chaim" <Ch***@discussions.microsoft.com> wrote in message
news:CB**********************************@microsof t.com...

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?
--
Chaim
"Nathan Sokalski" wrote:

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.
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Paul Clement" <Us***********************@swspectrum.com> wrote in
message
news:5j********************************@4ax.com...
> On Wed, 6 Jul 2005 22:12:33 -0400, "Nathan Sokalski"
> <nj********@hotmail.com> wrote:
>
> ¤ 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)

[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
"Nathan Sokalski" <nj********@hotmail.com> wrote in message
news:Or**************@TK2MSFTNGP14.phx.gbl...

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).
--
Nathan Sokalski
nj********@hotmail.com
http://www.nathansokalski.com/

"Ron Allen" <rallen@_nospam_src-us.com> wrote in message
news:OM*************@TK2MSFTNGP14.phx.gbl...

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

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