SQL Query Error

All Forums >> [Web Development] >> ASP and Database

Message

zedmond -> SQL Query Error (1/25/2006 15:50:54)
An asp page set that was previously operating correctly has recently started acting up. The page is built using variables passed via hidden and visible form variables. I can display the variables on screen but when I try to use them to create an SQL query I get the following: Database Results Wizard Error Unable to find operator in query string. Query string currently is INSERT INTO TripDetails(Destination, Route,PurposeofTrip,CustID,BoatNumber,BoatTime,KIB,KOB,KIBAMPM,KOBAMPM) VALUES ('::Destination::', '::Route::','::PurposeofTrip::','::CustWNumber::','::BoatNumber::','::BoatTime::','::KIB::','::KOB::','::KIBAMPM::','::KOBAMPM::'); I have set fp_DEBUG = True It seems that the variables are not visible to the SQL query. When I replace the vars with actual values, the insert works. I have started over from scratch and found that completely new forms give me the same error. Any help would be appreciated.

BeTheBall -> RE: SQL Query Error (1/25/2006 15:57:01)
Welcome to Outfront. Sounds like you are a victim of the problem mentioned here: http://www.frontpagewebmaster.com/m-175524/tm.htm The easiest fix is to replace the files in the _fpclass folder with those provided in the 4th post of the above thread.

