none
How to check end of record RRS feed

  • Question

  • I have the following code that get's data from an SQL table and loop through it for me to do some other calculations.
    Inside the main While Loop and have 3 other While loops.
    In the lat while loop I need to move to the next record so I use accreader1.read statement
    My problem is when I do this and when the record pointer reaches to the last record and if I am checking a value (ex: (accReader1.GetValue(0).ToString) I get ther error "No data exists for the row/column."
    I am not sure how to get around this. Any help is greatly appreciated. I would like to know how to check whether I am at the end of the record.
    Following is the main whilte loop. I have lot of other code in the while loop that I didn't include as it is not required here.

            Dim sConnAcc As String = "Provider=Microsoft.JET.OLEDB.4.0; Data Source=" & "C:\ctl\Ctl.mdb"
            Dim connAccess As New OleDbConnection(sConnAcc)
            Dim cmdAccess As New OleDbCommand
            Dim accReader1 As OleDbDataReader
    
            Dim sSql As String
    
            If connAccess.State = ConnectionState.Closed Then
                connAccess.Open()
            End If
    
            sSql = "SELECT [Ctl].* FROM [Ctl] "
            cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
            accReader1 = cmdAccess.ExecuteReader
    
            cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
            accReader1 = cmdAccess.ExecuteReader
    
            Dim f1 As String = ""
            While accReader1.Read
                Dim blnStat As Boolean
                f1 = Trim(accReader1.GetValue(0))
                While blnStat = False
                    If accReader1.Read = True And f1 = Trim(accReader1.GetValue(0).ToString) Then
                        Dim blnStat1 As Boolean = False
                        Dim f2 As String
                        f2 = accReader1.GetValue(8).ToString
                        While blnStat1 = False
                            If accReader1.Read = True And f2 = accReader1.GetValue(8).ToString Then
                                Dim blnStat3 As Boolean = False
                                Dim f3 As String
                                f3 = accReader1.GetValue(9).ToString
                                While blnStat3 = False
                                    If accReader1.Read = True And f3 = accReader1.GetValue(9).ToString Then
                                        sSql = "Update ctldetail Set [f1_tot] = " & (txtqty.Text * txtAmt.Text) & ", [fdesc] = '" & accReader1.GetValue(12).ToString & "' Where [f_id] = '" & accReader1.GetValue(17).ToString & "'"
    
                                        cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
                                        cmdAccess.ExecuteScalar()
    
                                        accReader1.Read()
                                    Else
                                        blnStat3 = True
                                    End If
                                End While
                            Else
                                blnStat1 = True
                            End If
                        End While
                    Else
                        blnStat = True
                    End If
                End While
            End While
    







    Friday, July 7, 2017 4:27 PM

All replies

  • Maybe, you only need one read that is the While Read. Any other reads in the While loop should be removed and replaced with a While Continue that changes execution path within the While loop sending the execution back to the While to read the next record. In that way, you will hit the EOF/last record in the read as should be. 

    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/continue-statement

    Friday, July 7, 2017 7:14 PM
  • Hi dper77,

    According to your code, you use the acReader1.Read to move the next record, but I don't understanding why you do this.

    While accReader1.Read
                Dim blnStat As Boolean
                f1 = Trim(accReader1.GetValue(0))
                While blnStat = False
                    If accReader1.Read = True And f1 = Trim(accReader1.GetValue(0).ToString) Then
                        Dim blnStat1 As Boolean = False
                        Dim f2 As String
                        f2 = accReader1.GetValue(8).ToString
                        While blnStat1 = False
                            If accReader1.Read = True And f2 = accReader1.GetValue(8).ToString Then
                                Dim blnStat3 As Boolean = False
                                Dim f3 As String
                                f3 = accReader1.GetValue(9).ToString
                                While blnStat3 = False
                                    If accReader1.Read = True And f3 = accReader1.GetValue(9).ToString Then
                                        ' sSql = "Update ctldetail Set [f1_tot] = " & (txtqty.Text * txtAmt.Text) & ", [fdesc] = '" & accReader1.GetValue(12).ToString & "' Where [f_id] = '" & accReader1.GetValue(17).ToString & "'"
    
                                        cmdAccess = New System.Data.OleDb.OleDbCommand(sSql, connAccess)
                                        cmdAccess.ExecuteScalar()
    
                                        accReader1.Read()
                                    Else
                                        blnStat3 = True
                                    End If
                                End While
                            Else
                                blnStat1 = True
                            End If
                        End While
                    Else
                        blnStat = True
                    End If
                End While
            End While
     Can you please explain the bold line.

    Best Regards,

    Cherry



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 10, 2017 8:13 AM
    Moderator
  • Thanks for the reply. Will try it out.

    The statement that you highlighted was mistake I made.

    If accReader1.Read = True
    Tuesday, July 11, 2017 4:51 PM
  • Thanks for the reply. Will try it out.

    The statement that you highlighted was mistake I made.

    If accReader1.Read = True

    You do understand that you have too many accReader1.Read() statements trying to move to the next record, which is causing the reading of records out of sync and you are getting a premature end of file or EOF due to this.
    Tuesday, July 11, 2017 6:39 PM