Cannot update database with datatable

Question

I am trying to update the database with a datatable loaded into a datagridview. Whenever I make some changes in the datagridview and click the "Save" buttton, I got the error message "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." Searching the net for this error, I see that the error is caused by the missing of primary key in the selected table. To be sure, the table I selected from the database DOES have a primary key column. I notice that this error only occurs when I make some changes in the datagridview. If I just load the datagridview and click "Save" without changing anything, there's no error. Here's my code:

//These object instances are created at class level
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet()

// In the form_load method:

string strSql = "SELECT * FROM Inventory";
try
{
SqlCommand sqlCmd = new SqlCommand(strSql, Conn);
da.SelectCommand = sqlCmd;
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}

da.Fill(ds, "Inventory");
BindingSource bSource = new BindingSource();
bSource.DataSource = ds;
bSource.DataMember = "Inventory";
datagridview1.DataSource = bSource;

//This is where I get trouble, when I click the "Save" button on the form to save changes in datagridview back to database,
// I got the error "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
//To be sure, the "Inventory" table DOES have a primary key column.
//If I do not make any changes in the datagridview, I do not get error message when clicking save button
private void btnSave_Click(object sender, EventArgs e)
{
SqlCommandBuilder sqlCmdBldr = new SqlCommandBuilder(da);
da.Update(ds, "Inventory");
}

The dataset actually contains other tables, since they are irrelevant I do not mention them here.

And here's the Sql command that I used to create the said Sql Server table:

    CREATE TABLE Inventory
    (
    Custom_SKU nvarchar(100) not null primary key,
    Custom_Title nvarchar(255) null,
    Custom_QtyOnHand int null,
    BT_QtyOnHand int null,
    BT_QtyScheduled int null,
    BT_QtyRunning int null,
    BT_QtySoldWaiting int null,
    BT_QtyClearedToShip int null,
    Custom_QtyAvailable int null,
    Custom_TrackInventory bit not null,
    Custom_Notes nvarchar(255) null
    )

Henry Vuong · Answer

I figured it out. Somewhere else in the code I used the same instance of the SqlDataAdapter to select another set of data from the database (not shown in the code above). So the SqlDataAdapter actually selected two sets of data one of them does not include a primary key that's the cause of the problem. I simply rename one of the SqlDataAdapter instance making them two different instances. I can now use the code above without problem. Thanks for reading my post.

Hemant Kumar · Answer

For Updating the Row using the SqlDataAdapter the table should have a Primary Key Column. You need to do like this also da.FillSchema(TableNameds);