Solved

ASP and Updates

Posted on 1998-10-01
13
150 Views
Last Modified: 2013-12-25
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)

0
Comment
Question by:yolish
  • 7
  • 6
13 Comments
 
LVL 28

Accepted Solution

by:
sybe earned 50 total points
ID: 1855380
sql = "UPDATE Individual SET " 
sql = sql & "vchAddress1 = " & (Session("Address1") & ", "
sql = sql & "vchAddress2 = " & (Session("Address2") & ", "
sql = sql & "vchAddress3 = " & (Session("Address3") & ", "
sql = sql & "vchCity = " & (Session("City") & ", "
sql = sql & "vchRegionCode = " & (Session("State") & ", "
sql = sql & "vchPostCode = " & (Session("Zip") & ", "
sql = sql & "vchEmailAddress = " & (Session("Email")
DBC.Execute(sql)


0
 

Author Comment

by:yolish
ID: 1855381
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?

0
 
LVL 28

Expert Comment

by:sybe
ID: 1855382
Ok, I see:

the values should be enquoted when they are string values:

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

(etc)
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:yolish
ID: 1855383
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 ).

0
 
LVL 28

Expert Comment

by:sybe
ID: 1855384
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
0
 
LVL 28

Expert Comment

by:sybe
ID: 1855385
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.
0
 

Author Comment

by:yolish
ID: 1855386
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.
0
 

Author Comment

by:yolish
ID: 1855387
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.
0
 
LVL 28

Expert Comment

by:sybe
ID: 1855388
your last line of sql must be:

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

0
 
LVL 28

Expert Comment

by:sybe
ID: 1855389
Oh, and before you update ALL records, you should add a WHERE statement...:)
0
 

Author Comment

by:yolish
ID: 1855390
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

0
 
LVL 28

Expert Comment

by:sybe
ID: 1855391
sql = sql & "vchEmailAddress = " & (Session("Email") & "' "
sql = sql & "WHERE Convert(varchar,Individual.iIndividualID)= '" & Session("Username") & "'"

0
 

Author Comment

by:yolish
ID: 1855392
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)

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question