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'

Negative voter please put a comment too so that I can fix my question. You have full right to give negative vote but do take some time out to explain negative vote. — Unbreakable
– Unbreakable, Commented Mar 18, 2018 at 2:25
Since I am a beginner I don't know what other details are required, If you ask me I will put my entire project solution here. — Unbreakable
– Unbreakable, Commented Mar 18, 2018 at 2:26
Wondering: what's the point of instantiating that builder? I don't see you using it — Camilo Terevinto
– Camilo Terevinto, Commented Mar 18, 2018 at 2:26
In the working version you pass "Select * from tblStudents where ID = @Id" to the DataAdapter, in the broken case you pass (string)ViewState["SQL_Query"]. What's the value of (string)ViewState["SQL_Query"]? — Camilo Terevinto
– Camilo Terevinto, Commented Mar 18, 2018 at 2:28

Camilo Terevinto · Accepted Answer · 2018-03-18 03:29:30Z

4

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

answered Mar 18, 2018 at 3:29

Camilo Terevinto

32.2k7 gold badges95 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related