?
Solved

Errors inserting into a table (Access db) using charters such as a ' (single quote)

Posted on 2009-04-14
10
Medium Priority
?
396 Views
Last Modified: 2012-05-06
How come I get an error inserting data into an Access db table if one of my fields contains a single quote (')?  Ex: Susan's dog.  

Is there a work around so the error does not occur (if a single quote is used) and the data is inserted without issue?
<%
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\test\registration\db\test.mdb") & ""
set conn = server.createobject("adodb.connection")
conn.open strconn
 
  
strSQLI = "insert into registrants (conf_id, firstname, middleinitial, lastname, address, city, state, zip, phonenumber, phoneext, Email) values ('"& request.Form("conf_id") &"', '"& request.Form("firstname") &"', '"& request.Form("middleinitial") &"', '"& request.Form("lastname") &"', '"& request.Form("address") &"', '"& request.Form("city") &"', '"& request.Form("state") &"', '"& request.Form("zip") &"', '"& request.Form("phonenumber") &"', '"& request.Form("phoneext") &"', '"& request.Form("Email") &"')"
 
conn.execute(strSQLI)
%>

Open in new window

0
Comment
Question by:arendt73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24143228
you need to do a replace function
Add this right above your SQL Statement

strFN = request.form("firstname")

Then
Replace your code for:

"& request.Form("firstname") &"

With

"& replace(strFN,"'", "''") &"


Good Luck
Carrzkiss
0
 

Author Comment

by:arendt73
ID: 24143329
I get the following error:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'O''Brien'.

Below is my mod to the SQL:
<%
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\test\registration\db\test.mdb") & ""
set conn = server.createobject("adodb.connection")
conn.open strconn
 
strLN = request.form("lastname")
  
strSQLI = "insert into registrants (conf_id, firstname, middleinitial, lastname, address, city, state, zip, phonenumber, phoneext, Email) values ('"& request.Form("conf_id") &"', '"& request.Form("firstname") &"', '"& request.Form("middleinitial") &"', "& replace(strLN,"'", "''") &", '"& request.Form("address") &"', '"& request.Form("city") &"', '"& request.Form("state") &"', '"& request.Form("zip") &"', '"& request.Form("phonenumber") &"', '"& request.Form("phoneext") &"', '"& request.Form("Email") &"')"
 
conn.execute(strSQLI)
%>
 

Open in new window

0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24143460
Is it the lastname that you are needed to work with here?
That was an example that I gave, so let me know when field that you are sending the ' to
0
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 

Author Comment

by:arendt73
ID: 24143760
Actually, I would like to filter the first and last names as well as street and city.  They are the usual fields that would contain a (').

Thank you.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24144029
simple
Replace yours with ALL of this.

Includes below;

Carrzkiss

 
<%
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\test\registration\db\test.mdb") & ""
set conn = server.createobject("adodb.connection")
conn.open strconn
 
strFN = request.form("firstname")
strLN = request.form("lastname")
strCity = request.form("city")
strStreet = request.form("address")
  
strSQLI = "insert into registrants (conf_id, firstname, middleinitial, lastname, address, city, state, zip, phonenumber, phoneext, Email) values ('"& request.Form("conf_id") &"', '"& replace(strFN,"'", "''") &"', '"& request.Form("middleinitial") &"', "& replace(strLN,"'", "''") &", '"& replace(strStreet,"'", "''") &"', '"& replace(strCity,"'", "''") &"', '"& request.Form("state") &"', '"& request.Form("zip") &"', '"& request.Form("phonenumber") &"', '"& request.Form("phoneext") &"', '"& request.Form("Email") &"')"
 
conn.execute(strSQLI)
%>

Open in new window

0
 

Author Comment

by:arendt73
ID: 24144278
Only tried the lastname field and same result.  Applied the code and again received the following error:

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

I am applying the code exactly as described.  My inserts were working correctly prior to modifications.  Just wanted to let you know the form really works.

Thank you again for your efforts.  I know you are very close.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24144296
please send me over a copy of your Database.
And a sample of your form that you are using to submit the information
(In the Form, pre-fill in all fields, (Fill in the Form-Fields Values with what you are trying))
As well as your complete Insert into statement.

I am not going to be able to help you with this unless I have something to look at.

Carrzkiss
0
 
LVL 31

Accepted Solution

by:
Wayne Barron earned 1600 total points
ID: 24144404
here you go
http://ee.cffcs.com/Q_24322329/insert.asp
code
http://ee.cffcs.com/Q_24322329/Q_24322329.zip

I forgot the ' that was needed in the INSERT statement.

Anyway.
Works like a charm now.

Carrzkiss
0
 

Author Closing Comment

by:arendt73
ID: 31570184
Perfect.  Exactly what I needed.  Thank you.
0
 
LVL 31

Expert Comment

by:Wayne Barron
ID: 24144455
You are very welcome.
We should of had this one taken care of earlier.
I forgot to add in the

 "& replace(strLN,"'", "''") &"

In the above string I forgot to add in the single Quote that was needed  to be around
The field since it is a Type Text.
Should be (And is corrected in the code)

' "& replace(strLN,"'", "''") &" '

As you can see now, there is a single Quote on each side of the string.

have a good one.

Carrzkiss

Author Comments:
Perfect. Exactly what I needed. Thank you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 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