c# - 使用 SqlCommandBuilder 时出现异常 "Incorrect syntax near ' System'"

Question

我正在学习 SqlCommandbuilder，但是当我尝试实现它时，出现异常。这是我的代码。

代码片段#1:工作正常

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();
    }    
}

代码片段 #2:导致异常:

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

我发现了原因:您关注的视频使用 .NET 2.0。我怎么知道？

查看 SqlCommandBuilder 的文档类:

• .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);
  

• .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);
  

它们之间的明显区别是在调用 adapter.Update 之前调用 builder.GetUpdateCommand();，所以您忽略了这一点。

就是说:我建议您切换到一些至少使用.NET 4.5 的教程