Solved

ASP and Updates

Posted on 1998-10-01
13
155 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 

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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

695 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