Solved

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

Posted on 2009-04-14
10
388 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
 

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

920 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