Steps to Reproduce the Behavior

1. Create a workbook for this test. To do this, follow these steps:
   1. Open a new workbook in Excel.
   2. In cell Sheet1!A1, type ID, and then in cell Sheet1!B1, type Name.
   3. In cell Sheet1!A2, type AAA, and then in cell Sheet1!B2, type John.
   4. In Excel 2000 and in Excel 2002, save the workbook as C:\Test.xls, and then quit Excel. In Excel 2007, save the workbook as C:\Test.xlsx, and then quit Excel.
2. In Visual Basic .NET, open a new Windows Application project. Form1 is created by default.
3. Add two Button controls to Form1.
4. Change the Text property of the first button to Add Record, and then change the Text property of the second button to Update Record.
5. On the View menu, click Code.
6. Add the following code to the beginning of the code module:

   Imports System.Data.OleDb
   					

7. In Excel 2000 and in Excel 2003, add the following code example to the Form1 class.

       Private Sub Button1_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles Button1.Click
           ModifyXLData(0) 'Add record.
       End Sub
   
       Private Sub Button2_Click(ByVal sender As Object, ByVal e As _
          System.EventArgs) Handles Button2.Click
           ModifyXLData(1) 'Update record.
       End Sub
   
       Private Sub ModifyXLData(ByVal nAction As Int16)
   
           Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=C:\Test.xls;" & _
                                 "Extended Properties=""Excel 8.0;HDR=YES"""
   
           ' Create an instance of a DataAdapter.
           Dim da As New OleDbDataAdapter("Select * From [Sheet1$]", sConn)
           Dim cb As New OleDbCommandBuilder(da)
   
           ' Create an instance of a DataSet from Sheet1 data.
           Dim ds As New DataSet("Data")
           da.FillSchema(ds, SchemaType.Source, "[Sheet1$]")
           da.Fill(ds, "[Sheet1$]")
   
           'Add, modify, or delete a row.
           Dim dr As DataRow
           Select Case nAction
               Case 0   'Add a new row
                   Dim rowVals(1) As Object
                   rowVals(0) = "hello"
                   rowVals(1) = "world"
                   ds.Tables(0).Rows.Add(rowVals)
               Case 1   'Modify an existing row
                   dr = ds.Tables(0).Rows(0)
                   dr.BeginEdit()
                   dr(0) = "hi"
                   dr.EndEdit()
           End Select
   
           'Attempt the update.
           Try
               da.Update(ds, "[Sheet1$]")
           Catch ex As OleDbException
               Dim er As OleDbError
               For Each er In ex.Errors
                   MsgBox(er.Message)
               Next
           Catch ex2 As System.InvalidOperationException
               MsgBox(ex2.Message)
           End Try
   
       End Sub    
   					

   In Excel 2007, add the following code example to the Form1 class.

       Private Sub Button1_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles Button1.Click
           ModifyXLData(0) 'Add record.
       End Sub
   
       Private Sub Button2_Click(ByVal sender As Object, ByVal e As _
          System.EventArgs) Handles Button2.Click
           ModifyXLData(1) 'Update record.
       End Sub
   
       Private Sub ModifyXLData(ByVal nAction As Int16)
   
           Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 '"Data Source=C:\Test.xls;" & _
                                 '"Extended Properties=""Excel 12.0;HDR=YES"""
   
           ' Create an instance of a DataAdapter.
           Dim da As New OleDbDataAdapter("Select * From [Sheet1$]", sConn)
           Dim cb As New OleDbCommandBuilder(da)
   
           ' Create an instance of a DataSet from Sheet1 data.
           Dim ds As New DataSet("Data")
           da.FillSchema(ds, SchemaType.Source, "[Sheet1$]")
           da.Fill(ds, "[Sheet1$]")
   
           'Add, modify, or delete a row.
           Dim dr As DataRow
           Select Case nAction
               Case 0   'Add a new row.
                   Dim rowVals(1) As Object
                   rowVals(0) = "hello"
                   rowVals(1) = "world"
                   ds.Tables(0).Rows.Add(rowVals)
               Case 1   'Modify an existing row
                   dr = ds.Tables(0).Rows(0)
                   dr.BeginEdit()
                   dr(0) = "hi"
                   dr.EndEdit()
           End Select
   
           'Attempt the update.
           Try
               da.Update(ds, "[Sheet1$]")
           Catch ex As OleDbException
               Dim er As OleDbError
               For Each er In ex.Errors
                   MsgBox(er.Message)
               Next
           Catch ex2 As System.InvalidOperationException
               MsgBox(ex2.Message)
           End Try
   
       End Sub    
   					

8. Press F5 to build and run the sample. Form1 is loaded.
9. On the form, click Add Record. Note that you receive an error.
10. On the form, click Update Record. Note that you receive an error.

Back to the top