SQL command problem

Question

Hey all I'm having trouble with SQL commands that contain single quote characters. It seems that single quote characters in a SQL command confuse the darn thing. Example: --------------------------------------- // the text I want to store in the SQL database string text = "Here is a single line quote ' there it was"; string sqlCommand = string.Format("Update myDataTable set Text = '{0}', where (ID = 3456781)", text); --------------------------------------- When I .ExecuteNonQuery(), it will throw an exception telling my I've got bad sql command syntax due to the single quote character in text. It works fine if I take the single quote character out. Now I'm very new to SQL, so I'm probably missing something simple. How do I get around this simple syntax issue? Mr. Chornberger, I'm looking your way. :)

chrishornberger · Answer

That's cool. I had no idea you could slash-escape them. Thanks. Learn something new every day! :)

Mahesh Chand · Answer

You can use that too ;). Or I belive you can also use CommandBuilder.Append method.

chrishornberger · Answer

You can slash-escape them? hmm. I just generally double them... like update some_table set last_name = 'O''Neal' When I don't have stored procs available.q

Mahesh Chand · Answer

You can also use something like this: ' \' ' to insert a single quote in SQL text.

ssswamii · Answer

Nevermind my question was answered in another forum by chornberger. The man is everywhere! :) In case anyone is interested using stored procedures will get around the problem; they don't require single line quotes at all so the whole thing is prevented.