Hi,
I am trying to fill the data of an excel sheet in a data table by using select query. When the sheet has parenthesis or any other special characters in its column names, I replace each one with "#" but execution results in error.
Private Sub ReadExcel
Dim sqlReadExcel as String = "Select [ModelCode] as ModelCode,[ListPrice] as ListPrice,[DiscountMultiplier] as Property39,[Cost] as Cost,[LeadTime#days#] as
LeadTime,[Manufacturer] as Manufacturer from [PPIF 2.6 Price Format$] where [ModelCode] = 'CP102-1-B-16S-005'"
If sqlReadExcel <> Nothing Then
Dim oDatabaseconnString As String
Dim dbConnection As OleDbConnection
Dim oDataAdapter As OleDbDataAdapter
Dim excelPath as String = "E:\excelFile.xlsx"
oDatabaseconnString = "PROVIDER='Microsoft.ACE.OLEDB.12.0;Data Source='" & excelPath & "';Extended Properties='Excel 8.0;'"
dbConnection = New OleDbConnection(oDatabaseconnString)
dbConnection.Open()
oDataAdapter = New OleDbDataAdapter(sqlReadExcel, dbConnection)
Try
oDataAdapter.Fill(dtExcelData)
Catch ex As Exception
Finally
oDataAdapter.Dispose()
oDataAdapter = Nothing
End Try
dbConnection.Close()
End If
End Sub
The execution of 16th line results in exception. The exception message is "Specified cast is not valid."
Any suggestions what should I do?
Thanks in advance