Syntax error (missing operator) in query expression

[Maciej Paras]

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,

 

[Bob Barrows]

See below for the best technique to debug this:

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')''.

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