"where" clause problem

gilgalbiblewhee · #1 (**permalink**) 08-18-04, 18:49

Why is it that whenever I press "search" it's saying:

Quote:

SELECT * FROM bible WHERE verse_spoke LIKE '%jehovah%'%' AND ( spokes = '004') AND ( recordType = 'gn' OR recordType = 'is' OR recordType = 'ro')
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'verse_spoke LIKE '%jehovah%'%' AND ( spokes = '004') AND ( recordType = 'gn' OR recordType = 'is' OR recordType = 'ro')'.

/amos.asp, line 161

Here is the code:

Code:

<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<TITLE>amos.asp</TITLE>
</head>

<body>
<%
Const DB_NAME    = "kjv.mdb"  ' Name of our database file

GetConnectionString =   "Driver={Microsoft Access Driver (*.mdb)};" & _
        "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
        "UID=;PWD=;"

SQL = "SELECT * FROM bible WHERE "

Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.Open(GetConnectionString)

dim Keyword
dim iCounter
dim iLoopCount
dim aRecTypes

Keyword = Request.QueryString("Keyword")
iCounter = 0

If   request.QueryString("book")="book" then

  SQL = SQL & "book LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("book_spoke")="book_spoke" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "book_spoke LIKE '%" & Keyword & "%'" & spoke & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("book_title")="book_title" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "book_title LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("chapter")="chapter" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "chapter LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("chapter_spoke")="chapter_spoke" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "chapter_spoke LIKE '%" & Keyword & "%'" & spoke & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("verse")="verse" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "verse LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("verse_spoke")="verse_spoke" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "verse_spoke LIKE '%" & Keyword & "%'"  & spoke & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("text_data")="text_data" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "text_data LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If Trim(Request.QueryString("spokes")) <> "" Then


  aRecTypes = Split(Request.QueryString("spokes"), ",")

  If IsArray(aRecTypes) Then 'This is a bit redundant, but it can't hurt
    SQL = SQL & " AND ("

    For iLoopCount = 0 To UBound(aRecTypes)
      If iLoopCount <> 0 Then
        SQL = SQL & " OR "
      End If

      SQL = SQL & " spokes = '" & trim(aRecTypes(iLoopCount)) & "'"
    Next
  End If
  SQL = SQL & ")"
End If

If Trim(Request.QueryString("recordType")) <> "" Then


  aRecTypes = Split(Request.QueryString("recordType"), ",")

  If IsArray(aRecTypes) Then 'This is a bit redundant, but it can't hurt
    SQL = SQL & " AND ("

    For iLoopCount = 0 To UBound(aRecTypes)
      If iLoopCount <> 0 Then
        SQL = SQL & " OR "
      End If

      SQL = SQL & " recordType = '" & trim(aRecTypes(iLoopCount)) & "'"
    Next
  End If
  SQL = SQL & ")"
End If

Response.Write SQL

Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
rsGlobalWeb.Open SQL, dbGlobalWeb, 3%>

<%
If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>

<h2 align="center">We did not find a match!</h2>
<%Else%>


<%If Not rsGlobalWeb.BOF Then%>

<h2>These are the results:</h2>

<table BORDER="0" width="100%" cellpadding="3">
  <tr>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book Spoke</font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book Title </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Chapter </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Chapter Spoke </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse Spoke </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Text </font></th>
  </tr>

    <%Do While Not rsGlobalWeb.EOF%>
  <tr>
        <td><%=rsGlobalWeb("book")%>&#32
</td>
    <td><%=rsGlobalWeb("book_spoke")%>
</td>
        <td><%=rsGlobalWeb("book_title")%>
</td>
        <td><%=rsGlobalWeb("chapter")%>
</td>
        <td><%=rsGlobalWeb("chapter_spoke")%>
</td>
        <td><%=rsGlobalWeb("verse")%>
</td>
        <td><%=rsGlobalWeb("verse_spoke")%>
</td>
        <td><%=rsGlobalWeb("text_data")%>
