CODEWITHSUNDEEP
c#exceloledb
Darren Young
Darren Young

Reputation: 11100

C# OleDBConnection Excel

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: 3458

Answers (1)

k3b
k3b

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

Related Questions