Solved

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

Posted on 2009-04-14
10
391 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hbo knew my windows software 4 62
replica website 2 35
Help With Simple Database Design 7 50
WordPress: Debugging from my Windows 10 Desktop 6 33
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

856 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