Getting Exception "Incorrect syntax near 'System'" while using SqlCommandBuilder

Question

I am learning SqlCommandbuilder, but when I try to implement it, I am getting an exception. This is my code.

Code snippet #1: working fine

protected void btnGetStudent_Click(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

    SqlConnection con = new SqlConnection(cs);
    SqlDataAdapter da = new SqlDataAdapter("Select * from tblStudents where ID = @Id", con);
    da.SelectCommand.Parameters.AddWithValue("@Id", txtStudentID.Text);

    DataSet ds = new DataSet();
    da.Fill(ds, "Students"); // now this FILL is very useful as it manages opening of the connection and then executing the command to get the data and the loading it into the dataset and then closes the connection.

    ViewState["SQL_Query"] = da.SelectCommand.ToString();
    ViewState["Data"] = ds;

    if (ds.Tables["Students"].Rows.Count > 0)
    {
        DataRow dr = ds.Tables["Students"].Rows[0];
        txtStudentID.Text = dr["Id"].ToString();
    }    
}

Code snippet #2: causes an exception:

protected void btnUpdate_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

    SqlConnection con = new SqlConnection(connectionString);
    SqlDataAdapter dataAdapter = new SqlDataAdapter();

    dataAdapter.SelectCommand =
        new SqlCommand((string)ViewState["SQL_Query"], con);
    SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);

    DataSet ds = (DataSet)ViewState["Data"];
    DataRow dr = ds.Tables["Students"].Rows[0];
    dr["Id"] = txtStudentID.Text;

    int rowsUpdated = dataAdapter.Update(ds, "Students"); // Exception
}

Exception:

Incorrect syntax near 'System'

Answer 1

I found out why: The videos you are following use .NET 2.0. How do I know?

Look at the documentation for the SqlCommandBuilder class:

• Example for .NET 2.0:

  DataSet dataSet = new DataSet();
  adapter.Fill(dataSet, tableName);
  
  //code to modify data in DataSet here
  
  //Without the SqlCommandBuilder this line would fail
  adapter.Update(dataSet, tableName);
  

• Example for .NET 3.0+:

  DataSet dataSet = new DataSet();
  adapter.Fill(dataSet, tableName);
  
  //code to modify data in DataSet here
  
  builder.GetUpdateCommand();
  
  //Without the SqlCommandBuilder this line would fail
  adapter.Update(dataSet, tableName);
  

The obvious difference between them is the call to builder.GetUpdateCommand(); before calling adapter.Update, so you are missing that.

That said: I'd suggest you to switch to some tutorial that at least uses .NET 4.5