?
Solved

semicolon in sql update statement ASP

Posted on 2014-11-15
3
Medium Priority
?
320 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
[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
3 Comments
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 664 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 668 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 53

Accepted Solution

by:
Scott Fell,  EE MVE earned 668 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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

743 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