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
Solved

ASP and Updates

Posted on 1998-10-01
13
151 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
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).

828 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