none
Trouble with odbcDataAdapter RRS feed

  • Question

  • I'm attempting to use an odbcDataAdapter to write an ADO.NET DataTable to an Excel sheet.  In the various things I've tried it doesn't write any rows so I must be missing something important.  Can someone with more experience please point out what I'm doing wrong or point me to the documentation that will explain it for me.

    Setup:
    I have a VB2008 Express Windows Forms app that uses a DataSet based on an SQLServer2008 Compact Edition 3.5 database.  I'm adding functionality to the app to import and export data tables from/to Excel.  I've started with export and so far am running into trouble.  For export it is expected that the operation will be performed on an empty .xls file.  The code below is what I have to date.  The section to delete the existing file is commented out so I could try an .xls file with column headers that match the column names for the table I'm trying to export.  I get 0 rows exported either way.  I've also tried specifying only a Select command and using an odbcCommandBuilder but it never built any commands.  I initially tried a simpler select command of SELECT * FROM [Sheet1$]. 

    Imports System.Data.Odbc, System.Data.SqlServerCe
    
    Public Class frmImprtExprt
    
    
    
     'http://support.microsoft.com/kb/306022/en-us
    
     'http://support.microsoft.com/kb/278973/
    
     'http://support.microsoft.com/kb/257819/EN-US/
    
     'http://support.microsoft.com/kb/247412
    
     'http://msdn.microsoft.com/en-us/library/aa168292(office.11).aspx
    
     'http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.interaction.createobject.aspx
    
     
    
     Dim MyDataSet As MyDataSet
    
     Dim Table1TableAdapter As MyDataSetTableAdapters.Table1TableAdapter
    
    
    
    
    
     Private Sub frmImprtExprt_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
    
    
      'Grab a reference to the main form DataSet
    
      MyDataSet = frmMain.MyDataSet
    
      Table1TableAdapter = frmMain.Table1TableAdapter
    
    
    
     End Sub
    
    
    
     Private Sub ImportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportButton.Click
    
      Dim openResult As DialogResult = OpenFileDialog1.ShowDialog
    
      Select Case openResult
    
       Case Windows.Forms.DialogResult.OK
    
        ImportFileNameLabel.Text = OpenFileDialog1.SafeFileName
    
       Case Else
    
        ImportFileNameLabel.Text = openResult.ToString
    
      End Select
    
     End Sub
    
    
    
     Private Sub ExportButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportButton.Click
    
      Dim saveResult As DialogResult = SaveFileDialog1.ShowDialog
    
      Select Case saveResult
    
       Case Windows.Forms.DialogResult.OK
    
        ExportFileNameLabel.Text = SaveFileDialog1.FileName
    
        'If My.Computer.FileSystem.FileExists(ExportFileNameLabel.Text) Then
    
        ' My.Computer.FileSystem.DeleteFile(ExportFileNameLabel.Text)
    
        'End If
    
        Dim strConn As String = "Driver={Microsoft Excel Driver (*.xls)};" & _
    
         "DBQ=" & ExportFileNameLabel.Text & ";;"
    
        Try
    
         Using odbConn As New OdbcConnection(strConn)
    
          'Dim strSelectCmd As String = "SELECT * FROM [Sheet1$]"
    
          Dim strSelectCmd As String = Table1TableAdapter.Adapter.SelectCommand.CommandText
    
          strSelectCmd = strSelectCmd.Remove(strSelectCmd.IndexOf("FROM [")) & "FROM [Sheet1$]"
    
          Dim strInsertCmd As String = Table1TableAdapter.Adapter.InsertCommand.CommandText
    
          strInsertCmd = strInsertCmd.Replace("[Table1]", "[Sheet1$]")
    
          Dim strUpdateCmd As String = Table1TableAdapter.Adapter.UpdateCommand.CommandText
    
          strUpdateCmd = strUpdateCmd.Replace("[Table1]", "[Sheet1$]")
    
          Dim strDeleteCmd As String = Table1TableAdapter.Adapter.DeleteCommand.CommandText
    
          strDeleteCmd = strDeleteCmd.Replace("[Table1]", "[Sheet1$]")
    
          Dim odbAdptr As New OdbcDataAdapter(strSelectCmd, odbConn)
    
          'Dim odbBldr As New OdbcCommandBuilder(odbAdptr)
    
          Dim cmdInsert As New OdbcCommand(strInsertCmd, odbConn)
    
          odbAdptr.InsertCommand = cmdInsert
    
          Dim cmdUpdate As New OdbcCommand(strUpdateCmd, odbConn)
    
          odbAdptr.UpdateCommand = cmdUpdate
    
          Dim cmdDelete As New OdbcCommand(strDeleteCmd, odbConn)
    
          odbAdptr.DeleteCommand = cmdDelete
    
          odbConn.Open()
    
          Dim rows As Integer = odbAdptr.Update(MyDataSet.Table1)
    
          MessageBox.Show(rows.ToString & " rows added" & vbCrLf & _
    
              "Select command: " & odbAdptr.SelectCommand.CommandText & vbCrLf & vbCrLf & _
    
              "Insert command: " & If(IsNothing(odbAdptr.InsertCommand), "Nothing", odbAdptr.InsertCommand.CommandText) & vbCrLf & vbCrLf & _
    
              "Update command: " & If(IsNothing(odbAdptr.UpdateCommand), "Nothing", odbAdptr.UpdateCommand.CommandText) & vbCrLf & vbCrLf & _
    
              "Delete command: " & If(IsNothing(odbAdptr.DeleteCommand), "Nothing", odbAdptr.DeleteCommand.CommandText), _
    
              "MyApp - Export")
    
         End Using
    
        Catch ex As Exception
    
         MessageBox.Show("Unable to open " & ExportFileNameLabel.Text & vbCrLf & "Error: " & ex.Message, "MyApp - Export")
    
        End Try
    
       Case Else
    
        ExportFileNameLabel.Text = saveResult.ToString
    
      End Select
    
     End Sub
    
    End Class
    
    

    Issue:
    All resulted in no rows being written to the .xls file and no errors being thrown.

    Request:
    Please help point out what I need to do to get the expected operation.

    Thanks,
    Eric

    Update:
    I'm not sure how it was deleted from the code above but the connection string does include a readonly parameter as below:

    Woah, I pasted it again and the ReadOnly parameter disappeared again so I typed it in by hand below:

    Dim strConn As String = "Driver={Microsoft Excel Driver (*.xls)};" & _
      "DBQ=" & ExportFileNameLabel.Text & "; ReadOnly=False;"

    Eric

    • Edited by Eric-67220 Wednesday, June 30, 2010 9:08 PM code update
    Wednesday, June 30, 2010 8:31 PM

