• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

Inserting an apostrophe causes error

Hi,

I'm getting this when someone uses an apostrophe in a memo field:

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression

Below is the basic code I'm using:
Can anyone help?
<%
Dim q1, q2, data_source, con, sql_insert
 
q1 		= Request.Form("q1")
q2 		= Request.Form("q2")
data_source     = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../db/cpc-survey.mdb")
sql_insert	= "INSERT INTO results (q1, q2, [Timestamp]) VALUES ('" & q1 & "', '" & q2 & "', Now())"
 
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
 
con.Close
Set con = Nothing
%>

Open in new window

0
seanpowell
Asked:
seanpowell
  • 2
  • 2
1 Solution
 
Ashish PatelCommented:
<%
Dim q1, q2, data_source, con, sql_insert
 
q1             = Request.Form("q1")
q2             = Request.Form("q2")
data_source     = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../db/cpc-survey.mdb")
sql_insert      = "INSERT INTO results (q1, q2, [Timestamp]) VALUES ('" & Replace(q1, "'", "") & "', '" & Replace(q2, "'", "") & "', Now())"
 
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
 
con.Close
Set con = Nothing
%>
 
0
 
Ashish PatelCommented:
Or the best thing to do is to replace one single quote with two single quote. this will resolve sql injection flaw too.

<%
Dim q1, q2, data_source, con, sql_insert
 
q1             = Request.Form("q1")
q2             = Request.Form("q2")
data_source     = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("../db/cpc-survey.mdb")
sql_insert      = "INSERT INTO results (q1, q2, [Timestamp]) VALUES ('" & Replace(q1, "'", "''") & "', '" & Replace(q2, "'", "''") & "', Now())"
 
Set con = Server.CreateObject("ADODB.Connection")
con.Open data_source
con.Execute sql_insert
 
con.Close
Set con = Nothing
%>
 
0
 
seanpowellAuthor Commented:
Thanks - had a hard time figuring out how to incorporate that.
And lightening fast too :-)
0
 
seanpowellAuthor Commented:
Wonderful - thank you so much ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now