Solved

semicolon in sql update statement ASP

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

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 32

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

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

757 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

21 Experts available now in Live!

Get 1:1 Help Now