Solved

semicolon in sql update statement ASP

Posted on 2014-11-15
3
293 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

786 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