</td>
  </tr>
<% rsGlobalWeb.MoveNext
    Loop
    %>
</table>
<%End If%>
<%End If%>
<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>
</body>
</html>

Seppuku · #2 (**permalink**) 08-18-04, 19:03

Gil, you should've left this in the previous thread...

You're getting this error because of this:

verse_spoke LIKE '%jehovah%'%'

Your code should be

SQL = SQL & "verse_spoke LIKE '%" & Keyword & "%'"

gilgalbiblewhee · #3 (**permalink**) 08-18-04, 19:18

That's what I have.

Code:

If   request.QueryString("verse_spoke")="verse_spoke" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "verse_spoke LIKE '%" & Keyword & "%'"  & spoke & "%'"

  iCounter = iCounter + 1

end if

What rokslide had done (meybe I should have kept it as the way he did) was to change

Code:

If   request.QueryString("verse_spoke")="verse_spoke" then

to

Code:

If   request.QueryString("verse_spoke")="Verse_Spoke" then

I still don't understand why it's capitalized but the field name is not capitalized.

Seppuku · #4 (**permalink**) 08-18-04, 19:25

Ok, you're not looking in the right spot... the error isn't caused by your If statement. it's in the IF statement's block of code. Forget why the value is capital, you can figure that out later.

Remove the code in the segment below that is bold red from your code:

Code:

If   request.QueryString("verse_spoke")="verse_spoke" then

  If iCounter > 0 Then
    SQL = SQL & " OR "
  End If

  SQL = SQL & "verse_spoke LIKE '%" & Keyword & "%'"  & spoke & "%'"

  iCounter = iCounter + 1

end if

rokslide · #5 (**permalink**) 08-18-04, 19:30

Sep is right,... and strangely enough, you are wrong. You do not always have what Sep has posted... you have this...

Code:

  SQL = SQL & "verse_spoke LIKE '%" & Keyword & "%'"  & spoke & "%'"

and

Code:

  SQL = SQL & "book_spoke LIKE '%" & Keyword & "%'" & spoke & "%'"

and the same for chapter_spoke....

Your "spoke" variable = "" though so when you do this concatenation you get what you are seeing.

As for the capitals thing, I think you have completely missed what you are doing... in your screen/page that submits the search you have radio buttons and checkboxes. You have a display value of Verse Spoke (for example) but the value attribute is verse_spoke (or something else). You need to check for the value of the attribute and not what you are displaying.

sundialsvcs · #6 (**permalink**) 08-18-04, 20:18

Don't forget that you must validate and clean-up anything that the user might send you. If the user adds quotes you must remove them or escape them. Wa-a-a-ay too many web-sites are out there which only work correctly for "correct" inputs, and which give away far too much information in error-messages when the user's inputs are "wrong."

rokslide · #7 (**permalink**) 08-18-04, 20:26

geez Sundial that's a long way off as far as this little project goes... I don't think we should head into at the moment it would just confuse things...

gilgalbiblewhee · #8 (**permalink**) 08-19-04, 01:22

I understood Sep.

Now it says

Quote:

SELECT * FROM bible WHERE AND ( recordType = 'gn' OR recordType = 'is' OR recordType = 'ro')
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'AND ( recordType = 'gn' OR recordType = 'is' OR recordType = 'ro')'.

/amos.asp, line 161

if I disable the 66 checkboxes I get:

Quote:

SELECT * FROM bible WHERE
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in WHERE clause.

/amos.asp, line 161

Strange. I wasn't notified all these replies by email.

gilgalbiblewhee · #9 (**permalink**) 08-19-04, 01:26

Quote:

Originally Posted by rokslide

Your "spoke" variable = "" though so when you do this concatenation you get what you are seeing.

As for the capitals thing, I think you have completely missed what you are doing... in your screen/page that submits the search you have radio buttons and checkboxes. You have a display value of Verse Spoke (for example) but the value attribute is verse_spoke (or something else). You need to check for the value of the attribute and not what you are displaying.

