I am retrieving data from an Excel file via ADO.NET. The data being returned is not correct.
Cell(14,3)=Hello, but the value being returned is DBNull. Cell(15,3)=93, but the value being returned is '3/31/1900'. What is curious is that the data being returned for columns 1 & 2 are correct.
Any ideas
Public Sub ReadExcel(ByVal myConnStr As String)
Dim mySelectQuery As String = "SELECT * FROM [Timesheet$]"
Dim myConn As New OleDbConnection(myConnStr)
Dim myCommand As New OleDbCommand(mySelectQuery, myConn)
myConn.Open()
Dim myReader As OleDbDataReader
myReader = myCommand.ExecuteReader()
Dim sMsg As String, myRow As Int16, myCol As Int16
While myReader.Read()
myRow = myRow + 1
sMsg = sMsg & "Row(" & myRow & ")"
For myCol = 0 To myReader.FieldCount - 1
Try
If TypeOf myReader.Item(myCol) Is DBNull Then
sMsg = sMsg & "...NULL"
Else
sMsg = sMsg & "..." & myReader.GetValue(myCol)
End If
Catch ex As Exception
sMsg = sMsg & "..." & ex.Message
End Try
Next
sMsg = sMsg & Chr(10)
End While
myReader.Close()
myConn.Close()
MessageBox.Show(sMsg)
End Sub
Problem with ADO.NET and Excel
Marian Drumea
Eddie P
Yes I know what this is, or I think I do. Its because of the data in Excel, if ADO.NET cannot determine the type of data for a whole column in Excel then it doesn't import it.
See if you link an Excel spreadsheet to Access as a linked table and view the records in the table some records might contain #Num rather than a value. Its because there is a mix of text and numbers for that column and Access decides to 'guess' what the data type is.
There is an advance setting in connection strings that lets you try and control it, but its touch and go and worse than useless.
Check your spreadsheet, if you have a column that contains both text and numeric data then that column won't import unless all numbers in this column are specifically marked as text by placing a ' at the start of each value.
'4 instead of 4 for example.
That is what's happening. All the best.