Using an apostrophe in a string from an input field

[Guest_imported]

Can someone help me? I want to be able to use an apostrophe in my form input.

Here is my code.

namedrop=request.form("namedrop&quot

sql= "insert into orderitems (orderid,productid,qty,mysize,mycolor,priceperunit,namedrop) values (" & orderid & "," & bin(i,0) & "," & bin(i,1) & ",'" & cstr(mysize) & "','" & cstr(mycolor) & "'," & currentprice & ",'" & cstr(namedrop) & "')"
set rsorderitems=db.execute(sql)


The form is a text input field.
If i enter something like: Mommas Boy
then it works.
If I enter something like: Momma's Boy
then i get an error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Momma's Boy')'.

I thought that typecasting it with cstr would fix this. Why not?
Any help appreciated.

 

[TechnicalAnalysis]

If you expect and allow user to input punctuation like " or ', you can use a replace function to replace them by something else.
For example " convert to %%, ' convert to && given that you have JavaScript to validate the form to stop users from inputting those
%% and && on the field, namedrop.
You can convert them back to " and ' on your output table.

Alternatively, You may consider inserting the field, namedrop, to the database by recordset.AddNew and recordset.Update instead of using SQL.
The performance is definitely an issue for this method.

Hope it helps.


Regards

http://www.technical.analysis.dingojunction.com