SQL server remove space before value when I insert data

Question

When I insert data using a SqlCommand, It also add space before values which I just inserted. How do I avoid adding spaces?

Here is the insert query code:

SqlCommand cmd1 = new SqlCommand("INSERT INTO [Contracts].[dbo].[Contract] 
    ([Contract_Id],[Name],[Description],[Contracted_by],[Vendor_Name],[Related_Dept],[Start_date],[Expiration_Date],[TypeofContract],[Contact_Person],[Contact_No],FileName,FileData,FileType) 
    VALUES (' " + TextBox1.Text + "',' " + TextBox2.Text + "',' " + TextBox3.Text + "',' " + TextBox4.Text + "',' " + TextBox5.Text + "',' " + DepartmentTextBox.SelectedValue.ToString() + "',' " + TextBox7.Text + "',' " + TextBox8.Text + "',' " + TextBox9.Text + "',' " + TextBox10.Text + "',' " + TextBox11.Text + "',@Name,@Data,@Type)", con);

Change `' "` to `'"` and next time show some research effort. — CodeCaster, Jun 10 '13 at 13:39
Consider using SqlParameters, your code is vulnerable to SQL Injection attacks http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection — Meff, Jun 10 '13 at 13:43

Steve · Accepted Answer · 2013-06-10T13:49:24.387

Of course any kind of problems surface when you use string concatenation to build command text. In your case you have inadvertently added a space before your control values.
If you had used a parameterized query this problem would not have arisen

  SqlCommand cmd1 = new SqlCommand("INSERT INTO [Contracts].[dbo].[Contract] " + 
     "([Contract_Id],[Name],[Description],[Contracted_by],[Vendor_Name],[Related_Dept]," + 
     "[Start_date],[Expiration_Date],[TypeofContract],[Contact_Person]," + 
     "[Contact_No],FileName,FileData,FileType) VALUES (" + 
     "@cid, @name, @desc, @cby, @vname, @rdept, @stdate, @expdate, " + 
     "@tc, @cp, @cno, @file, @fdate, @ftype",con)

  SqlParameter p = new SqlParameter("@cid", SqlDbType.Int).Value = Convert.ToInt32(textBox1.Text));
  cmd.Parameters.Add(p);
  .... and so on for the other parameters required

By the way, remember that if you have an IDENTITY column you should not try to insert anything in that column (Contract_ID is particulary suspect here)

score 0 · Answer 2 · answered Jun 10 '13 at 13:40

It's inserting spaces because you have extra spaces in your query string. I changed "',' " to "','":

SqlCommand cmd1 = new SqlCommand("INSERT INTO [Contracts].[dbo].[Contract] ([Contract_Id],
 [Name],[Description],[Contracted_by],[Vendor_Name],[Related_Dept],[Start_date],
 [Expiration_Date],[TypeofContract],[Contact_Person], Contact_No],FileName,FileData,FileType) 
 VALUES ('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + 
 TextBox4.Text + "','" + TextBox5.Text + "','" + DepartmentTextBox.SelectedValue.ToString() 
 + "','" + TextBox7.Text + "','" + TextBox8.Text + "','" + TextBox9.Text + "','" + 
 TextBox10.Text + "','" + TextBox11.Text + "',@Name,@Data,@Type)", con);

SQL server remove space before value when I insert data

2 Answers2