I didn't understand that part, especially the bold section. This stuff is new to me.

rokslide · #10 (**permalink**) 08-19-04, 01:27

okie, lets deal with the first one (again). the problem with the sql statement is that your where clause is incorrect. the reason it is incorrect is because either 1. all your boxes are unchecked (and for this I mean your text, book, chapter boxes) or 2. when you are trying to see if they are checked your check is failing (probably because of capitalisation)

the second problem has actually been there forever,... the question is what do you want to do when none of the 66 boxes are checked?

rokslide · #11 (**permalink**) 08-19-04, 01:41

okie, your code for your check box looks something like this....

Code:

<input type="checkbox" name="book_spoke" value="book_spoke">Book Spoke</br>

now, when you do a request.form("book_spoke") you must make sure you are checking for the value of the input. The value of the input is determined by this part value="book_spoke" not by [b]>Book Spoke<[b]

If you check for the wrong values your checks will fail (but not raise an error) and your sql statement will not be created correctly...

if you are unsure about what values are being sent through you can look at the url of your search results page and find the values in there....

gilgalbiblewhee · #12 (**permalink**) 08-19-04, 01:46

I have left the text checked and that was the response. Ok I noticed that the capitalization wasn't there. I changed that. And a question.

I have duplicated the (I guess it's called an array) function of the recorType and called it "spokes". But now that I think of it, the dropdown is not a multiple selection, so, I guess it's not an array. They are numbered 1-22 and should search the checkboxes checked right under it.

I had a model of a dropdown code but their dropdown selection were the fields. My selection of 1-22 are the records. The checkboxes are the fields. So I think it's not the same. What do you think?

Code:

If Trim(Request.QueryString("spokes")) <> "" Then


  aRecTypes = Split(Request.QueryString("spokes"), ",")

  If IsArray(aRecTypes) Then 'This is a bit redundant, but it can't hurt
    SQL = SQL & " AND ("

    For iLoopCount = 0 To UBound(aRecTypes)
      If iLoopCount <> 0 Then
        SQL = SQL & " OR "
      End If

      SQL = SQL & " spokes = '" & trim(aRecTypes(iLoopCount)) & "'"
    Next
  End If
  SQL = SQL & ")"
End If

If Trim(Request.QueryString("recordType")) <> "" Then


  aRecTypes = Split(Request.QueryString("recordType"), ",")

  If IsArray(aRecTypes) Then 'This is a bit redundant, but it can't hurt
    SQL = SQL & " AND ("

    For iLoopCount = 0 To UBound(aRecTypes)
      If iLoopCount <> 0 Then
        SQL = SQL & " OR "
      End If

      SQL = SQL & " recordType = '" & trim(aRecTypes(iLoopCount)) & "'"
    Next
  End If
  SQL = SQL & ")"
End If

rokslide · #13 (**permalink**) 08-19-04, 01:54

okie, I'm not really sure what you are trying to do here but to try and help and answer your question,.. the drop down box will only pass a single value, not an array of values...

it should be pointed out that all values passed are actually string values, but you can make then into other stuff programaticly...

gilgalbiblewhee · #14 (**permalink**) 08-19-04, 02:11

I didn't know the use of the array.

The dropdown is similar to the recordType in the manner that they search records. Although recordType is an array and searches the 66 books by 66 checkboxes. The dropdown is numbered 1-22 searching the records numbered 1-22. Now the three checkboxes underneath are supposed to check the 3 fields to search into.

I don't understand how the function should be written. I have a model code of a dropdown, but it is comprised of fields and not records.

gilgalbiblewhee · #15 (**permalink**) 08-19-04, 02:21

this

Code:

rsGlobalWeb.Open SQL, dbGlobalWeb, 3%>

Quote:

SELECT * FROM bible WHERE
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in WHERE clause.

/amos.asp, line 144

Internet Services

Web Development

Digital Marketing

Consumer Tech