' and " in SQL Statements

Hi,

How to you submit data to the SQL server when it has a ' or ' inline with the text?

Say I have a textbox with 5'9' in it, Visual Studio comes back with an error - that I have ended the SQL statement before it is inserted or updated the database.

How do you compensate for this?

Thanks

 

Dim sql As String = "SELECT * FROM stuff WHERE height = '5''11'"

escape a single quote ' with two single quotes ''

If you're pulling from user input, you can also do...

Dim height As String = "5'11"
Dim sql As String = String.Format("SELECT * FROM stuff WHERE height = '{0}'", height.Replace("'", "''"))



Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Thanks for the help.

Works great.

 

Thanks a lot for that.

I didn't realize the importance of parameters.

 

FYI,

The recommended way to do this is to use SQL parameters, instead of dynamically building the SQL query. You are still leaving yourself open to SQL injection attacks if you ever splice any user input directly into a SQL statement.

Check out this page for a better description of SqlParameter's and the danger of dynamically built SQL strings!

www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx