|
| |
|
|
zedmond
Posts: 1
Joined: 1/25/2006
Status: offline
 |
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
Posts: 6354
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BobGeezer
Posts: 22
Joined: 7/9/2004
Status: offline
|
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
Posts: 22
Joined: 7/9/2004
Status: offline
|
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???
| |
|
|
|
BobGeezer
Posts: 22
Joined: 7/9/2004
Status: offline
|
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
Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
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)
_____________________________
If you arent part of the solution, then there is good money to be made prolonging the problem
§þ k¥
| |
|
|
|
BobGeezer
Posts: 22
Joined: 7/9/2004
Status: offline
|
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
| |
|
|
|
BobGeezer
Posts: 22
Joined: 7/9/2004
Status: offline
|
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
Posts: 22
Joined: 7/9/2004
Status: offline
|
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
Posts: 22
Joined: 7/9/2004
Status: offline
|
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 . . .
| |
|
|
|
BobGeezer
Posts: 22
Joined: 7/9/2004
Status: offline
|
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
Posts: 22
Joined: 7/9/2004
Status: offline
|
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.
| |
|
|
|
Sol_1
Posts: 2
Joined: 3/2/2006
Status: offline
|
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!
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
