Solved

Inserting an apostrophe causes error

Posted on 2007-11-20
4
648 Views
Last Modified: 2010-08-05
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
Comment
Question by:seanpowell
  • 2
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 500 total points
Comment Utility
<%
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
 
LVL 23

Expert Comment

by:Ashish Patel
Comment Utility
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
 
LVL 31

Author Closing Comment

by:seanpowell
Comment Utility
Thanks - had a hard time figuring out how to incorporate that.
And lightening fast too :-)
0
 
LVL 31

Author Comment

by:seanpowell
Comment Utility
Wonderful - thank you so much ;-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

771 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

12 Experts available now in Live!

Get 1:1 Help Now