Update SQL Server Table with a Spreadsheet Worksheet DataTable using CommandBuilder
-
I have created a DataTable from a spreadsheet worksheet and want to UPDATE my SQL table, but am struggling. I cannot update the single table having a Primary Key with the changed column information. Should be easy and straight forward. The ShowResult(dataTable) depicts the correct DataTable information. (Using some DevExpress expressions) ' Create a data table with column names obtained from the first row in a range if it has headers. ' Column data types are obtained from cell value types of cells in the first data row of the worksheet range. Dim dtpHExportDataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders) ' Create the exporter that obtains data from the specified range, ' 'skips header row if required and populates the specified data ' table. Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range,dtpHExportDataTable, rangeHasHeaders) AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError ' Specify exporter options. exporter.Options.ConvertEmptyCells = True exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0 'Perform the export. exporter.Export() 'A custom method that displays the resulting data table. ShowResult(dtpHExportDataTable) 'Looks good! 'Now, my code to Update the "Result" column in my sql table with the datatable information Dim cnSQL As SqlConnection = dbLIMS.GetLIMSConnection Using adapter = New SqlDataAdapter("SELECT * FROM Analytical_Sample_Log_ResultsInfo", cnSQL) Using New SqlCommandBuilder(adapter) adapter.Fill(dtpHExportDataTable) cnSQL.Open() adapter.Update(dtpHExportDataTable) End Using End Using
-
I have created a DataTable from a spreadsheet worksheet and want to UPDATE my SQL table, but am struggling. I cannot update the single table having a Primary Key with the changed column information. Should be easy and straight forward. The ShowResult(dataTable) depicts the correct DataTable information. (Using some DevExpress expressions) ' Create a data table with column names obtained from the first row in a range if it has headers. ' Column data types are obtained from cell value types of cells in the first data row of the worksheet range. Dim dtpHExportDataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders) ' Create the exporter that obtains data from the specified range, ' 'skips header row if required and populates the specified data ' table. Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range,dtpHExportDataTable, rangeHasHeaders) AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError ' Specify exporter options. exporter.Options.ConvertEmptyCells = True exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0 'Perform the export. exporter.Export() 'A custom method that displays the resulting data table. ShowResult(dtpHExportDataTable) 'Looks good! 'Now, my code to Update the "Result" column in my sql table with the datatable information Dim cnSQL As SqlConnection = dbLIMS.GetLIMSConnection Using adapter = New SqlDataAdapter("SELECT * FROM Analytical_Sample_Log_ResultsInfo", cnSQL) Using New SqlCommandBuilder(adapter) adapter.Fill(dtpHExportDataTable) cnSQL.Open() adapter.Update(dtpHExportDataTable) End Using End Using
You can get an empty datatable from SQL with
Select * from Tablename where 1-1. This will give you the SQL data type, they may differ from the Excel data type. An update statement will look likeUpdate TableName set field1 = ValueFromExcelCell, field2 = ... where PrimareyKeyField = PrimaryKeyValueFromExcel
The update needs to be execute for each row in the ExcelTable where the data has changed
Never underestimate the power of human stupidity RAH