Henry Vuong

Henry Vuong

  • NA
  • 27
  • 0

Cannot update database with datatable

Aug 5 2012 12:47 PM
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
    )

Answers (2)