none
Updating database table from local table (from excel) - tried Table.Merge and Table.Load(IDataReader) methods, nothing is working! RRS feed

  • Question

  • Hi,

    I am trying to update the database used in my application from an excel sheet. I have so far been able to query the excel file, display in a datagrid, mess with table/column mappings, etc. Everything up to the point of getting it to actually update TO the database.

    I have tried the table.load method by creating a datatablereader from the excel table, and I've tried using merge between the two tables. Neither seems to affect either table at run-time, and no changes get updated to the database. I've checked from SQL Server Management Studio, as well. The code doesn't cause any errors. 

    Any guidance would be appreciated, even if only to point me to the right resources. I feel like I have read the entire MSDN Library! Here's my code:

    Module UpdateDB
    
        Sub UpdateDB(ByVal pathandfilename As String, ByVal xlversion As String, ByVal querystatement As String)
    
            'DATABASE CONNECTION
            Dim cnstrDB As String
            cnstrDB = "Server=Owner-pc;database=KanbanSuiteDB;Integrated Security=True;"
            Dim cnDB As New SqlConnection(cnstrDB)
            cnDB.Open()
    
            Dim dbSelect As String = "SELECT * FROM HCMC_Storeroom_Items" 'get field names from comboboxes on frmexceldata (those with boxes checked)
            Dim DAdb As New SqlDataAdapter(dbSelect, cnDB) 
            Dim dbtable As New DataTable
            DAdb.Fill(dbtable)
    
            cnDB.Close()
    
            'XL CONNECTION
            Dim cnstr As String
            cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & """" & pathandfilename & """" & ";" & _
                "Extended Properties=" & """" & xlversion & ";HDR=Yes;IMEX=1" & """"
            Dim cn As New OleDbConnection(cnstr)
            cn.Open()
    
            Dim xlQuery As String = querystatement
            Dim DAxl As New OleDbDataAdapter(xlQuery, cn)
            Dim xldata As New DataSet
            Dim srctbl As String = frmExcelData.cmbSheets.SelectedItem.ToString
    
    'I don't think the table/column mappings are the problem
            Dim dtm As DataTableMapping = DAxl.TableMappings.Add(srctbl, "XLtable")
            Dim i As Integer = 0
            For Each ComboBox As ComboBox In frmExcelData.flpSourceColumn.Controls
                'checkbox index matching current source column combobox index
                Dim srccolchkbx As System.Windows.Forms.CheckBox = frmExcelData.flpChkSourceCol.Controls(i)
                If srccolchkbx.CheckState = CheckState.Checked Then
                    Dim srccol As String = ComboBox.SelectedItem.ToString
                    'db column combox at same index
                    Dim DBcolIndex As Integer = frmExcelData.flpSourceColumn.Controls.IndexOf(ComboBox)
                    Dim cbox As System.Windows.Forms.ComboBox = frmExcelData.flpDBcol.Controls(DBcolIndex)
                    Dim dbcol As String = cbox.SelectedItem.ToString
                    dtm.ColumnMappings.Add(srccol, dbcol)
                End If
                i = i + 1
            Next
            DAxl.MissingMappingAction = MissingMappingAction.Error
            DAxl.Fill(xldata, srctbl)
    
            Dim xltable As DataTable = xldata.Tables("XLtable")
            Dim reader As New DataTableReader(xltable)
    
            dbtable.Load(reader)
    
            cn.Close()
    
            dbtable.Merge(xltable, True, MissingSchemaAction.Error)
    
            cnDB.Open()
            DAdb.Update(dbtable)
            cnDB.Close()
    
        End Sub
    
    End Module
    Obviously, I tried the merge and load methods independently. I just included them here to show my syntax in case of errors. 

    Thank you!

    Tuesday, April 3, 2012 3:21 AM

Answers

All replies