Solved

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

Posted on 2009-04-14
10
389 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
  • 6
  • 4
10 Comments
 
LVL 30

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 30

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 30

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 30

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 30

Accepted Solution

by:
Wayne Barron earned 400 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 30

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

821 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