Page 1 of 5 123 ... LastLast
Results 1 to 15 of 66
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: "where" clause problem

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

    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>

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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 & "%'"
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Jul 2004
    Posts
    494

    that's what I have

    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.

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    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."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  8. #8
    Join Date
    Jul 2004
    Posts
    494

    ok "spoke" is out of SQL

    I understood Sep.

    Now it says

    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:

    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.

  9. #9
    Join Date
    Jul 2004
    Posts
    494
    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.

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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?

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

  12. #12
    Join Date
    Jul 2004
    Posts
    494

    I have left one checked

    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

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  14. #14
    Join Date
    Jul 2004
    Posts
    494

    dropdown is not meant to be an array

    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.

  15. #15
    Join Date
    Jul 2004
    Posts
    494

    the error always points to...

    this

    Code:
    rsGlobalWeb.Open SQL, dbGlobalWeb, 3%>
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •