...

View Full Version : Syntax error (missing operator) in query expression



mattboy_slim
11-18-2003, 04:53 AM
I purchased a piece of realty software, and am having trouble adding custom fields.

I'm trying to add the new field I created in the DB to the SQL query.

When I attempt to submit the form, I get the following error message:


Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '1',LIST_LAKE = ''.
/carlson/sm_includes/functions.asp, line 97


I've posted what I believe to be the relevant code below, with my added items highlighted in red. Please help me out, as I'm feeling quite stupid here and could really use a little boost in my evening.

Thanks,
Matt



*************
'Check posted values and update database with new values
'*************************************************
*************
IF Request.QueryString("upd") = "go" THEN

list_id = Request.Form("list_id") * 1
list_address = Request.Form("list_address")
list_location = Request.Form("list_location") * 1
list_desc = Request.Form("list_desc")
list_features = Request.Form("list_features")
list_add_info = Request.Form("list_add_info")
list_price = Request.Form("list_price")
list_pricing = Request.Form("list_pricing") * 1
list_status = Request.Form("list_status") * 1
list_order = Request.Form("list_order") * 1
list_featured = Request.Form("list_featured") * 1
list_viewable = Request.Form("list_viewable")
list_lake = Request.Form("list_lake")

CALL sql_safe(list_address)
CALL sql_safe(list_desc)
CALL sql_safe(list_features)
CALL sql_safe(list_add_info)
CALL fix_number(list_price)

errortext = "<p><span class='rednotice'>Errors have occured</span><br>Please correct the errors listed below:</p>"
' errortext = CheckNotBlank(errortext,errorno,"Address",list_address)
errortext = CheckNotBlank(errortext,errorno,"Description",list_desc)
errortext = CheckNumber(errortext,errorno,"Price",list_price)

IF errorno = 0 THEN

sql_table = "TBL_LISTINGS"
sql_set = "LIST_DATE = #" & Date() & "#,LIST_LOCATION = " & list_location & ",LIST_ADDRESS = '" & list_address & "',LIST_DESC = '" & list_desc & "',LIST_FEATURES = '" & list_features & "',LIST_ADD_INFO = '" & list_add_info & "',LIST_PRICE = " & list_price & ",LIST_PRICING = " & list_pricing & ",LIST_STATUS = " & list_status & ",LIST_ORDER = " & list_order & ",LIST_VIEWABLE = '" & list_viewable & "',LIST_FEATURED = " & list_featured & "',LIST_LAKE = '" & list_lake
sql_where = "LIST_ID = " & list_id

CALL UpdateRecord(updListing,sql_table,sql_set,sql_wher
e)

Response.Redirect("listings.asp?l=" & Request.QueryString("l"))

END IF

END IF



EDIT:
If I remove the apostrophe (') before LIST_LAKE of the sql_set, then I get the following error message instead:


Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in string in query expression '' WHERE LIST_ID = 149'.
/carlson/sm_includes/functions.asp, line 97

Bullschmidt
11-18-2003, 06:31 AM
Instead of this:
...LIST_FEATURED = " & list_featured & "',LIST_LAKE = '" & list_lake

Try something more like this (assuming text fields in the database):

LIST_FEATURED = '" & list_featured & "', LIST_LAKE = '" & list_lake & "'"

glenngv
11-18-2003, 06:32 AM
I think the problem is in missing single quotes

... & "',LIST_FEATURED = '" & list_featured & "',LIST_LAKE = '" & list_lake & "'"

where the bold text are the ones I added.

The single quote at the end will depend on what UpdateRecord() does when forming the whole SQL statement.

Can you post the code in UpdateRecord()?

The best way to debug syntax errors in SQL statements is to response.write them.

response.write(strSQLVariable)
response.end

try to put that 2 lines and then post the output here.

mattboy_slim
11-18-2003, 06:39 AM
Thanks for the help guys. This works. Awesome.



& ",LIST_LAKE = '" & list_lake & "'"

Bullschmidt
11-18-2003, 06:41 AM
Rock 'n' roll!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum