|
| |
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
 |
Syntax Error: Update SQL Statement in ASP - 8/19/2003 16:14:45
Hello -
I've got some ASP code that I use to submit to both a database and email. I've used this code with no problem to add records, but I've run into syntax errors when attempting to update records. The syntax error I've been getting is "Incorrect syntax near the keyword 'WHERE'. "
I'm an ASP newbie, so the answer may be obvious but I've been unable to find an example in the forums. See below code and thank you in advance for any help. These forums are a lifesaver!
mySQL = "UPDATE DISTINCTROW [T_SALE_TRCK_FORM] "
mySQL = mySQL & "SET ( BIZ_OTHR_PROD2_TXT, BIZ_OTHR_PROD_TXT, RQST_CLOS_FLG ) "
mySQL= mySQL & "VALUES ('" & Request.Form("BIZ_OTHR_PROD2_TXT") & "','" & Request.Form("BIZ_OTHR_PROD_TXT") & "','" & Request.Form("RQST_CLOS_FLG") & "')"
mySQL= "WHERE STF_ID = '" & Request.Form("STF_ID") & "'"
< Message edited by Loert -- 8/19/2003 4:17:00 PM >
|
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/20/2003 11:17:59
quote:
mySQL= "WHERE STF_ID = '" & Request.Form("STF_ID") & "'"
Response.write mySQL
Response.end
Thanks Spooky, I appreciate your feedback. The string written to the page after I added the Response.write statements was "WHERE STF_ID = ' 1 ' " Which is correct, this is the record I'm dealing with I guess. But where is the rest of the string?
Here is the entire code for this ASP script:
Dim myConnString
Dim myConnection
Dim mySQL
myConnString = Application("TELEBANKPRODUCTION_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
mySQL = "UPDATE DISTINCTROW [T_SALE_TRCK_FORM] "
mySQL = mySQL & "SET ( BIZ_OTHR_PROD2_TXT, BIZ_OTHR_PROD_TXT, RQST_CLOS_FLG ) "
mySQL= mySQL & "VALUES ('" & Request.Form("BIZ_OTHR_PROD2_TXT") & "','" & Request.Form("BIZ_OTHR_PROD_TXT") & "','" & Request.Form("RQST_CLOS_FLG") & "')"
mySQL= " WHERE STF_ID = ' " & Request.Form("STF_ID") & " ' "
myConnection.Execute mySQL
myConnection.Close
Set myConnection = Nothing
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/20/2003 11:42:27
quote:
mySQL= " WHERE STF_ID = ' " & Request.Form("STF_ID") & " ' "
Is STF_ID a numerical value? If so try:
mySQL= " WHERE STF_ID = " & Request.Form("STF_ID")
Just a quick guess. The apostrophe's are for text. Hope it helps...
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/20/2003 12:58:15
Ok. I think I'm making some progress. I'm now getting the following: "Incorrect syntax near 'T_SALE_TRCK_FORM'. " Thanks for the tip rdouglass, you were right with the STF_ID being a number. I've added the following to the where clause: "mySQL &" so when I run the response.write, I can see the SQL statement in its entirety:
UPDATE DISTINCTROW [T_SALE_TRCK_FORM] SET ( BIZ_OTHR_PROD2_TXT, BIZ_OTHR_PROD_TXT, RQST_CLOS_FLG ) VALUES ('UGRL41','First.Last@somecompany.com','') WHERE STF_ID = 1
Here is the code as it stands now:
Dim myConnString
Dim myConnection
Dim mySQL
myConnString = Application("TELEBANKPRODUCTION_ConnectionString")
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open myConnString
mySQL = " UPDATE DISTINCTROW T_SALE_TRCK_FORM "
mySQL = mySQL & "SET ( BIZ_OTHR_PROD2_TXT, BIZ_OTHR_PROD_TXT, RQST_CLOS_FLG ) "
mySQL= mySQL & "VALUES ('" & Request.Form("BIZ_OTHR_PROD2_TXT") & "','" & Request.Form("BIZ_OTHR_PROD_TXT") & "','" & Request.Form("RQST_CLOS_FLG") & "')"
mySQL= mySQL & " WHERE STF_ID = " & Request.Form("STF_ID")
myConnection.Execute mySQL
myConnection.Close
When I run this code, I get a new syntax error referring to the table itself:
Incorrect syntax near 'T_SALE_TRCK_FORM'.
Then I add the response.write to see the string.
This is puzzeling. It's almost if I don't have rights to update the database. I'll follow up with the DBA. Any other thoughts guys?
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/20/2003 13:01:57
quote:
UPDATE DISTINCTROW [T_SALE_TRCK_FORM] SET ( BIZ_OTHR_PROD2_TXT, BIZ_OTHR_PROD_TXT, RQST_CLOS_FLG ) VALUES ('UGRL41','First.Last@somecompany.com','') WHERE STF_ID = 1
One more thing I see; the RQST_CLOS_FLG value is blank. Does that DB field aloow "Zero Length" or Nulls? I can't tell by your code whether you're using SQL Server or Access - you're using the global.asa application connection...
Any help there?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/20/2003 15:03:08
That was another good call, rdouglass. I noticed it right before you made your post and modified the page to correctly pass a value.
I was pretty sure this was the issue, however still getting this:
Line 1: Incorrect syntax near 'T_SALE_TRCK_FORM'.
This is running the same code as in the above posts. Here is the full SQL Statement after adding response.write (BELOW). Notice that I have a value for RQST_CLOS_FLG now. The funny thing is that I can add records at will, so I know all my data types and lengths are OK......
DATE DISTINCTROW [T_SALE_TRCK_FORM] SET ( BIZ_OTHR_PROD2_TXT, BIZ_OTHR_PROD_TXT, RQST_CLOS_FLG ) VALUES ('UGRL41','First.Last@company.com','Y') WHERE STF_ID = 6
| |
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/20/2003 17:27:05
Ahh, Ok. So we are looking at this instead:
mySQL = "UPDATE DISTINCTROW [T_SALE_TRCK_FORM] "
mySQL = mySQL & "SET ( BIZ_OTHR_PROD2_TXT = '" & Request.Form("BIZ_OTHR_PROD2_TXT") & "', BIZ_OTHR_PROD_TXT = '" & Request.Form("BIZ_OTHR_PROD_TXT") & "', RQST_CLOS_FLG = '" & Request.Form("RQST_CLOS_FLG") & "' ) "
mySQL= mySQL & " WHERE STF_ID = " & Request.Form("STF_ID")
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/21/2003 10:21:59
Does that work?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/21/2003 10:49:51
rdouglas - Thank you for the follow-up. Again I appreciate your insight.
Did I get it working? Well no, for some undiscovered reason. I'm still getting: Incorrect syntax near 'T_SALE_TRCK_FORM'
I believe the query is clean. Below is the query when I response.write it:
UPDATE DISTINCTROW [T_SALE_TRCK_FORM] SET ( BIZ_OTHR_PROD2_TXT = 'UGRL41', BIZ_OTHR_PROD_TXT = 'First.Last@company.com', RQST_CLOS_FLG = 'Y' ) WHERE STF_ID = 2
The funny thing is that I created another page that was a hybrid of CDONTS/ASP code (For the email functionality) + DRW update code (instead of the above query) and it worked. The DRW is messing up some of my CDONTs code formatting on the confirmation page, but otherwise it works.
So, since the DWR update works fine, I can only assume that everything is correct except the above query. But the query looks OK now too.
????
| |
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/21/2003 13:07:12
quote:
RQST_CLOS_FLG = 'Y'
Is that field really a text field and not a Yes/No field? Other than that, the SQL looks OK to me.
Spooky?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/21/2003 13:38:49
quote:
RQST_CLOS_FLG = 'Y'
This is set in the Database as a Char(1) field. I've added records to this field with no problem using both SQL and DRW. Just for a test, I removed the ' ' in the SQL statement around this field just to see if that was the issue:
RQST_CLOS_FLG = " & Request.Form("RQST_CLOS_FLG") & "
Ended up with the same error message: Line 1: Incorrect syntax near 'T_SALE_TRCK_FORM'.
This is getting weird and about to drive me crazy. I may have to give up and go with DRW, which seems to be updating fine. I don't like the idea of defeat however . . .
And again, both your and Spooky's insight is very much appreciated.
| |
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/21/2003 17:22:47
Still doesn't like it.
Here is the Query, clean as can be:
UPDATE [T_SALE_TRCK_FORM] SET ( BIZ_OTHR_PROD2_TXT = 'UGRL41', BIZ_OTHR_PROD_TXT = 'first.last@company.com', RQST_CLOS_FLG = 'Y' ) WHERE STF_ID = 1
Here is the query in ASP:
mySQL = "UPDATE [T_SALE_TRCK_FORM] "
mySQL = mySQL & "SET ( BIZ_OTHR_PROD2_TXT = '" & Request.Form("BIZ_OTHR_PROD2_TXT") & "', BIZ_OTHR_PROD_TXT = '" & Request.Form("BIZ_OTHR_PROD_TXT") & "', RQST_CLOS_FLG = '" & Request.Form("RQST_CLOS_FLG") & "' ) "
mySQL= mySQL & " WHERE STF_ID = " & Request.Form("STF_ID")
Looks like I'll be settling on DRW for this update.
Don't know what else can be done.....
Thanks guys.
Loert
| |
|
|
|
Loert
Posts: 19
From: Atlanta, GA, USA
Status: offline
|
RE: Syntax Error: Update SQL Statement in ASP - 8/22/2003 9:07:17
Yup, SQL.
Once I removed the "DISTINCTROW" the error message changed to this:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '('.
/forms/Sales_Desktop/MailBBRBusiness.asp, line 50 \
Line 50 of course is the "myConnection.Execute mySQL"
Just for giggles, here is the error message with the "DISTINCTROW" included:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'T_SALE_TRCK_FORM'.
/forms/Sales_Desktop/MailBBRBusiness.asp, line 50
| |
|
 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 
k¥ 
That format is for INSERT. For UPDATE the format should look like: 