arendt73
asked on
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?
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)
%>
ASKER
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:
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)
%>
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
That was an example that I gave, so let me know when field that you are sending the ' to
ASKER
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.
Thank you.
simple
Replace yours with ALL of this.
Includes below;
Carrzkiss
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)
%>
ASKER
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Exactly what I needed. Thank you.
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.
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.
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