oledbdataadapter command builder getupdate, needs key for argument

Question

I seem to be having an issue. I have a dataset (excel) sourced to a datagridview in a windows forms application. I'm hoping to find a way to refresh/update my dataset within the datagridview after making a change to a cell.

Let me go through the steps I've taken to try and accomplish this. I've added a "refresh" button to the form and I've created a method called write2DGV which will write changes to the dataset after changes are made in the datagridview.

I've added a timer that will allow Excel to update to update the outputs. I've also added code to wipe clean my dataset (which is the datasource for the datagridview). Last I have a retrieve method that will source the dataset to the datagridview via the oledataadapter.

Unfortunately when I run the code I get an error message about not providing a key as an argument to the get update method. I'm not exactly sure what they mean by "key", also the only valid data types for the argument to the get update method is boolean. My code is below:

'Declarations
Dim myDataSet As DataSet
Dim MyCommand As OleDb.OleDbDataAdapter
Dim objWorkSheet As Excel.Worksheet = objExcel.ActiveSheet
Dim sizetimer As New System.Timers.Timer

Sub retrieveMyDataSet()
    MyCommand = New OleDbDataAdapter(select * from [MyExcelWorksheet$A13:x150], MyConnection)
     myDataSet = New System.Data.DataSet()
    MyCommand.Fill(myDataSet)
    myDataGridView.DataSource = myDataSet.Tables(0).DefaultView
End Sub

Sub write2Size()
    'A-k gets written.  Entire graph goes to a-x so I only need to writ the columncount -14
   'x is letter 24. k is letter 11.  24-11 = 13.  So, offset needs to be -13
   '39;Rows 13 through first blank
        Dim rowindex As Integer
        Dim columnindex As Integer
        For rowindex = 1 To myDataGridView.RowCount
        For columnindex = 1 To myDataGridView.ColumnCount - 13
            objWorkSheet.Cells(rowindex + 13, columnindex + 0) = myDataGridView(columnindex     - 1, rowindex - 1).Value
        Next
    Next
End Sub

Sub refreshDGV()
    write2DGV()
    myDataSet.Clear()
    Dim x As New OleDbDataAdapter("select * from [MyExcelWorksheet$A13:x150]", MyConnection)
    Dim oledbCommands As New OleDb.OleDbCommandBuilder(x)
    x.UpdateCommand = oledbCommands.GetUpdateCommand() 'oops type! removed = x.UpdateCommand
    x.Update(myDataSet)
    sizetimer.AutoReset = True
    sizetimer.Interval = 2000 '2 seconds
    retrieveMyDataSet()
End Sub

Private Sub refreshbtn_Click(sender As System.Object, e As System.EventArgs) Handles refreshbtnSize.Click
   refreshDGV()
End Sub

Answer 1

Unfortunately when I run the code I get an error message about not providing a key as an argument to the get update method.

The reason of this error is explained in this article that you should read carrefully: How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

You are using one of the solution mentionned in this article to update the worksheet, ie Make changes to a DataSet that you have filled with a table/query from an Excel workbook and then call the Update method of the DataAdapter to resolve changes from the DataSet back to the workbook.

But just after we can read:

However, to use the Update method for change resolution you must set parameterized commands for the DataAdapter's InsertCommand

INSERT INTO [Sheet1$] (F1, F2) values (?, ?) 

and UpdateCommand:

UPDATE [Sheet1$]SET F2 = ? WHERE F1 = ? 

Parameterized INSERT and UPDATE commands are required because the OleDbDataAdapter does not supply key/index information for Excel workbooks; without key/index fields, the CommandBuilder cannot automatically generate the commands for you.

The message is clear: you have to explicitly write parameterized INSERT and UPDATE commands if you want achieve what you are trying to do.

And don't forget that Excel has a lot a functionnality but is not especially designed to work as a database. So to avoid a lot of problems, I recommand you to switch to another local database (SqlServerCompact, SQlLite, ...).