Answers

  • Eric,

    You haven't posted the code that shows how you're filling the DataSet/DataTable but I'm pretty sure the current problem is that the data in your DataTable has no pending changes, which means that the DataAdapter doesn't think there are any changes to submit to your data store.  As a result, the call to DataAdapter.Update does nothing - no rows submitted, no exceptions generated.

    If you're retrieving the data from the source by calling DataAdapter.Fill, be sure to set the AcceptChangesDuringFill property on that DataAdapter to False prior to calling Fill.  This will cause the rows retrieved to be marked as pending inserts.  Another option is to manually call the SetAdded method on each DataRow you wish to be marked as a pending insert.

    I'd recommend using an OLE DB provider to talk to your Excel file.  The Microsoft ACE OLE DB provider (the "new and improved" version of the Jet engine) supports accessing Excel files.

    I used the code below to create a simple DataTable, add a couple rows, and export those rows to an Excel worksheet.  For another example, you could look at this Knowledge Base article:  http://support.microsoft.com/kb/316934/en-us

    I hope this information proves helpful.

    Dim path As String = ".\Customers.xls"
    Dim connectionStringBuilder As New OleDbConnectionStringBuilder
    connectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0"
    connectionStringBuilder.DataSource = path
    connectionStringBuilder("Extended Properties") = "Excel 8.0;HDR=YES;"
    Console.WriteLine(connectionStringBuilder.ConnectionString)
    Dim connection As New OleDbConnection()
    connection.ConnectionString = connectionStringBuilder.ConnectionString
    connection.Open()
    
    Dim commandText As String = "SELECT COUNT(*) FROM [Sheet1$]"
    Dim rowCountQuery As New OleDbCommand(commandText, connection)
    Console.WriteLine("Before insert, sheet has {0} row(s)", _
                      rowCountQuery.ExecuteScalar())
    
    commandText = "SELECT * FROM [Sheet1$]"
    Dim adapter As New OleDbDataAdapter(commandText, connection)
    Dim table As New DataTable("Customers")
    adapter.FillSchema(table, SchemaType.Source)
    
    'Add a couple rows
    table.Rows.Add(1, "Company One")
    table.Rows.Add(2, "Company Two")
    
    'Generate updating logic using a CommandBuilder
    'This is fine for test code, but supplying your own InsertCommand
    'will give you much better performance
    Dim commandBuilder As New OleDbCommandBuilder(adapter)
    
    'Make sure to set QuotePrefix and QuoteSuffix if your table or column
    'names will contain any special characters or reserved words
    commandBuilder.QuotePrefix = "["
    commandBuilder.QuoteSuffix = "]"
    
    'Show the InsertCommand's CommandText
    Console.WriteLine(commandBuilder.GetInsertCommand().CommandText)
    
    Dim rowsUpdated As Integer = adapter.Update(table)
    Console.WriteLine("Adapter.Update reports submitting changes to {0} row(s)", _
                      rowsUpdated)
    
    Console.WriteLine("After insert, sheet has {0} row(s)", _
                      rowCountQuery.ExecuteScalar())

    David Sceppa
    Thursday, July 1, 2010 9:05 PM
    Moderator

