CODEWITHSUNDEEP
c#sql
Rene Ries
Rene Ries

Reputation: 21

lose \ when using ExecuteNonQuery

I'm trying to insert a single value into an SQL Database. It works fine as long as I don't insert a "\". If I do, then I lose a "\" in the database.

For example, in the debugger I see this Command Text:

Insert into tblProjekte (Projektbezeichnung) values ('\\bla\\bla\\bla')

But in the SQL Server Profiler I always find this Insert Statement:

Insert into tblProjekte (Projektbezeichnung) values ('\bla\bla\bla')

My source code:

public void InsertProjekt(string Projektbezeichnung)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Server=localhost; Database=myProjekt; UID=user; PWD=pwd";
    con.Open();

    SqlCommand com = new SqlCommand();
    com.Connection = con;
    com.CommandText = String.Format("Insert into tblProjekte (Projektbezeichnung) values ('{0}')",@Projektbezeichnung);
    int rows = com.ExecuteNonQuery();        
}

After I changed my Source Code to:

SqlCommand com = new SqlCommand("INSERT INTO tblProjekte (Projektbezeichnung) VALUES (@Projektbezeichnung)");
            com.Parameters.AddWithValue("@Projektbezeichnung", Projekt.Projektbezeichnung);

I get this information during debugging: enter image description here

The Value is "\\Tesafilm" the SQLValue is "\Tesafilm"

Upvotes: 2

Views: 514

Answers (2)

newfurniturey
newfurniturey

Reputation: 38456

As several comments have pointed out, the \ character is an "escape character" in SQL. When you're inserting it without using a properly escaped string, SQL will strip them out as it interprets them as just being escape-characters.

You're using string.Format() to simulate a parameterized query, and that won't really cut it. If you use SqlCommand.Parameters.AddWithValue(), however, it should fix your issue:

SqlCommand com = new SqlCommand("INSERT INTO tblProjekte (Projektbezeichnung) VALUES (@Projektbezeichnung)");
com.Parameters.AddWithValue("@Projektbezeichnung", Projektbezeichnung);
com.Connection = con;
int rows = com.ExecuteNonQuery();

For more information regarding SqlCommand.Parameters collection, check it out on the MSDN here. It offers a handful of 'add' methods that may be more appropriate for different scenarios - though regular .AddWithValue() should work fine in this case.

update: changed my original .Add() to .AddWithValue() as the MSDN states SqlParameterCollection.Add() has been deprecated in favor of .AddWithValue().

Upvotes: 3

Bas
Bas

Reputation: 27115

Use a parametrized query instead:

public void InsertProjekt(string Projektbezeichnung)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Server=localhost; Database=myProjekt; UID=user; PWD=pwd";
    con.Open();

    SqlCommand com = new SqlCommand();
    com.Connection = con;
    com.CommandText = "Insert into tblProjekte (Projektbezeichnung) values (@value)"
    com.Parameters.AddWithValue("@value", Projektbezeichnung);

    int rows = com.ExecuteNonQuery();

}

Upvotes: 4

Related Questions