• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

Single Quote as SQL String Delimiter

I'm using VB6 against an sql server database.
I use a single quote to string together my sql statements and,as you can imagine, if a user enters a string with a single quote in it, the statement bombs in sql server.

For example,
Here's the completed string:

update Programs set agencycode = 'INSUR', programcode = 'XTS', description = 'Stock Workmen's Compensation Security Fund', sequence = '5', lastactionuser = 'Trapatsos      ', lastactiondate = getdate(), recordstatus = 'Active ' where id = 207

and the string chokes with:
"Incorrect syntax near 's'"

Does anyone have the best advice for stringing together sql statements with values that may contain single quotes?

Thanks in Advance

1 Solution
Anthony PerkinsCommented:
Replace single quotes with two quotes.

Dim sValue As string
sValue = "Stock Workmen's Compensation Security Fund"
sValue = Replace(sValue, "'", "''")

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now