wcameron
asked on
Using single quotes causes error in sql Insert Statement
I have a two part form that inserts the record into a database. If someone uses a single quotation ' in the comment field, it causes the following error message;
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in string in query expression ''lkajdfljaf'')'.
/mountainnature/bookings/P rocessBook ing.asp, line 77
Is there a way to write my code so that it does not causes errors. Here is my code:
<%option explicit
dim DestID, FirstName, LastName, OtherNames, PostalCode, Comments, NumberGuests, _
ContactTime, Title, City, Daytime, Evening, Cell, Fax, Province, Country, Ages, StartDate, _
EndDate, NumberDays, Email, MinimumDeposit, Activity, Address1, Address2, objConn, strConn, rs, SQLStmt
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<%
DestID = Request.Form("DestID")
StartDate = Request.Form("StartDate")
EndDate = Request.Form("EndDate")
NumberGuests = Request.Form("NumberGuests ")
Ages = Request.Form("Ages")
Activity = Request.Form("Activity")
NumberDays = Request.Form("NumberDays")
MinimumDeposit = Request.Form("MinimumDepos it")
Title = Request.Form("Title")
FirstName = Request.Form("FirstName")
LastName = Request.Form("LastName")
OtherNames = Request.Form("OtherNames")
Address1 = Request.Form("Address1")
Address2 = Request.Form("Address2")
City = Request.Form("City")
Province = Request.Form("Province")
Country = Request.Form("Country")
PostalCode = Request.Form("PostalCode")
Daytime = Request.Form("Daytime")
Evening = Request.Form("Evening")
Cell = Request.Form("Cell")
Fax = Request.Form("Fax")
Email = Request.Form("Email")
ContactTime = Request.Form("ContactTime" )
Comments = Request.Form("Comments")
Set objConn = Server.CreateObject("ADODB .Connectio n")
objConn.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" & _
Server.MapPath ("../fpdb/Booking.mdb") & ";"
objConn.Open
SQLStmt = "INSERT INTO tBookings(DestID, StartDate, EndDate, NumberGuests, Ages, Activity, NumberDays, Title, FirstName, LastName, OtherNames, Address1, Address2, City, Province, Country, PostalCode, Daytime, Evening, Cell ,Fax, Email, ContactTime, Comments)"
SQLStmt = SQLStmt & " values ("
SQLStmt = SQLStmt & "'" & DestID & "', "
SQLStmt = SQLStmt & "'" & StartDate & "', "
SQLStmt = SQLStmt & "'" & EndDate & "', "
SQLStmt = SQLStmt & "'" & NumberGuests & "', "
SQLStmt = SQLStmt & "'" & Ages & "', "
SQLStmt = SQLStmt & "'" & Activity & "', "
SQLStmt = SQLStmt & "'" & NumberDays & "', "
SQLStmt = SQLStmt & "'" & Title & "', "
SQLStmt = SQLStmt & "'" & FirstName & "', "
SQLStmt = SQLStmt & "'" & LastName & "', "
SQLStmt = SQLStmt & "'" & OtherNames & "', "
SQLStmt = SQLStmt & "'" & Address1 & "', "
SQLStmt = SQLStmt & "'" & Address2 & "', "
SQLStmt = SQLStmt & "'" & City & "', "
SQLStmt = SQLStmt & "'" & Province & "', "
SQLStmt = SQLStmt & "'" & Country & "', "
SQLStmt = SQLStmt & "'" & PostalCode & "', "
SQLStmt = SQLStmt & "'" & Daytime & "', "
SQLStmt = SQLStmt & "'" & Evening & "', "
SQLStmt = SQLStmt & "'" & Cell & "', "
SQLStmt = SQLStmt & "'" & Fax & "', "
SQLStmt = SQLStmt & "'" & Email & "', "
SQLStmt = SQLStmt & "'" & ContactTime & "', "
SQLStmt = SQLStmt & "'" & Comments & "'"
SQLStmt = SQLStmt & ")"
Set RS = ObjConn.execute(SQLStmt)
If err.number>0 then
response.write "VBScript Errors Occurred:" & "<p>"
response.write "Error Number=" & err.number & "<p>"
response.write "Error Descr.=" & err.description & "<p>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
response.write "SQLState=" & err.sqlstate & "<P>"
end if
If objConn.errors.count>0 then
response.write "Database Errors Occurred" & "<P>"
response.write SQLStmt & "<P>"
for counter=0 to objConn.errors.count
response.write "Error #" & objConn.errors(counter).nu mber & "<P>"
response.write "Error desc. -> " & objConn.errors(counter).de scription & "<P>"
next
else
response.write "<font face='arial'<b>"
response.write "The booking has been recorded. You will receive an email shortly that will give you additional details about your upcoming activity. MountainNature.com works with only the very best activity suppliers. Your satisfaction is our number one concern. If you are in any unsatisfied with our partner suppliers, please feel free to contact us with any questions you may have.</b><P>"
end if
objConn.close
set objConn=nothing
set rs=nothing
response.end
%>
</body>
</html>
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in string in query expression ''lkajdfljaf'')'.
/mountainnature/bookings/P
Is there a way to write my code so that it does not causes errors. Here is my code:
<%option explicit
dim DestID, FirstName, LastName, OtherNames, PostalCode, Comments, NumberGuests, _
ContactTime, Title, City, Daytime, Evening, Cell, Fax, Province, Country, Ages, StartDate, _
EndDate, NumberDays, Email, MinimumDeposit, Activity, Address1, Address2, objConn, strConn, rs, SQLStmt
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<%
DestID = Request.Form("DestID")
StartDate = Request.Form("StartDate")
EndDate = Request.Form("EndDate")
NumberGuests = Request.Form("NumberGuests
Ages = Request.Form("Ages")
Activity = Request.Form("Activity")
NumberDays = Request.Form("NumberDays")
MinimumDeposit = Request.Form("MinimumDepos
Title = Request.Form("Title")
FirstName = Request.Form("FirstName")
LastName = Request.Form("LastName")
OtherNames = Request.Form("OtherNames")
Address1 = Request.Form("Address1")
Address2 = Request.Form("Address2")
City = Request.Form("City")
Province = Request.Form("Province")
Country = Request.Form("Country")
PostalCode = Request.Form("PostalCode")
Daytime = Request.Form("Daytime")
Evening = Request.Form("Evening")
Cell = Request.Form("Cell")
Fax = Request.Form("Fax")
Email = Request.Form("Email")
ContactTime = Request.Form("ContactTime"
Comments = Request.Form("Comments")
Set objConn = Server.CreateObject("ADODB
objConn.ConnectionString = "Provider=Microsoft.Jet.OL
Server.MapPath ("../fpdb/Booking.mdb") & ";"
objConn.Open
SQLStmt = "INSERT INTO tBookings(DestID, StartDate, EndDate, NumberGuests, Ages, Activity, NumberDays, Title, FirstName, LastName, OtherNames, Address1, Address2, City, Province, Country, PostalCode, Daytime, Evening, Cell ,Fax, Email, ContactTime, Comments)"
SQLStmt = SQLStmt & " values ("
SQLStmt = SQLStmt & "'" & DestID & "', "
SQLStmt = SQLStmt & "'" & StartDate & "', "
SQLStmt = SQLStmt & "'" & EndDate & "', "
SQLStmt = SQLStmt & "'" & NumberGuests & "', "
SQLStmt = SQLStmt & "'" & Ages & "', "
SQLStmt = SQLStmt & "'" & Activity & "', "
SQLStmt = SQLStmt & "'" & NumberDays & "', "
SQLStmt = SQLStmt & "'" & Title & "', "
SQLStmt = SQLStmt & "'" & FirstName & "', "
SQLStmt = SQLStmt & "'" & LastName & "', "
SQLStmt = SQLStmt & "'" & OtherNames & "', "
SQLStmt = SQLStmt & "'" & Address1 & "', "
SQLStmt = SQLStmt & "'" & Address2 & "', "
SQLStmt = SQLStmt & "'" & City & "', "
SQLStmt = SQLStmt & "'" & Province & "', "
SQLStmt = SQLStmt & "'" & Country & "', "
SQLStmt = SQLStmt & "'" & PostalCode & "', "
SQLStmt = SQLStmt & "'" & Daytime & "', "
SQLStmt = SQLStmt & "'" & Evening & "', "
SQLStmt = SQLStmt & "'" & Cell & "', "
SQLStmt = SQLStmt & "'" & Fax & "', "
SQLStmt = SQLStmt & "'" & Email & "', "
SQLStmt = SQLStmt & "'" & ContactTime & "', "
SQLStmt = SQLStmt & "'" & Comments & "'"
SQLStmt = SQLStmt & ")"
Set RS = ObjConn.execute(SQLStmt)
If err.number>0 then
response.write "VBScript Errors Occurred:" & "<p>"
response.write "Error Number=" & err.number & "<p>"
response.write "Error Descr.=" & err.description & "<p>"
response.write "Help Context=" & err.helpcontext & "<P>"
response.write "Help Path=" & err.helppath & "<P>"
response.write "Native Error=" & err.nativeerror & "<P>"
response.write "Source=" & err.source & "<P>"
response.write "SQLState=" & err.sqlstate & "<P>"
end if
If objConn.errors.count>0 then
response.write "Database Errors Occurred" & "<P>"
response.write SQLStmt & "<P>"
for counter=0 to objConn.errors.count
response.write "Error #" & objConn.errors(counter).nu
response.write "Error desc. -> " & objConn.errors(counter).de
next
else
response.write "<font face='arial'<b>"
response.write "The booking has been recorded. You will receive an email shortly that will give you additional details about your upcoming activity. MountainNature.com works with only the very best activity suppliers. Your satisfaction is our number one concern. If you are in any unsatisfied with our partner suppliers, please feel free to contact us with any questions you may have.</b><P>"
end if
objConn.close
set objConn=nothing
set rs=nothing
response.end
%>
</body>
</html>
Actualy you can use ' to replace with the ASCII single Quote Value as well
DestID = Replace(Request.Form("Dest ID"),"'", "'")
Do it for all the Fields.
DestID = Replace(Request.Form("Dest
Do it for all the Fields.
Helloo
adilkhan is very right to replace single quote into its ASCII value..
Also you can do it like this
DestId = Replace(Request.Form("Dest ID"),"'", "''"))
Here we replace single quote into two times single quote which would be interperated as a single quote when we fire the insert statement. Your data base in this case will save exact value.. i mean only single quote and you wont be required to replace anything when you want to display records from database..
Also you would need to replace double quote in the values you are passing in your insert statement.. else it would also create problems..
DestId = Replace(Request.Form("Dest ID"),""", """"))
Hope it will work for u
neeraj523
adilkhan is very right to replace single quote into its ASCII value..
Also you can do it like this
DestId = Replace(Request.Form("Dest
Here we replace single quote into two times single quote which would be interperated as a single quote when we fire the insert statement. Your data base in this case will save exact value.. i mean only single quote and you wont be required to replace anything when you want to display records from database..
Also you would need to replace double quote in the values you are passing in your insert statement.. else it would also create problems..
DestId = Replace(Request.Form("Dest
Hope it will work for u
neeraj523
Try this:
<!--#include file="adovbs.inc" -->
<%
Set objConn = Server.CreateObject("ADODB .Connectio n")
objConn.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" & _
Server.MapPath ("database/db2.mdb") & ";"
objConn.Open
' Open the table.
Set rs = Server.CreateObject("ADODB .Recordset ")
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "table1", objConn, , , adCmdTable
rs.addnew
rs("name") = Request.Form("name")
rs("age") = Request.Form("age")
rs.update
rs.close
objConn.close
%>
You will find the server side include in the following path
c:program files\common files\system\ado
copy that 'n' put it in your directory
hope this will definitely solve your problem
cheers!!
<!--#include file="adovbs.inc" -->
<%
Set objConn = Server.CreateObject("ADODB
objConn.ConnectionString = "Provider=Microsoft.Jet.OL
Server.MapPath ("database/db2.mdb") & ";"
objConn.Open
' Open the table.
Set rs = Server.CreateObject("ADODB
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "table1", objConn, , , adCmdTable
rs.addnew
rs("name") = Request.Form("name")
rs("age") = Request.Form("age")
rs.update
rs.close
objConn.close
%>
You will find the server side include in the following path
c:program files\common files\system\ado
copy that 'n' put it in your directory
hope this will definitely solve your problem
cheers!!
replace the db name and table name with yours
ASKER
Thanks all.
neeraj523, you suggest that I replace both the single and double quotes, but how do I do that in my single variable statement?
DestId = Replace(Request.Form("Dest ID"),"'", "''"))
replaces a single quote, but I don't know how to alter it to change the double quote simultaneously.
neeraj523, you suggest that I replace both the single and double quotes, but how do I do that in my single variable statement?
DestId = Replace(Request.Form("Dest
replaces a single quote, but I don't know how to alter it to change the double quote simultaneously.
DestId = Replace(Request.Form("Dest ID"),"'", "''"))
DestId = Replace(Request.Form("Dest ID"),""", """"))
DestId = Replace(Request.Form("Dest
ASKER
If I follow your example, won't the second version simply replace the first and delete the first replace statement?
What about this example?
DestID = Trim(Replace(Request.Form( "DestID"), """",""""" "))
What about this example?
DestID = Trim(Replace(Request.Form(
hello wcameron,
did u try my method
did u try my method
ASKER
Hi Nandhini, your solution does not relate to my question. I already know how to add records to the database. I simply need to know how to deal with errors causes when the values include punctuation like single quotes ' that mess up the sql statement. I do appreciate you taking the time to reply though. It took me two years to get this far so I'm keen to move it to the next level.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks neeraj. Just one final question. When do double quotes cause problems? I've been trying to crash my form using them but have not yet been able to. It seems that the single quotes are much more problematic.
Here is my url:
www.MountainNature.com/bookings
Here is my url:
www.MountainNature.com/bookings
Hello wcameron
double quote creates problem when you are trying to insert a text value to the database with a double quotes.. ASP engine assumes this double quotes as the terminating quote of the sql stamtement and gets confused about it..
You can try to passing double quotes in a value of datatype text..
neeraj523
double quote creates problem when you are trying to insert a text value to the database with a double quotes.. ASP engine assumes this double quotes as the terminating quote of the sql stamtement and gets confused about it..
You can try to passing double quotes in a value of datatype text..
neeraj523
Dear wcameron
still you are looking for any further help ??
neeraj523
still you are looking for any further help ??
neeraj523
Example
DestID = Request.Form("DestID")
Should be
DestID = Replace(Request.Form("Dest