Unclosed quotation mark after the character string '

Question

I'm working on Datagrid tool in web form. I have added Edit button, but whenever I update data I getting the error:

An exception of type 'System.Data.SqlClient.SqlException' occurred in

System.Data.dll but was not handled in user code

Additional information: Unclosed quotation mark after the character string ',Computer=System.Web.UI.WebControls.TextBox where rollno=1'.

and below is the code that I have written in UpdateCommand Event of DataGrid

TextBox txtName = (TextBox)e.Item.Cells[1].Controls[0];
TextBox txtEnglish = (TextBox)e.Item.Cells[2].Controls[0];
TextBox txtComputer = (TextBox)e.Item.Cells[3].Controls[0];
string strSQL = "update student set Name='" + txtName.Text + "',English=" + txtEnglish + "',Computer=" + txtComputer + " where rollno=" + DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
SqlCommand mycmd = new SqlCommand(strSQL, mycon);
mycon.Open();
mycmd.ExecuteNonQuery();
mycon.Close();
DataGrid1.EditItemIndex = -1;
FullupGrid();

You should use parameratized queries. That prevents SQL injection and these kinds of errors. — VDWWD, Jul 14 '17 at 10:12
Of course! you did `English=" + txtEnglish + "',`but it should be `English='" + txtEnglish + "',` — Suraj S, Jul 14 '17 at 10:17

Dmitry Bychenko · Answer 1 · 2017-07-14T16:07:08.890

Use formatting to avoid syntax errors:

TextBox txtName = (TextBox)e.Item.Cells[1].Controls[0];
TextBox txtEnglish = (TextBox)e.Item.Cells[2].Controls[0];
TextBox txtComputer = (TextBox)e.Item.Cells[3].Controls[0];

string strSQL = 
  //DONE: Make SQL readable with a help of string interpolation and verbatim strings  
  $@"update Student 
        set Name     = '{txtName.Text}',
            English  = '{txtEnglish}',
            Computer = '{txtComputer}' 
      where RollNo   = {DataGrid1.DataKeys[e.Item.ItemIndex].ToString()}";

using (SqlConnection con = new SqlConnection("ConnectionStringHere")) {
  con.Open();

  using (SqlCommand mycmd = new SqlCommand(strSQL, con)) {
     mycmd.ExecuteNonQuery();
  }
}

DataGrid1.EditItemIndex = -1;
FullupGrid();

A much better approach, however, is to parametrize the query:

TextBox txtName = (TextBox)e.Item.Cells[1].Controls[0];
TextBox txtEnglish = (TextBox)e.Item.Cells[2].Controls[0];
TextBox txtComputer = (TextBox)e.Item.Cells[3].Controls[0];

string strSQL = 
  $@"update Student 
        set Name     = :prm_Name,
            English  = :prm_English,
            Computer = :prm_Computer 
      where RollNo   = :prm_RollNo";

using (SqlConnection con = new SqlConnection("ConnectionStringHere")) {
  con.Open();

  using (SqlCommand mycmd = new SqlCommand(strSQL, con)) {
    //TODO: a better choice is to create parameter with specified RDMBS type
    mycmd.Parameters.AddWithValue(":prm_Name", txtName.Text);         
    mycmd.Parameters.AddWithValue(":prm_English", txtEnglish);         
    mycmd.Parameters.AddWithValue(":prm_Computer", txtComputer);         
    mycmd.Parameters.AddWithValue(":prm_RollNo", 
      DataGrid1.DataKeys[e.Item.ItemIndex].ToString());         

    mycmd.ExecuteNonQuery();
  }
}

DataGrid1.EditItemIndex = -1;
FullupGrid();

Unclosed quotation mark after the character string '

1 Answers1