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

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

arendt73Asked:
Who is Participating?
 
Wayne BarronConnect With a Mentor Author, Web DeveloperCommented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
arendt73Author Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
arendt73Author Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
arendt73Author Commented:
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
 
Wayne BarronAuthor, Web DeveloperCommented:
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
 
arendt73Author Commented:
Perfect.  Exactly what I needed.  Thank you.
0
 
Wayne BarronAuthor, Web DeveloperCommented:
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
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.

All Courses

From novice to tech pro — start learning today.