Passing value with ' to Access

[bcw]

I'm having problems passing values entered into a form to my Access backend. It works fine unless a single quote is entered into the form. i can't seem to get the sql string correct. any help would be aprreciated.

I've tried the following:
sql = "UPDATE tblEmployees SET"
sql = sql & " Name = '" & Name & "'"
sql = sql & " WHERE EmpNumber =" & empID
--returns Works without ' entered, otherwise Syntax error (missing operator) in query expression ''O'Brien' WHERE EmpNumber =1000'.

sql = "UPDATE tblEmployees SET"
sql = sql & " Name = " & chr(39) & Name & chr(39)
sql = sql & " WHERE EmpNumber =" & empID
--returns Syntax error (missing operator) in query expression ''O'Brien' WHERE EmpNumber =1000'.

sql = "UPDATE tblEmployees SET"
sql = sql & " Name = " & chr(34) & Name & chr(34)
sql = sql & " WHERE EmpNumber =" & empID
--returns Too few parameters. Expected 1.

sql = "UPDATE tblEmployees SET"
sql = sql & " Name = """ & FirstName & """"
sql = sql & " WHERE EmpNumber =" & empID
--returns Too few parameters. Expected 1.

Thanks,

Brian

 

[fhlee]

If you read the code, if you enter a ', the resulting code will be:
:: sql = "UPDATE tblEmployees SET Name = 'Tommy's Name'"
It should look obvious why the error comes out.

Solution: You need to translate the ' character into %27 (I think) or &qout. U can use the replace( myString, "'", "%27&quot.

Alternatively, do an URLEncoding on all strings going into the database. Note, you need to URLUnEncode it before displaying it.

regards,
- Joseph ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Visit -->

http://www.replicaSi.com

for (Replica) Watches, Pen, Handbags, Hats, Jerseys and more ... at prices that makes your code spin ...
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[lobstah]

or you can replace( myString, "'", "''&quot before entering it in the access db.

 

[bcw]

Thanks guys, I couldnt' get it to work replacing with %27 but the "''" worked. Thanks again.

Brian