Syntax error (missing operator) in query expression

Discussion in 'ASP General' started by Maciej Paras, Oct 30, 2003.

  1. Maciej Paras

    Maciej Paras Guest

    Hello! I've written 2 pages: one i HTML format, and second - in ASP. When
    I'm posting data from HTML page, I receive this error generated by ASP page:

    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression '' AND ([ID
    Platnika]='OFFICE DEPOT')''.

    "Office Depot" is the value from form.

    When all values are set to "*" the script generates correct results, but if
    its value contains any string it generates the error message shown above.
    And this is the code of ASP page:

    <%
    Dim pT1
    Dim pT2
    Dim pD1
    Dim pD2
    Dim pD3
    Dim pD4
    Dim pK1
    Dim pD5
    Dim pTP1
    Dim pTP2

    pT1 = request("T1")
    pT2 = request("T2")
    pD1 = request("D1")
    pD2 = request("D2")
    pD3 = request("D3")
    pD4 = request("D4")
    pK1 = request("K1")
    pD5 = request("D5")
    pTP1 = request("TP1")
    pTP2 = request("TP2")

    Dim mySQL 'SQL command to execute
    Dim objPagingConn 'The ADODB connection object
    Dim objPagingRS 'The ADODB recordset object

    Set objPagingConn = Server.CreateObject("ADODB.Connection")
    objPagingConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
    Server.MapPath("../db2.mdb") & ";"
    Set objPagingRS = Server.CreateObject("ADODB.Recordset")

    mySQL = ""

    If pT1 <> "" And pT2 <> "" Then
    mySQL = mySQL & " AND (Data BETWEEN '" & Request ("T1") & "' AND '" &
    Request ("T2") & "')"
    End If

    If pD1 <> "*" Then
    mySQL = mySQL & " AND ([Numer faktury]='" & Request ("D1") & "')"
    End If

    If pD2 <> "*" Then
    mySQL = mySQL & " AND ([ID Platnika]='" & Request ("D2") & "')"
    End If

    If pD3 <> "*" Then
    mySQL = mySQL & " AND ([Kategoria]='" & Request ("D3") & "')"
    End If

    If pD4 <> "*" Then
    mySQL = mySQL & " AND ([ID faktury]='" & Request ("D4") & "')"
    End If

    'If pK1 <> "*" Then
    'mySQL = mySQL & " AND ([Kwota]='" & Request ("K1") & "')"
    'End If

    If pD5 <> "*" Then
    mySQL = mySQL & " AND ([Status]='" & Request ("D5") & "')"
    End If

    If pTP1 <> "" And pTP2 <> "" Then
    mySQL = mySQL & " AND ([Termin platnosci] BETWEEN '" & Request ("TP1") & "'
    AND '" & Request ("TP2") & "')"
    End If

    If mySQL <> "" Then
    mySQL = "WHERE '" & mySQL & "'"
    End If

    '---------------------------------------------------------------------------
    --------------------------------------------------------
    mySQL = "SELECT Data, [Numer faktury], [ID Platnika], Kategoria, [ID
    faktury], [Kwota], Status, [Termin platnosci] FROM [Faktury tabela
    zbierajaca faktury przychodzace]" & mySQL
    'mySQL = mySQL & ";"

    objPagingRS.Open mySQL, objPagingConn, 1,1 ', adOpenStatic, adLockReadOnly,
    adCmdText %>

    <% Do While Not objPagingRS.EOF %>
    <% response.write objPagingRS("Numer faktury") %>"><% response.write
    objPagingRS("Numer faktury") %><BR>
    <% objPagingRS.MoveNext
    Loop

    objPagingRS.Close
    Set objPagingRS = Nothing
    objPagingConn.Close
    Set objPagingConn = Nothing

    %>

    Can anybody tell me what is wrong with this code?
    What kind of parentes should I use to prevent this error? I'm using '" &
    variable & "' ..... Maybe there is another statement?
    Please, help!!!
    Maciej,
     
    Maciej Paras, Oct 30, 2003
    #1
    1. Advertising

  2. Maciej Paras

    Bob Barrows Guest

    See below for the best technique to debug this:
    Maciej Paras wrote:
    > Hello! I've written 2 pages: one i HTML format, and second - in ASP.
    > When I'm posting data from HTML page, I receive this error generated
    > by ASP page:
    >
    > Microsoft JET Database Engine (0x80040E14)
    > Syntax error (missing operator) in query expression '' AND ([ID
    > Platnika]='OFFICE DEPOT')''.
    >

    <snip>

    > mySQL = "SELECT Data, [Numer faktury], [ID Platnika], Kategoria, [ID
    > faktury], [Kwota], Status, [Termin platnosci] FROM [Faktury tabela
    > zbierajaca faktury przychodzace]" & mySQL
    > 'mySQL = mySQL & ";"


    Right here:

    Response.Write mySQL

    You have no hope of debugging this without knowing exactly what sql
    statement you are sending to the database. So, take a look at the result of
    the response.write in the browser window. The problem should stand out, but,
    if it doesn't, copy and paste the statement from the browser window the the
    SQL View of a Query Builder window in Access. Try to run it. At the very
    least, you will probably get a better error message.

    If you still can't figure it out, show us the sql statement.

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Oct 30, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. D
    Replies:
    4
    Views:
    2,287
  2. D
    Replies:
    0
    Views:
    2,240
  3. alexz
    Replies:
    0
    Views:
    118
    alexz
    Jul 11, 2003
  4. shank
    Replies:
    26
    Views:
    385
    Bob Barrows [MVP]
    Aug 9, 2004
  5. Replies:
    4
    Views:
    209
    Bob Barrows
    Sep 30, 2005
Loading...

Share This Page