All replies

  • Eric,

    You haven't posted the code that shows how you're filling the DataSet/DataTable but I'm pretty sure the current problem is that the data in your DataTable has no pending changes, which means that the DataAdapter doesn't think there are any changes to submit to your data store.  As a result, the call to DataAdapter.Update does nothing - no rows submitted, no exceptions generated.

    If you're retrieving the data from the source by calling DataAdapter.Fill, be sure to set the AcceptChangesDuringFill property on that DataAdapter to False prior to calling Fill.  This will cause the rows retrieved to be marked as pending inserts.  Another option is to manually call the SetAdded method on each DataRow you wish to be marked as a pending insert.

    I'd recommend using an OLE DB provider to talk to your Excel file.  The Microsoft ACE OLE DB provider (the "new and improved" version of the Jet engine) supports accessing Excel files.

    I used the code below to create a simple DataTable, add a couple rows, and export those rows to an Excel worksheet.  For another example, you could look at this Knowledge Base article:  http://support.microsoft.com/kb/316934/en-us

    I hope this information proves helpful.

    Dim path As String = ".\Customers.xls"
    Dim connectionStringBuilder As New OleDbConnectionStringBuilder
    connectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0"
    connectionStringBuilder.DataSource = path
    connectionStringBuilder("Extended Properties") = "Excel 8.0;HDR=YES;"
    Console.WriteLine(connectionStringBuilder.ConnectionString)
    Dim connection As New OleDbConnection()
    connection.ConnectionString = connectionStringBuilder.ConnectionString
    connection.Open()
    
    Dim commandText As String = "SELECT COUNT(*) FROM [Sheet1$]"
    Dim rowCountQuery As New OleDbCommand(commandText, connection)
    Console.WriteLine("Before insert, sheet has {0} row(s)", _
                      rowCountQuery.ExecuteScalar())
    
    commandText = "SELECT * FROM [Sheet1$]"
    Dim adapter As New OleDbDataAdapter(commandText, connection)
    Dim table As New DataTable("Customers")
    adapter.FillSchema(table, SchemaType.Source)
    
    'Add a couple rows
    table.Rows.Add(1, "Company One")
    table.Rows.Add(2, "Company Two")
    
    'Generate updating logic using a CommandBuilder
    'This is fine for test code, but supplying your own InsertCommand
    'will give you much better performance
    Dim commandBuilder As New OleDbCommandBuilder(adapter)
    
    'Make sure to set QuotePrefix and QuoteSuffix if your table or column
    'names will contain any special characters or reserved words
    commandBuilder.QuotePrefix = "["
    commandBuilder.QuoteSuffix = "]"
    
    'Show the InsertCommand's CommandText
    Console.WriteLine(commandBuilder.GetInsertCommand().CommandText)
    
    Dim rowsUpdated As Integer = adapter.Update(table)
    Console.WriteLine("Adapter.Update reports submitting changes to {0} row(s)", _
                      rowsUpdated)
    
    Console.WriteLine("After insert, sheet has {0} row(s)", _
                      rowCountQuery.ExecuteScalar())

    David Sceppa
    Thursday, July 1, 2010 9:05 PM
    Moderator
  • David,

    I've been away from this project for several weeks.  Sorry for the delay and thanks for the assistance.
    You are correct no rows in the dataset source table (which has it's own dataset table adapter) have been changed as will usually be the case as this is an Export utility to take what is in the dataset and make an Excel sheet out of it.
    However, none of the rows exist in the destination Excel table so wouldn't they all be 'new' relative to the data adapter for the Excel table and require an Insert statement to effect the Update?

    I'll go ahead and try using SetAdded to all rows of the dataset source table but won't this in turn force all the rows to be inserted again into my source table the next time an update is called for it?

    The reason I haven't pursued the OLE BD provider is because you have to tell it which version of Excel is being used and I don't want to require my users to figure that out.  If there is a way to programmatically discover the installed Excel version to fill in the extended properties of the connection string please let me know.  If the user doesn't need to have Excel installed to create the .xls file I could just plug a lowest common denominator version for the connection string.

    Thanks for the code example.  I will need to study it some more, especially the following line -

    adapter.FillSchema(table, SchemaType.Source)
    

    Thanks,
    Eric

    Thursday, July 22, 2010 9:30 PM
  • Eric,

    The DataAdapter has no knowledge of what data resides in the destination data source.  It examines the RowState of the supplied DataRows and then uses that information to decide whether to execute one of the commands for updating or not.  This simplifies the logic in DataAdapter.Update.  In theory, you could construct a component that would sync the destination data source with the contents of the DataTable, but that's not how the DataAdapter works. 

    Marking the DataRows as being Added will tell the DataAdapter to fire the InsertCommand for each of those DataRows.  (Another option is to set AcceptChangesDuringFill on the DataAdapter to False, which will cause the DataRows retrieved via DataAdapter.Fill to have a RowState of Added.)

    When you submit a pending change via DataAdapter.Update, by default the DataAdapter will implicitly call AcceptChanges on the DataRows processed.  For a DataRow previously marked as Added, that DataRow will then have a RowState of Unchanged.  This process prevents DataRows from being processed as a pending change on subsequent calls to DataAdapter.Update.

    I'm not sure about the Excel version information.  That's worth researching on another thread.  My guess is that the version information that's important is the version of Excel that the file is formatted for.  For example, you can use Excel 2010 to work with Excel 2003-formatted files.  In that case, the version for the file would correspond to Excel 2003.  I'm no expert on this matter but there might be a simple solution you can employ.

    The call to FillSchema probably is not necessary for you.  FillSchema takes the original query and retrieves only the resultset schema but no rows.  I used that method so that my DataTable's schema would match the destination but remain empty.  If you already know the desired schema for the DataTable, multiple calls to DataTable.Columns.Add gives you more control and better performance.


    David Sceppa
    Friday, July 23, 2010 5:46 PM
    Moderator
  • Thanks David for your patience and thorough explanation.
    I'm beginning to understand DataAdapters a little better.

    I'll keep plugging at this and post back if I get stuck.

    BTW here's the code where the source DataSet is filled.

      Private Sub ApplyFilter()
        'Save any changes
        writeAll()
        'Apply the filter
        Dim txtSelect = "SELECT * FROM Table1 WHERE " & SelectedFilter.Text
        Table1TableAdapter.Adapter.SelectCommand.CommandText = txtSelect
        Table1TableAdapter.Fill(MyDataSet.Table1)
        SelectedFilter.Applied = True
        FilteredToolStripButton.Checked = True
      End Sub
    
      Private Sub RemoveFilter()
        'Save any changes
        writeAll()
        'Remove the filter
        Dim txtSelect = "SELECT * FROM Table1"
        Table1TableAdapter.Adapter.SelectCommand.CommandText = txtSelect
        Table1TableAdapter.Fill(MyDataSet.Table1)
        SelectedFilter.Applied = False
        FilteredToolStripButton.Checked = False
      End Sub
    

     

    Eric

    Friday, July 23, 2010 6:03 PM
  • David,

    Please let me ask for clarification on one point you made above.  I may be reading it incorrectly so please let me know what I'm missing.

    "The DataAdapter has no knowledge of what data resides in the destination data source."

    I'm reading this as the [destination] DataAdapter has no knowledge of what resides in the destination data source.
    This seems puzzling to me since the destination DataAdapter was declared with the connection string pointing to and opened on the [destination] Excel file.

    Dim adapter As New OleDbDataAdapter(commandText, connection)

    On the other hand, I would expect the TableAdapter already defined in my code to have knowledge of the state of the data in the particular table in the [source] DataSet and to respond appropriately to the DataSet TableManager UpdateAll command.

    In your code sample I believe you create a DataTable("Customers") referenced as 'table' just to have something to use as a source table to write to the Excel file right?  This would be like the existing table in my source DataSet right?

    The line of code that takes the data from the source 'table' and writes it to the Excel file is then one below right?

    Dim rowsUpdated As Integer = adapter.Update(table)

    Please let me know if I have this all wrong.

    BTW here is writeAll() referenced in the code I posted above.

      Public Sub writeAll()
        Try
          Me.Validate()
          Me.Table3BindingSource.EndEdit()
          Me.Table4BindingSource.EndEdit()
          Me.Table5BindingSource.EndEdit()
          Me.Table6Source.EndEdit()
          Me.Table7BindingSource.EndEdit()
          Me.Table8BindingSource.EndEdit()
          frmBikes.Validate()
          Me.Table9BindingSource.EndEdit()
          Me.Table10BindingSource.EndEdit()
          Me.Table2BindingSource.EndEdit()
          Me.Table1BindingSource.EndEdit()
          Me.TableAdapterManager.UpdateAll(Me.MyDataSet)
        Catch ex As Exception
          MessageBox.Show(ex.Message, ex.ToString)
        End Try
      End Sub
    
    

    Eric

     

    Friday, July 23, 2010 6:52 PM
  • David,

    One [hopefully] last installment on this barrage of questions.
    This time regarding the following [again].

    Dim table As New DataTable("Customers")
    adapter.FillSchema(table, SchemaType.Source)

    since the DataTable, table, has just been declared as New it has no schema right?
    So where does SchemaType.Source find any schema information? 
    .Source refers to the new table right?

    I must have my backward glasses on today.

    Eric

    Friday, July 23, 2010 7:18 PM
  • David,

    I've tried the code you provided and am getting the following error message.

    Error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

    Also, I spent the last couple of days coding up a small test program that can be used to try various export methods.  So far I have included code for the method I originally tried and the one you suggested.  If you have time to look at it, I'd be glad to send it to you.

    Thanks,
    Eric

    Thursday, July 29, 2010 8:50 PM
  • David,

    In the code you provided, I changed the connection string from Microsoft.ACE.12.0 to Microsoft.Jet.4.0.  I can now make a connection but err on the command builder.  Note there is no Excel sheet to start with.  I found I needed to change the command text from [Sheet1$] to [Sheet1] when this is the case.

    'Commented out the below as we will use the Animals table
    'Dim table As New DataTable("Customers")
    'adapter.FillSchema(table, SchemaType.Source)
    
    ''Add a couple rows
    'table.Rows.Add(1, "Company One")
    'table.Rows.Add(2, "Company Two")
    
    'Prepare source table
    strAction = "preparing source table"
    ZooDataSet.Animals.Clear()
    AnimalsTableAdapter.Adapter.AcceptChangesDuringFill = False
    AnimalsTableAdapter.Fill(ZooDataSet.Animals)
    msg("Source table contains " & ZooDataSet.Animals.Rows.Count.ToString & " rows")
    
    'Generate updating logic using a CommandBuilder
    strAction = "building commands"
    Dim commandBuilder As New OleDbCommandBuilder(adapter)
    
    'Make sure to set QuotePrefix and QuoteSuffix if your table or column
    'names will contain any special characters or reserved words
    commandBuilder.QuotePrefix = "["
    commandBuilder.QuoteSuffix = "]"
    
    'Show the InsertCommand's CommandText
    msg(commandBuilder.GetInsertCommand().CommandText)
    
    strAction = "updating Excel sheet"
    Dim rowsUpdated As Integer = adapter.Update(ZooDataSet.Animals)
    msg("Adapter.Update reports submitting changes to " & rowsUpdated.ToString & " row(s)")
    
    

    I get the following Error:

    Error: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

    ------------------------

    I also tried your suggestion for making the records in the source table appear as added by setting the table adapter AcceptChangesDuringFill to false prior to filling the table in my ODBC method.  Instead of running with no error but creating no Excel file I get an error stating the source table is not found.

    'Prepare source table
    strAction = "preparing source table"
    ZooDataSet.Animals.Clear()
    AnimalsTableAdapter.Adapter.AcceptChangesDuringFill = False
    AnimalsTableAdapter.Fill(ZooDataSet.Animals)
    msg("Source table contains " & ZooDataSet.Animals.Rows.Count.ToString & " rows")
    
    strAction = "updating Excel sheet"
    Dim rows As Integer = odbAdptr.Update(ZooDataSet.Animals)
    msg(rows.ToString & " rows added" & vbCrLf & _
    "Select command: " & odbAdptr.SelectCommand.CommandText & vbCrLf & vbCrLf & _
    "Insert command: " & If(IsNothing(odbAdptr.InsertCommand), "Nothing", odbAdptr.InsertCommand.CommandText) & vbCrLf & vbCrLf & _
    "Update command: " & If(IsNothing(odbAdptr.UpdateCommand), "Nothing", odbAdptr.UpdateCommand.CommandText) & vbCrLf & vbCrLf & _
    "Delete command: " & If(IsNothing(odbAdptr.DeleteCommand), "Nothing", odbAdptr.DeleteCommand.CommandText))
    
    

    The error I get is:

    Error: ERROR [07002] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'Animals'.  Make sure the object exists and that you spell its name and the path name correctly.

    Eric

     

    Friday, July 30, 2010 3:39 PM
  • David,

    I can get the OLEDB method you suggested to work when I start with an Excel file to export to that has the column names of the table to export in the first row.

    I now need to find a way to generate an Excel file with the column names in the first row in code vs by hand.

    Thanks for getting me started on a path with some promise for my application.

    If I treat the ODBC method similarly I can get it to work too.

    Eric

    Wednesday, August 4, 2010 7:06 PM