none
Most weirdest DbDataReader.Read() PROBLEM. RRS feed

  • Question

  • Private Sub StoreLineTableToTextFile()
            Dim command As DbCommand
            Dim connection As DbConnection
            Dim reader As DbDataReader
            Dim startLong, startLat, endLong, endLat As Double
            Dim color As Long
            Dim outputFile As New StreamWriter("MapsDbLineTable.txt")
    
            Try
                connection = CreateProviderConnection()
                Using connection
                    command = connection.CreateCommand()
                    command.CommandType = CommandType.Text
                    command.CommandText = "Select StartLong, StartLat, EndLong, EndLat, Color from Line"
                    connection.Open()
                    reader = command.ExecuteReader()
                    If reader.HasRows Then
                        While (reader.Read())
                            startLong = reader.GetDouble(0)
                            startLat = reader.GetDouble(1)
                            endLong = reader.GetDouble(2)
                            endLat = reader.GetDouble(3)
                            color = reader.GetInt32(4)
                            outputFile.WriteLine(startLong & " " & startLat & " " & endLong & " " & _
                                                endLat & " " & color)
                        End While
                    End If
                    reader.Close()
                    outputFile.Close()
                End Using
            Catch ex As Exception
                MsgBox("Error reading Maps.mdb." _
                       & vbCrLf & ex.Message & vbCrLf & ex.StackTrace)
            End Try
        End Sub

    The reader.read() first reads record 510, then 509, then 511, then 512 when reading.  Makes no sense.  There are 1302 records in total.  It should start at record 1 and progress to record 1302 normally.  This is not happening!
    A new refurbished computer for everyone!
    Saturday, March 28, 2009 5:41 PM

Answers

  • Do you have a timestamp or primary key field(s) that defines the order in which the rows were added? Otherwise, I'm not aware of any specific physical order of the rows in an Access table. 

    You could try compacting the database. But if that doesn't place the rows in the order they were added then I'm not sure what else to suggest. Typically if you want to designate row order you use a column by which the data can be sorted.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 30, 2009 7:23 PM

All replies

  • Do you use Jet OLEDB provider to connect to the database? I remember long time ago someone posted very similar question and, if I am not mistaken, the issue was related to provider's bug. I cannot find this information anymore though. Have you tried to check which version of provider you have on your PC? You could try to install the latest service pack for Jet.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, March 30, 2009 10:07 AM
    Moderator
  • Yes I am using Jet OleDb provider.  Also, I have Vista without SP1. 
    http://support.microsoft.com/kb/239114

    However, I have latest MSJETOLEDB40.dll (4.0.9635.0).
    There is newer version out for MSJET40.dll (I have an older 4.0.9635.0).
    I assume I need the latest one for the latter.

    Ajay

    A new refurbished computer for everyone!
    Monday, March 30, 2009 3:18 PM
  • Well, I cannot d/l any updates directly from MS's website or via Windows update.  It has been like this for some time actually.  I do not know what the problem is.

    AJay

    A new refurbished computer for everyone!
    Monday, March 30, 2009 3:53 PM
  • I don't see an ORDER BY clause so rows will be retrieved in whatever order the database engine retrieves them.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 30, 2009 4:40 PM
  • How do I retrieve the values in the order they are stored in the DB?

    A new refurbished computer for everyone!
    Monday, March 30, 2009 6:45 PM
  • Do you have a timestamp or primary key field(s) that defines the order in which the rows were added? Otherwise, I'm not aware of any specific physical order of the rows in an Access table. 

    You could try compacting the database. But if that doesn't place the rows in the order they were added then I'm not sure what else to suggest. Typically if you want to designate row order you use a column by which the data can be sorted.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, March 30, 2009 7:23 PM