CODEWITHSUNDEEP
c#sqlstring
dusm
dusm

Reputation: 1257

insert into sql db a string that contain special character '

i want to insert to a sql table a string that might contain ' character.

what is my best way to do so ? should i insert a \ before the ' ? here's my command in a c# code: 

SqlCommand myCommand = new SqlCommand(
    String.Format(
    "insert into ACTIVE.dbo.Workspaces_WsToRefile values({0},'{1}',getdate())", 
        folderId, 
        NewWorkspaceName), 
     myConnection);

where NewWorkspaceName might contain ' character, so the insert will cause an exception at the moment.

thanks in advanced, hadas.

Upvotes: 0

Views: 10482

Answers (4)

Sheba
Sheba

Reputation: 736

You can try this:

string stringToDatabase=Server.HtmlEncode("կҤїАͻBsdҤїА");

This saves 'stringToDatabase' in your database . Then while retreiving

string OriginalText=Server.HtmlDecode(stringFromDatabase);

Upvotes: 0

Steve
Steve

Reputation: 216273

You have only one option, forget everything else. Use Parametrized queries like this

SqlCommand myCommand = new SqlCommand("insert into ACTIVE.dbo.Workspaces_WsToRefile" + 
                                      " values(@id, @space, getDate()", myConnection);  
myCommand.Parameters.AddWithValue("@id", folderId);
myCommand.Parameters.AddWithValue("@space", NewWorkspaceName);
myCommand.ExecuteNonQuery();

folderID and NewWorkspaceName, are passed to the Sql Engine inside parameters.
This will take care of special characters like quotes.
But you gain another benefit using parametrized queries. You avoid Sql Injection Attacks

Upvotes: 3

Stephen Gilboy
Stephen Gilboy

Reputation: 5825

You should be using SqlParameter. http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

    string query = "insert into ACTIVE.dbo.Workspaces_WsToRefile values(@folderID, @newWorkSpace, @createDate)";

using(SqlCommand cmd = new SqlCommand(query, SqlConnection))
{

    SqlParameter param = new SqlParameter("@folderID", folderId);
    param.SqlDbType = SqlDbType.Int;
    cmd.Parameters.Add(param);
    .....
}

Upvotes: 7

God Dices
God Dices

Reputation: 51

NewWorkspaceName= NewWorkspaceName.Replace("\'","\'\'");

'' is a ' in sql

Upvotes: 1

Related Questions