Link to home
Start Free TrialLog in
Avatar of yolish
yolish

asked on

ASP and Updates

I am trying to write an Update Statement to update a SQL table. What I have doesn't seem to work. Any advice would be helpful.

sql = "UPDATE Individual SET " &_
"vchAddress1 = " & (Session("Address1") ,",") & _
"vchAddress2 = " & (Session("Address2"), ",") &_
"vchAddress3 = " & (Session("Address3"), ",") &_
"vchCity = " & (Session("City"), ",") &_
"vchRegionCode = " & (Session("State"), ",") &_
"vchPostCode = " & (Session("Zip"), ",") &_
"vchEmailAddress = " & (Session("Email"), "") &_
Set rsDBC = DBC.Execute(sql)

ASKER CERTIFIED SOLUTION
Avatar of sybe
sybe

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of yolish
yolish

ASKER

But I am still getting an error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Papa'.

/NORMA/Account/DemoUpdate.asp, line 41

And Papa is part of Address1. Does this take into account spaces and numbers?

Ok, I see:

the values should be enquoted when they are string values:

sql = sql & "vchAddress1 = '" & (Session("Address1") & "', "

(etc)
Avatar of yolish

ASKER

sybe-

Thanks, that is definitely on the right track, still getting an error.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','.

/NORMA/Account/DemoUpdate.asp, line 41

Plus you need to remember to close your Parens. You have 2 (, and only 1 ).

You are right about the parens, sorry.

sql = sql & "vchAddress1 = '" & Session("Address1") & "', " 

should be it.

What always is useful is to display the sql string before the execute command.

sql = ....
Response.write sql & "<br>"
DBC.Execute(sql)
Then you can easily see where the mistake is.

If you still get errors then post the sql statement
a very common problem is when the variables itself have quotes inside. You'll have to use the REPLACE function to escape them:

escaped_var = REPLACE(var,CHR(34),CHR(34) & CHR(34))

This will replace all quotes with a pair of quotes, which is "escape" for VBS.
Avatar of yolish

ASKER

It would appear that that would be the case. Except for the last variable being passed.

UPDATE Individual SET vchAddress1 = '850 Papa Place', vchAddress2 = '', vchAddress3 = '', vchCity = 'Chicago', vchRegionCode = 'IL ', vchPostCode = '60605', vchEmailAddress = ' peterz@hgakhg

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quote before the character string ' peterz@hgakhg'.

/NORMA/Account/DemoUpdate.asp, line 42

It is dying  ' peterz@hgakhg there.
Avatar of yolish

ASKER

It would appear that that would be the case. Except for the last variable being passed.

UPDATE Individual SET vchAddress1 = '850 Papa Place', vchAddress2 = '', vchAddress3 = '', vchCity = 'Chicago', vchRegionCode = 'IL ', vchPostCode = '60605', vchEmailAddress = ' peterz@hgakhg

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quote before the character string ' peterz@hgakhg'.

/NORMA/Account/DemoUpdate.asp, line 42

It is dying  ' peterz@hgakhg there.
your last line of sql must be:

sql = sql & "vchEmailAddress = '" & (Session("Email") & "'"

Oh, and before you update ALL records, you should add a WHERE statement...:)
Avatar of yolish

ASKER

I believe I am close, I have added my Where clause
WHERE Convert(varchar,Individual.iIndividualID)= '" & Session("Username") & "'"
But get this error
Microsoft VBScript compilation error '800a03ea'

Syntax error

/NORMA/Account/DemoUpdate.asp, line 41

WHERE Convert(varchar,Individual.iIndividualID)= '" & Session("Username") & "'"
-------------------------------------------------^

Any last help would be great

sql = sql & "vchEmailAddress = " & (Session("Email") & "' "
sql = sql & "WHERE Convert(varchar,Individual.iIndividualID)= '" & Session("Username") & "'"

Avatar of yolish

ASKER

Here's what I did to get it to work. Thanks again for the help

sql = "UPDATE Individual SET " &_  
      "vchAddress1 = '" & (Session("Address1") & "', ") &_
      "vchAddress2 = '" & (Session("Address2") & "', ") &_
    "vchAddress3 = '" & (Session("Address3") & "', ") &_
    "vchCity = '" & (Session("City") & "', ") &_
    "vchRegionCode = '" & (Session("State") & "', ") &_
    "vchPostCode = '" & (Session("Zip") & "', ") &_
      "vchEmailAddress = '" & (Session("Email") & "'") &_
      "WHERE Convert(varchar,Individual.iIndividualID)='" & Session("Username") & "'"
DBC.Execute(sql)