R
Ryan0827
Guest
I have a program that opens an Excel workbook (not visible to the user) using Excel.Interop. I set some cell values, then I fill a DataTable from a worksheet Table that has formulas dependent on these cells using a select statement in an OLEDB command/connection, but I always get the original file Table data, not the updated data from the currently opened workbook. Is there a way to select the current data in the Table from the currently opened workbook using OLEDB? Or do I have to use the Interop to loop thru the Table rows and fill my DataTable that way?
Partial Public Class QuoteItemBOMTableAdapter
Private WithEvents _fileAdapter As OleDbDataAdapter
Protected Friend ReadOnly Property FileAdapter() As OleDbDataAdapter
Get
If (Me._fileAdapter Is Nothing) Then
Me.InitFileAdapter()
End If
Return Me._fileAdapter
End Get
End Property
Public Overridable Overloads Function FillBySchema(ByVal dataTable As dsQuotes.QuoteItemBOMDataTable, itemId As Integer, table As Excel.ListObject) As Integer
Me.FileAdapter.SelectCommand = Me.GetSelectCommand(itemId, table)
If (Me.ClearBeforeFill = True) Then
dataTable.Clear()
End If
Dim returnValue As Integer = Me.FileAdapter.Fill(dataTable)
Return returnValue
End Function
Public Overridable Overloads Function GetDataBySchema(itemId As Integer, table As Excel.ListObject) As dsQuotes.QuoteItemBOMDataTable
Me.FileAdapter.SelectCommand = Me.GetSelectCommand(itemId, table)
Dim dataTable As New dsQuotes.QuoteItemBOMDataTable
Try
' Common error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
' 1) Check if Excel data is too long for database field data size.
' 2) Check if all Parcel # are NOT null.
' 3) Make sure all mapped header text are found in Excel file
Me.FileAdapter.Fill(dataTable)
Catch ex As OleDbException
If ex.ErrorCode = -2147217904 Or ex.ErrorCode = -2146233079 Then
MsgBox("Expected column name not found in import file.", vbInformation)
Return Nothing
End If
Throw ex
Catch ex2 As System.Data.ConstraintException
MsgBox(ex2.Message, vbInformation)
Return Nothing
End Try
Return dataTable
End Function
Private Sub InitFileAdapter()
Me._fileAdapter = New OleDbDataAdapter
Dim tableMapping As Global.System.Data.Common.DataTableMapping = New Global.System.Data.Common.DataTableMapping()
tableMapping.SourceTable = "Table"
Using productBOM As New dsQuotes.QuoteItemBOMDataTable
tableMapping.DataSetTable = productBOM.TableName
tableMapping.ColumnMappings.Add("ItemId", productBOM.QuoteItemIdColumn.ColumnName)
tableMapping.ColumnMappings.Add("Item Number", productBOM.ItemNumberColumn.ColumnName)
tableMapping.ColumnMappings.Add("Description", productBOM.ItemDescriptionColumn.ColumnName)
tableMapping.ColumnMappings.Add("UOM", productBOM.UnitOfMeasureColumn.ColumnName)
tableMapping.ColumnMappings.Add("Cost", productBOM.StandardCostColumn.ColumnName)
tableMapping.ColumnMappings.Add("Qty", productBOM.QtyColumn.ColumnName)
tableMapping.ColumnMappings.Add("Margin", productBOM.MarginColumn.ColumnName)
tableMapping.ColumnMappings.Add("Item Class", productBOM.ItemClassCodeColumn.ColumnName)
End Using
Me._fileAdapter.TableMappings.Add(tableMapping)
Me._fileAdapter.MissingSchemaAction = MissingSchemaAction.Error ' if columns selected from fileName aren't mapped to dsQuotes.ItemBOMDataTable properly, throw exception
End Sub
Private Function GetConnection(fileName As String) As OleDb.OleDbConnection
'The Microsoft.ACE.OLEDB.12.0 driver guesses datatypes for Excel columns to be text or numerics by scanning a specified number of rows.
' I need to force the ListSourceId column to be text, because the ListSourceId was mostly numerics and the text values were set to NULL because of the driver.
' To fix this:
' 1) Open Registry Editor
' 2) Go to: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
' 3) Change TypeGuessRows from 8 to 0. (8 is default)
' *I don't know if this works on other people computers.
'Read ExtendedProperties: Working with MS Excel(xls / xlsx) Using MDAC and Oledb
'Read comment from Siddharth Rout: Vb.NET Read Excel Missing Data
'Return New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & fileName & "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1;READONLY=True';")
Return New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;READONLY=True';")
End Function
Private Function GetSelectCommand(itemId As Integer, table As Excel.ListObject) As OleDbCommand
Dim cmd As New OleDbCommand
Dim sql As New Text.StringBuilder()
sql.AppendFormat("SELECT")
Using dt As New dsQuotes.QuoteItemBOMDataTable
For Each columnMap As DataColumnMapping In Me.FileAdapter.TableMappings(0).ColumnMappings
Select Case columnMap.DataSetColumn
Case dt.QuoteItemIdColumn.ColumnName
sql.AppendFormat(" {0} AS {1},", itemId, columnMap.SourceColumn)
Case Else
sql.AppendFormat(" [{0}],", columnMap.SourceColumn)
End Select
Next
sql.Replace(",", "", sql.ToString.Length - 1, 1)
'sql.AppendFormat(" FROM [{0}] WHERE {1} > 0;", table.Name, Me.FileAdapter.TableMappings(0).ColumnMappings.GetByDataSetColumn(dt.QtyColumn.ColumnName))
' sql.AppendFormat(" FROM [LEDSCREEN_FTX_BOM$A1:J64] WHERE {0} > 0;", Me.FileAdapter.TableMappings(0).ColumnMappings.GetByDataSetColumn(dt.QtyColumn.ColumnName))
' must remove "$" from address string or exception is thrown when trying to select data from the range
sql.AppendFormat(" FROM [{0}${1}] WHERE {2} > 0;", table.Range.Worksheet.Name, table.Range.Address.Replace("$", ""), Me.FileAdapter.TableMappings(0).ColumnMappings.GetByDataSetColumn(dt.QtyColumn.ColumnName))
End Using
cmd.Connection = Me.GetConnection(CType(table.Range.Worksheet.Parent, Excel.Workbook).FullName)
cmd.Connection = New OleDbConnection()
cmd.CommandText = sql.ToString
cmd.CommandType = CommandType.Text
Return cmd
End Function
End Class
Ryan
Continue reading...
Partial Public Class QuoteItemBOMTableAdapter
Private WithEvents _fileAdapter As OleDbDataAdapter
Protected Friend ReadOnly Property FileAdapter() As OleDbDataAdapter
Get
If (Me._fileAdapter Is Nothing) Then
Me.InitFileAdapter()
End If
Return Me._fileAdapter
End Get
End Property
Public Overridable Overloads Function FillBySchema(ByVal dataTable As dsQuotes.QuoteItemBOMDataTable, itemId As Integer, table As Excel.ListObject) As Integer
Me.FileAdapter.SelectCommand = Me.GetSelectCommand(itemId, table)
If (Me.ClearBeforeFill = True) Then
dataTable.Clear()
End If
Dim returnValue As Integer = Me.FileAdapter.Fill(dataTable)
Return returnValue
End Function
Public Overridable Overloads Function GetDataBySchema(itemId As Integer, table As Excel.ListObject) As dsQuotes.QuoteItemBOMDataTable
Me.FileAdapter.SelectCommand = Me.GetSelectCommand(itemId, table)
Dim dataTable As New dsQuotes.QuoteItemBOMDataTable
Try
' Common error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
' 1) Check if Excel data is too long for database field data size.
' 2) Check if all Parcel # are NOT null.
' 3) Make sure all mapped header text are found in Excel file
Me.FileAdapter.Fill(dataTable)
Catch ex As OleDbException
If ex.ErrorCode = -2147217904 Or ex.ErrorCode = -2146233079 Then
MsgBox("Expected column name not found in import file.", vbInformation)
Return Nothing
End If
Throw ex
Catch ex2 As System.Data.ConstraintException
MsgBox(ex2.Message, vbInformation)
Return Nothing
End Try
Return dataTable
End Function
Private Sub InitFileAdapter()
Me._fileAdapter = New OleDbDataAdapter
Dim tableMapping As Global.System.Data.Common.DataTableMapping = New Global.System.Data.Common.DataTableMapping()
tableMapping.SourceTable = "Table"
Using productBOM As New dsQuotes.QuoteItemBOMDataTable
tableMapping.DataSetTable = productBOM.TableName
tableMapping.ColumnMappings.Add("ItemId", productBOM.QuoteItemIdColumn.ColumnName)
tableMapping.ColumnMappings.Add("Item Number", productBOM.ItemNumberColumn.ColumnName)
tableMapping.ColumnMappings.Add("Description", productBOM.ItemDescriptionColumn.ColumnName)
tableMapping.ColumnMappings.Add("UOM", productBOM.UnitOfMeasureColumn.ColumnName)
tableMapping.ColumnMappings.Add("Cost", productBOM.StandardCostColumn.ColumnName)
tableMapping.ColumnMappings.Add("Qty", productBOM.QtyColumn.ColumnName)
tableMapping.ColumnMappings.Add("Margin", productBOM.MarginColumn.ColumnName)
tableMapping.ColumnMappings.Add("Item Class", productBOM.ItemClassCodeColumn.ColumnName)
End Using
Me._fileAdapter.TableMappings.Add(tableMapping)
Me._fileAdapter.MissingSchemaAction = MissingSchemaAction.Error ' if columns selected from fileName aren't mapped to dsQuotes.ItemBOMDataTable properly, throw exception
End Sub
Private Function GetConnection(fileName As String) As OleDb.OleDbConnection
'The Microsoft.ACE.OLEDB.12.0 driver guesses datatypes for Excel columns to be text or numerics by scanning a specified number of rows.
' I need to force the ListSourceId column to be text, because the ListSourceId was mostly numerics and the text values were set to NULL because of the driver.
' To fix this:
' 1) Open Registry Editor
' 2) Go to: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
' 3) Change TypeGuessRows from 8 to 0. (8 is default)
' *I don't know if this works on other people computers.
'Read ExtendedProperties: Working with MS Excel(xls / xlsx) Using MDAC and Oledb
'Read comment from Siddharth Rout: Vb.NET Read Excel Missing Data
'Return New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & fileName & "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1;READONLY=True';")
Return New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;READONLY=True';")
End Function
Private Function GetSelectCommand(itemId As Integer, table As Excel.ListObject) As OleDbCommand
Dim cmd As New OleDbCommand
Dim sql As New Text.StringBuilder()
sql.AppendFormat("SELECT")
Using dt As New dsQuotes.QuoteItemBOMDataTable
For Each columnMap As DataColumnMapping In Me.FileAdapter.TableMappings(0).ColumnMappings
Select Case columnMap.DataSetColumn
Case dt.QuoteItemIdColumn.ColumnName
sql.AppendFormat(" {0} AS {1},", itemId, columnMap.SourceColumn)
Case Else
sql.AppendFormat(" [{0}],", columnMap.SourceColumn)
End Select
Next
sql.Replace(",", "", sql.ToString.Length - 1, 1)
'sql.AppendFormat(" FROM [{0}] WHERE {1} > 0;", table.Name, Me.FileAdapter.TableMappings(0).ColumnMappings.GetByDataSetColumn(dt.QtyColumn.ColumnName))
' sql.AppendFormat(" FROM [LEDSCREEN_FTX_BOM$A1:J64] WHERE {0} > 0;", Me.FileAdapter.TableMappings(0).ColumnMappings.GetByDataSetColumn(dt.QtyColumn.ColumnName))
' must remove "$" from address string or exception is thrown when trying to select data from the range
sql.AppendFormat(" FROM [{0}${1}] WHERE {2} > 0;", table.Range.Worksheet.Name, table.Range.Address.Replace("$", ""), Me.FileAdapter.TableMappings(0).ColumnMappings.GetByDataSetColumn(dt.QtyColumn.ColumnName))
End Using
cmd.Connection = Me.GetConnection(CType(table.Range.Worksheet.Parent, Excel.Workbook).FullName)
cmd.Connection = New OleDbConnection()
cmd.CommandText = sql.ToString
cmd.CommandType = CommandType.Text
Return cmd
End Function
End Class
Ryan
Continue reading...