BobGeezer -> RE: SQL Query Error (1/27/2006 17:21:35)
I have a problem getting past "the Query has errors" error message using DRW with the (&%&^% FP2003 SP2 additions: This code works (gets past the error message): Update Reservation Set Notes = 'Hello World' Where ReservationID = ::ReservationID::; This code fails, and there's no way to ignore it: Update Reservation Set Notes = '::Notes::' Where ReservationID = ::ReservationID::; Of course, I have many mpre parameters, in code that has run correctly for a year, until the SP2 "fixes" were applied. THe MS erro message ab out why the Special QQuery has error leads to a page that says the include aren't included, etc., etc. - - gibberish.

BobGeezer -> RE: SQL Query Error (1/27/2006 18:51:31)
I tried replacing the 2003 includes with the old FP2000 ones, but that doesn't work either: I still get the Custom Query Contains Errors" and can't get past it, even when I code a totally new page using a DRW Custom Query." How do I get FP parameters into a straight SQL UPDATE statement? conn.execute("Update Reservation Set Notes = ???? Where ReservationID = ????") etc. . . I can code script / asp but am not sure how to convert Request("Notes") into values in SQL statements: Dim SQLNotes Request("Notes") = SQLnotes ? conn.execute("Update Reservation Set Notes = SQLNotes Where ReservationID = ????") etc???

Spooky -> RE: SQL Query Error (1/27/2006 19:13:32)
Try this : conn.execute("Update Reservation Set Notes = '"&replace(request.form("notes"),"'","''") &"' Where ReservationID = "& cLng(request.form("ReservationID")))

BobGeezer -> RE: SQL Query Error (1/28/2006 13:45:28)
Spooky, once more you're great . . .and the code worked the first time for alpha fields. But I don't seem to have the right syntax for numeric fields; a Yes/No field ("Cancel") and a straight numeric one (Count"). THis code : conn.execute("Update Reservation Set Notes = '"&replace(request.form("notes"),"'","''") & Cancel = "&cLng(request.form("Cancel"),"''") & Group = '"&replace(request.form("Group"),"'","''") & Count = "cLng(request.form("Count"),"''") &"' Where ReservationID = "& cLng(request.form("ReservationID"))) Produces this error: Expected ')' /StaffServices/Resos/ResoUpdate.asp, line 15 conn.execute("Update Reservation Set Notes = '"&replace(request.form("notes"),"'","''") & Cancel = "&cLng(request.form("XXX Cancel"),"''") & Group = '"&replace(request.form("Group"),"'","''") & Count = "cLng(request.form("Count"),"''") &"' Where ReservationID = "& cLng(request.form("ReservationID"))) ------------------------------------------------------------------------------------------------------------------------^ (pointing to the XXX above in request.form("Cancel"),"

Spooky -> RE: SQL Query Error (1/28/2006 14:03:48)

See how that goes?
Note - to check the SQL string, see I have used response write to ensure the syntax is correct (disabled)
If you have any errors, enable that and see how the SQL string actually looks?


sSQL = "Update Reservation Set Notes = '"&replace(request.form("notes"),"'","''") &"', Cancel = "&cLng(request.form("Cancel"))&", Group = '"&replace(request.form("Group"),"'","''") &"', Count = " & cLng(request.form("Count")) &" Where ReservationID = "& cLng(request.form("ReservationID"))

'response.write sSQL
'response.end

conn.execute(sSQL)

BobGeezer -> RE: SQL Query Error (1/28/2006 14:45:53)
This is what I get when I execute the response.Write: Update Reservation Set Notes = '', Cancel = 0, Group = '', Count = 0 Where ReservationID = 0 I assume this means the parsing accepts Count and Cancel and ReservationID as numeric and Goup and Notes as alpha. . . . but I'm getting an error when I conn.execute(sSQL). I'll check it again. Can you recommend a publication that explains clearly the (*&^&^%$ syntax needed for writing SQL statements with paramaterizen input strings? It's clearly bizarre, but doable if one can find a publication. THanks again

Spooky -> RE: SQL Query Error (1/28/2006 15:04:13)
Is the posting form using POST or GET ? or were you testing with notes and group blank? The syntax certainly looks correct now. Does the database design allow for zero length and not required inputs? If so - what is the error?

BobGeezer -> RE: SQL Query Error (1/28/2006 15:06:06)
When I enable the conn.execute(sSQL) statement in Line 12, I get the following error, indicating a syntax error (sSQL i= etc. is line 7) but referrig to the conn.execute(sSQL) line - 12. When I comment line 12 out, it runs OK but of course doesn't update anything ! Update Reservation Set Notes = '', Cancel = 0, Group = '', Count = 0 Where ReservationID = 0 Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. /StaffServices/Resos/ResoUpdate.asp, line 12 Maybe I should stick to bridge?

BobGeezer -> RE: SQL Query Error (1/28/2006 15:15:50)
The posting form is Post See below for use with non-blank inputs . . . but once again the line 12 is the conn.execute, not the sSQL line. Perhaps it's confused as to which line it is as the sSQL line is quite long. . .? Update Reservation Set Notes = 'Test of ', Cancel = 0, Group = 'xxxx', Count = 3 Where ReservationID = 246 Produces: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. /StaffServices/Resos/ResoUpdate.asp, line 12

BobGeezer -> RE: SQL Query Error (1/28/2006 15:18:58)
Trying it with a direct conn.execute, the syntax error reappears in the proper place: conn.execute("Update Reservation Set Notes = '"&replace(request.form("notes"),"'","''") & "', Cancel = "&cLng(request.form("Cancel"))&", Group = '"&replace(request.form("Group"),"'","''") &"', Count = " & cLng(request.form("Count")) &" Where ReservationID = "& cLng(request.form("ReservationID"))) Syntax error in UPDATE statement . . .

Spooky -> RE: SQL Query Error (1/28/2006 15:22:06)

Count and group are reserved words !

sSQL = "Update Reservation Set [Notes] = '"&replace(request.form("notes"),"'","''") &"', [Cancel] = "&cLng(request.form("Cancel"))&", [Group] = '"&replace(request.form("Group"),"'","''") &"', [Count] = " & cLng(request.form("Count")) &" Where ReservationID = "& cLng(request.form("ReservationID"))

'response.write sSQL
'response.end

conn.execute(sSQL)

BobGeezer -> RE: SQL Query Error (1/28/2006 15:27:20)
Of course: anyone would know that : ( as I said Do you know of a publication, etc.) However: Type mismatch: 'cLng'

BobGeezer -> RE: SQL Query Error (1/28/2006 15:47:30)
THe cLng error comes if the numeric fields are left blank: I can solve that programatically, of course . . . . thanks so much for your help and hanging in there: my client thanks you, too, if only he knew . . . !!! BTW, this conversation could be the basis for a really valuable piece for those nhot speaking all the nuances of the (&^^%$ syntax required to do these kinds of things, instead of using the &^%&^ DRW for Updates and Deletes, which haven't worked right for years !!! THanks again.

Spooky -> RE: SQL Query Error (1/28/2006 15:58:11)
http://www.w3schools.com/asp/default.asp [:D]

Sol_1 -> RE: SQL Query Error (3/2/2006 2:11:15)
Hey guys - I am having this exact same problem where the SP2 update and everything mentioned in this thread (except for the code part) isn't working. Things were fine until a little bit ago when I was no longer able write a simple update statement using something like '::notes::'. Anyone know what is causing this recent change? I am not a strong asp coder so I wasn't fully catching where the scripts you were writing about above go. In the DBRW in the or straight into the code of the page? If in the code of the page, how do I get to that point if the DBRW won't even let me get past entering it in there in the first place? Any help you can provide would be great. I appreciate it and I appreciate this forum. Thank you!

Page: [1]

0.09375