Solved

ASP and Updates

Posted on 1998-10-01
13
147 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Problem to be resolved in this article Currently, development of website and web application can be done without writing thousands of lines of programming code by hand. Description This can be done through by using a open source framework such …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now