Hello!
I'm using the code below to import an Excel datasheet into an ado.net
table. The problem is, when I
get to the point where I want to iterate the recods, it always thinks
that there is 999 records in the table! If the table (worksheet) is
larger than that, then it returns an equally fictious number (always
way more).
Does anybody have any idea what I'm doing wrong? I'ts an excel 2003
spreadsheet that I'm reading from. Thanks in advance!
Sub readSheet()
Dim excelConn As New System.Data.OleDb.OleDbConnection
excelConn.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MySpreadSheet.xls;"
& _
"Extended Properties=""Excel
8.0;HDR=Yes;"""
excelConn.Open()
Dim da As New OleDbDataAdapter("Select * From [SheetName$]",
excelConn)
Dim escapeExit As Boolean = False
Dim ds As DataSet = New DataSet()
Dim dc As DataColumn = Nothing
Dim dt As DataTable = Nothing
Dim rec As String = ""
Dim i As Integer = 0
Dim x As Integer = 0
da.Fill(ds)
dt = excelConn.GetSchema
Dim dr As DataRow
While Not Me.bwWorker.CancellationPending
'HERE'S THE PROBLEM: IT ALWAYS DECIDES THAT THERE'S 999
RECORDS!!!
For Each dr In ds.Tables(0).Rows
rec = ""
For x = 0 To UBound(dr.ItemArray)
rec = rec & " " & dr.ItemArray(x).ToString
Next
logIt(txtOutput.Text & ControlChars.CrLf & rec)
'Next
Next
End While
excelConn.Close()
excelConn.Dispose()
End Sub