Solved

semicolon in sql update statement ASP

Posted on 2014-11-15
3
295 Views
Last Modified: 2014-11-15
Greeting,
I have an ASP page contains sql update statement. The code breaks if the update values contains ';' (semicolon).
How to make it work?
Thanks.
0
Comment
Question by:mrong
3 Comments
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 166 total points
ID: 40444454
Isn't the update value captured as a string in single quotes? A semicolon in SQL indicates the end of a batch of statements, so if it's not captured properly as a string value the statement will stop to execute whatever comes before the semicolon.
0
 
LVL 33

Assisted Solution

by:Big Monty
Big Monty earned 167 total points
ID: 40444518
having a semi colon in your sql statement won't break it unless there is invalid sql present. can you post your sql statement and the exact error you're getting?
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 167 total points
ID: 40444545
It sounds like you may be creating your update with a straight update.

If you are building your update on the fly like this http://www.w3schools.com/asp/ado_update.asp
 sql="UPDATE customers SET "
  sql=sql & "companyname='" & Request.Form("companyname") & "',"
  sql=sql & "contactname='" & Request.Form("contactname") & "',"
  sql=sql & "address='" & Request.Form("address") & "',"
  sql=sql & "city='" & Request.Form("city") & "',"
  sql=sql & "postalcode='" & Request.Form("postalcode") & "',"
  sql=sql & "country='" & Request.Form("country") & "'"
  sql=sql & " WHERE customerID='" & cid & "'"
  on error resume next
  conn.Execute sql

Open in new window

You will get yourself in trouble.

Check out Wayne's article on this subject.  It is exactly what you need.  http://www.experts-exchange.com/Programming/Languages/Scripting/ASP/A_3626-ASP-Classic-Using-Parameterized-Queries.html
Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.commandtext="update ordercavecustomer set cusEmail=?, password=? where myID=?"
chEmail.Parameters.Append chEmail.CreateParameter("@cusEmail", adVarChar, adParamInput, 25, loginEmail)
chEmail.Parameters.Append chEmail.CreateParameter("@password", adVarChar, adParamInput, 25, loginPass)
chEmail.Parameters.Append chEmail.CreateParameter("@myID", adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute

Open in new window

0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

790 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