ASP, SQL and ME

davemcdoug

Hey there,

I am getting the following error -

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/tcs/pages/warranty_search.asp, line 45, column 28
Set objRS = objConn.Execute sqlmain

Don't know why and have tried heaps of things. It seems the line which calls my sql is failing and I am confused as to why. Any idea's?

Check out my code...

<%
sqlmain = "SELECT * FROM fib_warr, fib_warr_cust, fib_warr_prod, fib_store, fib_merch WHERE fib_warr_cust.fib_warr_id = fib_warr.fib_warr_id AND fib_warr_prod.fib_warr_id = fib_warr.fib_warr_id AND fib_warr.fib_store_id = fib_store.fib_store_id AND fib_store.fib_merch_id = fib_merch.fib_merch_id"

if Request("warranty") <> "" then
sqlmain=sqlmain & " AND fib_warr.fib_warr_id = " & "'" &Request("warranty") &"'"
end if
if Request("surname") <> "" then
sqlmain=sqlmain & " AND fib_warr_cust.fib_warr_cust_surname = " & "'" &Request("surname") &"'"
end if
if Request("add1") <> "" then
sqlmain=sqlmain & " AND fib_warr_cust.fib_warr_cust_add1 = " & "'" &Request("add1") &"'"
end if
if Request("city") <> "" then
sqlmain=sqlmain & " AND fib_warr_cust.fib_warr_cust_city = " & "'" &Request("city") &"'"
end if
if Request("hm") <> "" then
sqlmain=sqlmain & " AND fib_warr_cust.fib_warr_cust_hm = " & "'" &Request("hm") &"'"
end if
if Request("regtrans") <> "" then
sqlmain=sqlmain & " AND fib_warr.fib_warr_regtrans = " & "'" &Request("regtrans") &"'"
end if

Set objRS = objConn.Execute sqlmain
while not objRS.EOF
%>
<tr><td><a href="warranty_display.asp?id=<%Response.Write objRS.Fields("fib_warr.fib-warr_id")%>"><%Response.Write objRS.Fields("fib_warr.fib-warr_id")%></a></td></tr>
<%
objRS.MoveNext
wend


F.Y.I.

I have the following code earlier on in the page -

<%
function EscapeSQL(s)
EscapeSQL = Replace(s, "'", "\'")
end function

Dim sConnection, objConn , objRS

sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=****; UID=****;PASSWORD=****; OPTION=3"

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open(sConnection)

%>

Also I did a response.write(sqlmain) where I have the Set objRS = objConn.Execute sqlmain line and it printed the sql statement as it should be so no problem there I guess that I can't call sqlmain in this manner but don't know how I should do it otherwise???

Any help would be awesome!

Thanks
Dave