Syntax Error: Update SQL Statement in ASP (Full Version)

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



Message

Loert -> 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") & "'"



Spooky -> RE: Syntax Error: Update SQL Statement in ASP (8/20/2003 4:01:19)

Try a space before where :

mySQL= " WHERE

If that fails, use 

mySQL= "WHERE STF_ID = '" & Request.Form("STF_ID") & "'" 

Response.write mySQL
Response.end


To see what the SQL string actually is.



Loert -> 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 -> 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...



Loert -> 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 -> 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?



Loert -> 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



rdouglass -> RE: Syntax Error: Update SQL Statement in ASP (8/20/2003 16:41:47)

quote:

UPDATE 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


Sorry! Big brain cramp. [:'(] That format is for INSERT. For UPDATE the format should look like:

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 = 6

Notice the diff??



Loert -> 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 -> RE: Syntax Error: Update SQL Statement in ASP (8/21/2003 10:21:59)

Does that work?



Loert -> 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 -> 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?



Loert -> 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.



Spooky -> RE: Syntax Error: Update SQL Statement in ASP (8/21/2003 15:51:48)

Remove distinctrow, its not needed.How does it like that?



Loert -> 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



Spooky -> RE: Syntax Error: Update SQL Statement in ASP (8/21/2003 19:07:14)

Its an SQL database?
I dont understand why its "Line 1: Incorrect syntax near 'T_SALE_TRCK_FORM'"

Is that the exact error when processing via the asp page?
If not, could you paste the actual (full) error?



Loert -> 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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375