[RESOLVED] Inserting records into database table from Excel worksheet

[Mark@SF]

I need some advice on a problem trying to insert into an Access 2016 database from an Excel 2016 worksheet. My application imports a few (not all) of the columns from the worksheet into the database table.

This code executes w/o raising any errors, but does not result in any records being inserted into the database table:

                            lngStart = timerProcedure.ElapsedMilliseconds
                            frm.tsslMessage.Text = "Inserting prices from worksheet into database, please wait..."
                            frm.ssMain.Refresh()
                            strProvider = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ofd.FileName.ToString & ";Extended Properties=""Excel 12.0;HDR=Yes"""
                            strSQL = "INSERT INTO [MS Access;Database=" & My.Settings.myDataSource & "].[Asset Prices] SELECT " & lngAssetID & " AS AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, CalcAdjClose AS AdjClose FROM [" & xlsWS.Name & "$]"
                            Using myCon As New OleDb.OleDbConnection(strProvider)
                                Using myCmd As New OleDb.OleDbCommand(strSQL, myCon)
                                    myCon.Open()
                                    intResult = cmd.ExecuteNonQuery()
                                    myCon.Close()
                                    Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., database I/O)
                                    If _DEBUG Then
                                        Debug.Print("Insert " & Format(intResult, "#,##0") & " prices rows into database: " & Format(timerProcedure.ElapsedMilliseconds - lngStart, "#,##0") & " milliseconds")
                                    End If
                                End Using '...myCmd
                            End Using '...myCon

I know how to insert from Excel into the database table via a pair of data adapters. For example, this code works:

                            '...load the prices rows of the worksheet into the dtTemp datatable
                            lngStart = timerProcedure.ElapsedMilliseconds
                            frm.tsslMessage.Text = "Loading temporary prices table from worksheet, please wait..."
                            frm.ssMain.Refresh()
                            strProvider = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ofd.FileName.ToString & ";Extended Properties=""Excel 12.0;HDR=Yes"""
                            strSQL = "SELECT " & lngAssetID & " AS AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, CalcAdjClose AS AdjClose FROM [" & xlsWS.Name & "$]"
                            Using myCon As New OleDb.OleDbConnection(strProvider)
                                Using myCmd As New OleDb.OleDbCommand(strSQL)
                                    Using myAdp As New OleDb.OleDbDataAdapter(strSQL, myCon)
                                        dtTemp.Reset()  '...resets the DataTable to its original state (removes all data, indexes, relations, and columns)
                                        myAdp.Fill(dtTemp)
                                        For Each row As DataRow In dtTemp.Rows
                                            row.SetAdded()  '...necessary for the myAdp.Update(dtTemp)
                                        Next
                                        If _DEBUG Then
                                            Debug.Print("Load " & Format(dtTemp.Rows.Count, "#,##0") & " prices rows into datatable: " & Format(timerProcedure.ElapsedMilliseconds - lngStart, "#,##0") & " milliseconds")
                                            If dtTemp.Rows.Count > 0 Then
                                                Debug.WriteLine("Rows = {0}, Columns = {1}", CStr(dtTemp.Rows.Count), CStr(dtTemp.Columns.Count))
                                                For iRow As Integer = 0 To 9
                                                    Debug.Print("Row #" & iRow)
                                                    For iCol As Integer = 0 To dtTemp.Columns.Count - 1
                                                        Debug.WriteLine(vbTab & "{0} = {1}", dtTemp.Columns(iCol).ColumnName, dtTemp.Rows(iRow).Item(iCol).ToString)
                                                    Next iCol
                                                Next iRow
                                            End If '...dtTemp.Rows.Count > 0
                                        End If '..._DEBUG
                                    End Using '...myAdp
                                End Using '...myCmd
                            End Using '...myCon

                            '...save the prices rows of the dtTemp datatable to the Asset Prices table
                            lngStart = timerProcedure.ElapsedMilliseconds
                            frm.tsslMessage.Text = "Saving prices to database, please wait..."
                            frm.ssMain.Refresh()
                            If dtTemp.Rows.Count > 0 Then
                                lngStart = timerProcedure.ElapsedMilliseconds
                                Using myAdp As New OleDb.OleDbDataAdapter
                                    strSQL = "INSERT INTO [Asset Prices] (AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, AdjClose) VALUES (@AssetID, @RecDate, @OpenPrice, @HighPrice, @LowPrice, @ClosePrice, @DayVol, @AdjClose)"
                                    myAdp.InsertCommand = New OleDb.OleDbCommand(strSQL, con)
                                    myAdp.InsertCommand.Parameters.Add("@AssetID", OleDb.OleDbType.BigInt).SourceColumn = "AssetID"
                                    myAdp.InsertCommand.Parameters.Add("@RecDate", OleDb.OleDbType.Date).SourceColumn = "RecDate"
                                    myAdp.InsertCommand.Parameters.Add("@OpenPrice", OleDb.OleDbType.Double).SourceColumn = "OpenPrice"
                                    myAdp.InsertCommand.Parameters.Add("@HighPrice", OleDb.OleDbType.Double).SourceColumn = "HighPrice"
                                    myAdp.InsertCommand.Parameters.Add("@LowPrice", OleDb.OleDbType.Double).SourceColumn = "LowPrice"
                                    myAdp.InsertCommand.Parameters.Add("@ClosePrice", OleDb.OleDbType.Double).SourceColumn = "ClosePrice"
                                    myAdp.InsertCommand.Parameters.Add("@DayVol", OleDb.OleDbType.BigInt).SourceColumn = "DayVol"
                                    myAdp.InsertCommand.Parameters.Add("@AdjClose", OleDb.OleDbType.Double).SourceColumn = "AdjClose"
                                    myAdp.Update(dtTemp)
                                    Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., database I/O)
                                    If _DEBUG Then
                                        Debug.Print("Save " & Format(dtTemp.Rows.Count, "#,##0") & " prices records to database: " & Format(timerProcedure.ElapsedMilliseconds - lngStart, "#,##0") & " milliseconds")
                                    End If
                                    lngPriceCount = dtTemp.Rows.Count
                                End Using '...myAdp
                            End If '...dtTemp.Rows.Count > 0

In other projects, I import from a CSV text file into the database without having to use the pair of data adapters approach. For example:

            strSQL = "INSERT INTO [Asset Prices] ( AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, AdjClose ) " & _
                "SELECT AssetID, [Date], Open, High, Low, Close, Volume, AdjClose " & _
                "FROM [Text;Database=" & strFolder & ";HDR=yes].[" & strFile & "]"

I am wanting to avoid the overhead of creating 2 data adapters and a temporary data table, and instead would like to do a single INSERT INTO from the worksheet to the database (like I can do from a CSV text file to the database).

Any suggestions would be most appreciated.

Thanks.