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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ok, I see:
the values should be enquoted when they are string values:
sql = sql & "vchAddress1 = '" & (Session("Address1") & "', "
(etc)
the values should be enquoted when they are string values:
sql = sql & "vchAddress1 = '" & (Session("Address1") & "', "
(etc)
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 ).
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.
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
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.
escaped_var = REPLACE(var,CHR(34),CHR(34
This will replace all quotes with a pair of quotes, which is "escape" for VBS.
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.
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.
It is dying ' peterz@hgakhg there.
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.
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.
It is dying ' peterz@hgakhg there.
your last line of sql must be:
sql = sql & "vchEmailAddress = '" & (Session("Email") & "'"
sql = sql & "vchEmailAddress = '" & (Session("Email") & "'"
Oh, and before you update ALL records, you should add a WHERE statement...:)
ASKER
I believe I am close, I have added my Where clause
WHERE Convert(varchar,Individual .iIndividu alID)= '" & Session("Username") & "'"
But get this error
Microsoft VBScript compilation error '800a03ea'
Syntax error
/NORMA/Account/DemoUpdate. asp, line 41
WHERE Convert(varchar,Individual .iIndividu alID)= '" & Session("Username") & "'"
-------------------------- ---------- ---------- ---^
Any last help would be great
WHERE Convert(varchar,Individual
But get this error
Microsoft VBScript compilation error '800a03ea'
Syntax error
/NORMA/Account/DemoUpdate.
WHERE Convert(varchar,Individual
--------------------------
Any last help would be great
sql = sql & "vchEmailAddress = " & (Session("Email") & "' "
sql = sql & "WHERE Convert(varchar,Individual .iIndividu alID)= '" & Session("Username") & "'"
sql = sql & "WHERE Convert(varchar,Individual
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 .iIndividu alID)='" & Session("Username") & "'"
DBC.Execute(sql)
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
DBC.Execute(sql)
ASKER
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.
And Papa is part of Address1. Does this take into account spaces and numbers?