[RESOLVED] DB doesn't update

[Rookie77]

Hello All,

can anyone tell me why a MS Access DB would crash when I submit a form using asp that has an ' (Apostrophe) in the value of the form fields?

Thank you!

[gil davis]

Because the query engine thinks it is the beginning of a string and can't find the closing one. You need to "escape" all apostrophes in your queries.

http://www.webdeveloper.com/forum/showthread.php?t=134031&highlight=apostrophe

[Rookie77]

I can't figure it out. I tried placing the code and it didn't work. continues to give error

[buntine]

In Access, special characters are escaped by duplicating them, so you will need to do something like this:

sSQL = Replace(sSQL, "'", "''")

So store your SQL query in a string variable, escape the single quotes, and then execute it.

Cheers,
Andrew.

[Rookie77]

I'm just a beginner, can you please show me where to put the code....

Here is what I have......

<%
on error resume next
set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0"
conn.open server.mappath("db/db.mdb")

InfoProvider=Request.Form("InfoProvider")
FocusArea=Request.Form("FocusArea")
Posted= NOW

sql="INSERT INTO FOCUS (InfoProvider,FocusArea,Posted) VALUES "
sql=sql & "('" & InfoProvider & "','" & FocusArea & "','" & Posted & "')"

conn.Execute sql,recordsaffected
if err <> 0 then
Response.Write("An Error Occured When Attempting to write to the Database!")
else
Response.Write("Record Updated!")
end if
conn.close
%>



Thank you

[buntine]

If I understand correct, you will need to replace what you currently have with this:

InfoProvider=Replace(Request.Form("InfoProvider"), "'", "''")
FocusArea=Replace(Request.Form("FocusArea"), "'", "''")

Cheers.

[Rookie77]

Thank you very much!