使用SqlCommandBuilder时，在'System'附近获得异常“错误语法”

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

我正在学习SqlCommandbuilder，但是当我尝试实现它时，会得到一个异常。这是我的代码。

Code snippet #1: working fine

代码片段#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();
    }    
}

Code snippet #2: causes an exception:

代码片段#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'

“系统”附近的不正确的语法

1 个解决方案

#1

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

我发现了原因:你正在观看的视频使用。net 2.0。我怎么知道?

Look at the documentation for the SqlCommandBuilder class:

查看SqlCommandBuilder类的文档:

Example for .NET 2.0:

例子为。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+:

为。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.

它们之间的明显区别是调用build . getupdatecommand ();之前调用适配器。更新，所以你错过了。

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

这就是说:我建议您切换到至少使用。net 4.5的教程。

#1