Reputation: 11090
Following on from this question C# OLEDBConnection to Excel
I am having issues with using OLEdbCommandBuilder. I have this code:
DataTable dt = new DataTable();
OleDbCommand command = new OleDbCommand();
command = new OleDbCommand("Select * from [working sheet$]", oleDBConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = command;
OleDbCommandBuilder c = new OleDbCommandBuilder(dataAdapter);
dataAdapter.Fill(dt);
//arbitrary changed datatable
dt.Rows[2][3] = "dfd";
c.GetUpdateCommand();
dataAdapter.Update(dt);
oleDBConnection.Close();
However when I run it, I get this error:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
How can I update the Excel sheet with the updated row/cell information? I've never had to deal with Excel in such a way before.
I've googled the error but cannot find anything that meets my exact requirements.
Thanks,
Darren.
Upvotes: 1
Views: 3456
Reputation: 14775
You can only update if the adapter knows the unique primary key of the excel-database-table. You can add an id-column as first column in excel and make it the primary key and continue like this
...
dataAdapter.Fill(dt);
...
System.Data.DataColumn pkCol = dt.Columns[0]; // something like dt.Columns["Id"]
pkCol.Unique = true;
dt.PrimaryKey = new DataColumn[] {pkCol };
...
OleDbCommandBuilder c = new OleDbCommandBuilder(dataAdapter);
...
Upvotes: 1