You receive error messages when you try to use ADO.NET OLEDbDataAdapter to modify an Excel workbook

Article translations Article translations
Article ID: 316756 - View products that this article applies to.
This article was previously published under Q316756
Expand all | Collapse all

On This Page

SYMPTOMS

When you try to add, to update, or to delete records in an Excel workbook that uses the OLEDbDataAdapter with ADO.NET, you receive one of the following error messages:

When you add a record:
Syntax error in INSERT INTO statement.
When you update a record:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
When you delete a record:
Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.

CAUSE

The Microsoft Jet OLE DB provider does not return key or index information for Excel workbooks. Therefore, the OLEDbCommandBuilder cannot automatically generate SQL statements to perform the intended action.

RESOLUTION

To resolve this problem when you insert new records by using the OLEDbDataAdapter, you must provide primary key information through the InsertCommand member of the adapter. To modify existing records, you must provide primary key information through the UpdateCommand member.

The Jet OLE DB provider does not support delete operations for Excel workbooks. Therefore, you cannot delete records in a workbook by using ADO or ADO.NET.

STATUS

This behavior is by design.

MORE INFORMATION

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.

REFERENCES

For additional information, see the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/33y2221y(vs.71).aspx
For additional informationabout using InsertCommand or UpdateCommand to update Excel workbooks, click the following article number to view the article in the Microsoft Knowledge Base:
316934 HOW TO: Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

Properties

Article ID: 316756 - Last Review: January 9, 2007 - Revision: 7.4
APPLIES TO
  • Microsoft ADO.NET 2.0
  • Microsoft ADO.NET 1.0
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbtshoot kberrmsg kbprb KB316756

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com