VB Forum / Database Access / November 2006

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