How to INSERT a string that contains single-quotes?

My code results in SQL statements like the following one - and it gives an error because of the extra single-quotes in 'it's great':

UPDATE Comments SET Comment='it's great' WHERE UserID='joe' AND GameID='503'

Here's the error I get when I try this code in SQL Server:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

I need to know how I can insert a string such as 'it's great' - how do I deal with the extra quotes issue? is there a way to ecape it like this 'it/'s great' ? This doesn't seem to work.

Here's the code that generates the SQL. I'm using a FCKeditor box instead of a TextBox, but I got the same error when I was using the TextBox:

string strUpdate = "UPDATE Comments SET Comment='";
strUpdate = strUpdate + FCKeditor1.Value;
//strUpdate = strUpdate + ThisUserCommentTextBox.Text;
strUpdate = strUpdate + "' WHERE UserID='";
strUpdate = strUpdate + (string)Session["UserID"];
strUpdate = strUpdate + "'";
strUpdate = strUpdate + " AND GameID='";
strUpdate = strUpdate + Request.QueryString["GameID"];
strUpdate = strUpdate + "'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

I'm using SQL Server 2005 and ASP.NET 2.0

Much thanks

ok i wont go through your code...but i can tell you the key point

try run

SELECT 'AAAA' SQL retuen AAAA

SELECT '''AAAA''' SQL return 'AAAA' (you need 3 * ' + AAAA + 3 * ' )

hope this give u idea

|||

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

|||

limno:

You should use parameterized query, this problem will go away.

You can find out why by searching this forum. If you still have question, please post back.

This is the best advice you will get today. If you value your applications you will never ever build an Sql statement that way again

string strUpdate = "UPDATE Comments SET Comment= @.Comment WHERE UserID = @.UserID AND GameID = @.GameID'";

SqlConnection myConnection = new SqlConnection(...);
SqlCommand myCommand = new SqlCommand(strUpdate, myConnection);

myCommand.Parameters.AddWithValue( "@.Comment", FCKeditor1.Value );
myCommand.Parameters.AddWithValue( "@.UserID", Session["UserID"] );
myCommand.Parameters.AddWithValue( "@.GameID", Request.QueryString["GameID"]);

try
{
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
}
catch (SqlException ex)
{
ErrorLabel.Text = "Error: " + ex.Message;

}
finally
{
myCommand.Connection.Close();
}

|||

Thanks, it works now.