How do I eliminate ";" """ and "'" from SQL s

[michellecole]

I have a form with a text box entry field which allows the user to enter paragraph(s) of data which is used to build a SQL string to ultimately be saved to a memo field in the table. Before the record is saved, it is spell checked. But when executing the SQL statement with the data from the User input, if the user entered characters such as ";" or "'" or """, the query fails. The user needs the ability to enter grammatically correct sentences such as: "John's project ..." or "regarding the "Adjustments Report", ...".

Here is the code utilizing the user entered data in the field called [txtSummaryEntry]):

strSummary = [txtSummaryEntry].Value

sql = "INSERT INTO tblMonthlySummary_Projects ([DM_Summary) Values('" & strSummary & "');"

Since a "'" was entered by the user in the txtSummaryEntry field, I receive a "Syntax error (missing operator) in query expression..." run-time error messagebox.

Is there a work around to allow these characters to be included in the SQL string? If not, what are my options.

Thank you,

 

[TonyJollans]

Hi michellecole,

If you get the quotes right, the semicolons will not cause a problem, so all you need to do is double up all the single quotes in the User input. Try this (which changes each single single-quote with two single-quotes) ..

[blue][tt]sql = "INSERT INTO tblMonthlySummary_Projects ([DM_Summary) Values('" & [highlight]Replace([/highlight]strSummary[highlight],"[red]'[/red]","[red]''[/red]")[/highlight] & "');"[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[michellecole]

Awesome, it worked beautifully on the single quotes!! Thank you!

Is there a way to do the same for double quotes too (I couldn't get it to work on double quotes i.e. ... working on the "Adjustment Reports" ....? Also, what if the user enters single and double quotes in the same string? Can you do (2) Replace statements?

I appreciate your feedback,

Michelle

 

[TonyJollans]

Hi Michelle,

You can nest replace functions, but you don't want to in this case. The only character that is treated specially when doubled-up is the one used to delimit the string - in this case, a single-quote. So just replacing each single-quote with two single-quotes should be enough.

If you still have a problem, could you post some more details of what you do with the various strings and examples of things which fail.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.