Single quotes and apostrophes in ASP

[cwolgamott]

Hello. I am writing some ASP pages where I would like the user to be able to enter in a subject and description. Then, I would like to insert the subject and description into a SQL Server 2000 table. However, if the user enters an apostrophe or single quote in the subject or description, it errors out and will not insert the record into the table. Here is the code for how I am inserting the code into SQL:

SQL = " INSERT INTO CaseInfo (CaseInfo.casenum, CaseInfo.caller, CaseInfo.subject, CaseInfo.description, CaseInfo.assignedto, CaseInfo.openedby, CaseInfo.dateopened, CaseInfo.daterequested, CaseInfo.dateneeded, CaseInfo.dateestimated, CaseInfo.iscompleted, CaseInfo.respondedto, CaseInfo.percentcomplete, CaseInfo.status) "
SQL = SQL + "VALUES (" + CStr(newCaseNumber) + ", " + "'" + inputFullName2 + "', " + "'" + Request("subject&quot + "', " + "'" + Request("description&quot + "', " + "'" + "UNASSIGNED" + "', " + "'" + "INTRANET" + "', " + "GETDATE()" + ", " + "'" + dateVar + "', " + "'" + dateVar + "', " + "GETDATE()" + ", " + "'" + "N" + "', " + "'" + "N" + "', " + "0" + ", " + "'" + "NOT STARTED" + "')"
conn1.Execute(SQL)

I would greatly appreciate any help or suggestions. Thank you.

 

[Bastien]

you need to replace the single quotes with some other variable...

strSomething = replace(theString, "'", "&chr(39)&quot

replaces all single apostrophies with the &chr(39) which is the character for '

remember when you display the data to replace the &chr(39) with the ' so that all reads okay

hth

Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[cwolgamott]

Thank you so much for your reply. I greatly appreciate it.

 

[GabeC]

Good morning. Another way to do this is to replace the single quote with two single qoutes in you sql statement. SQL Server knows you really mean one single quote. When you display your text it will be the way you intended it to be thus you don't have to do a replace after extracting the date from the data base.

Ex.
theString = "cwolgamott's cool app."
strSomething = replace(theString, "'", "''&quot

strSomething will now have the value of "cwolgamott''s cool app."

SQL Server will know to only store "cwolgamott's cool app." into the database.

Thanks,

Gabe

 

[cwolgamott]

Thank you so much for your reply. I greatly appreciate it.