Reputation: 21
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:
The Value is "\\Tesafilm" the SQLValue is "\Tesafilm"
Upvotes: 2
Views: 514
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
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