[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Working with the datareader

Using a While(datareader.read) structure, I am having some trouble. Is there an equivalent looping structure to the Do While recordset.eof = False for the datareader. Each time the cursor comes to While (datareader.read) line, it advances on record. Within the While(datareader.read) structure, I have to advance a record to grab an id of the next record to compare to the previous record. What ends up happening is that I advance two records when I really only want to advance one. My code is below. Bascially, if the While(rdrExtract.read) line did not advance a record, I would be in good shape. Any suggestions. Right now, I have another structure below this one that captures the first record of the next set of ids, then loop back to it. It works, but I have other problems I'm trying to deal with that are problems only because of this structure. Thanks.

 While (rdrExtract.Read)
            If intAppIdSingleResp = 0 Then
                intAppIdSingleResp = rdrExtract("cde_applicant_id")
            End If
            'the cursor will go into this if, then structure if there is only one record for the current candidate
            If intAppIdSingleResp <> rdrExtract("cde_applicant_id") Then
                dsTbl.Tables(0).Rows.Add(dr)
                dr = dsTbl.Tables(0).NewRow
            End If
            intPrevPerson = rdrExtract("cde_applicant_id")
            intCurPerson = rdrExtract("cde_applicant_id")
            dr("cde_applicant_id") = intCurPerson
            dr("txt_last") = rdrExtract("txt_last")
            dr("txt_first") = rdrExtract("txt_first")
            dr("dte_application_date") = rdrExtract("dte_application_date")
            Do Until intCurPerson <> intPrevPerson
                If rdrExtract("cde_question_type") = 2 Or rdrExtract("cde_question_type") = 4 _
                Or rdrExtract("cde_question_type") = 9 Or rdrExtract("cde_question_type") = 11 Or _
                rdrExtract("cde_question_type") = 13 Or rdrExtract("cde_question_type") = 14 Then
                    strField = rdrExtract("cde_question") & "_" & rdrExtract("cde_choice")
                    strField = "Q" & strField
                    dr(strField) = rdrExtract("num_order")
                Else
                    strField = rdrExtract("cde_question")
                    strField = "Q" & strField
                    If rdrExtract("cde_question_type") = 5 Or rdrExtract("cde_question_type") = 6 _
                    Or rdrExtract("cde_question_type") = 7 Then
                        dr(strField) = rdrExtract("txt_response")
                    Else
                        dr(strField) = rdrExtract("num_order")
                    End If
                End If
                If rdrExtract..Read() Then
                    intPrevPerson = rdrExtract("cde_applicant_id")
                    'intRecordsPer = intRecordsPer + 1
                Else
                    blnEnd = True
                    Exit Do
                End If
            Loop

0
Feyo
Asked:
Feyo
  • 4
  • 4
1 Solution
 
Bob LearnedCommented:
A DataReader is a forward-only view, so you can't go back.  My suggestion would be to use a DataTable, instead of a DataReader, since you can access any row in the table by index, and you wouldn't have that problem.

Bob
0
 
FeyoAuthor Commented:
I figured there wouldn't be a way around this. Speed is very important, so using the datareader is so darn important. Thanks for the reply.

0
 
Bob LearnedCommented:
If you need to stay with the data reader, then you need to come up with a way of not requiring the second .Read call.

Bob
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
FeyoAuthor Commented:
This is what I've done, and it works, but it's hard to debug and cause other undesirables.

hile (rdrExtract.Read)
            'the first loop will not have intAppIdSingleResp set, so have to set it
            If intAppIdSingleResp = 0 Then
                intAppIdSingleResp = rdrExtract("cde_applicant_id")
            End If
            'the cursor will go into this if, then structure if there is only one record for the previous candidate
            If intAppIdSingleResp <> rdrExtract("cde_applicant_id") Then
                dsTbl.Tables(0).Rows.Add(dr)
                dr = dsTbl.Tables(0).NewRow
            End If
            intPrevPerson = rdrExtract("cde_applicant_id")
            intCurPerson = rdrExtract("cde_applicant_id")
            dr("cde_applicant_id") = intCurPerson
            dr("txt_last") = rdrExtract("txt_last")
            dr("txt_first") = rdrExtract("txt_first")
            dr("dte_application_date") = rdrExtract("dte_application_date")
            Do Until intCurPerson <> intPrevPerson
                If rdrExtract("cde_question_type") = 2 Or rdrExtract("cde_question_type") = 4 _
                Or rdrExtract("cde_question_type") = 9 Or rdrExtract("cde_question_type") = 11 Or _
                rdrExtract("cde_question_type") = 13 Or rdrExtract("cde_question_type") = 14 Then
                    strField = rdrExtract("cde_question") & "_" & rdrExtract("cde_choice")
                    strField = "Q" & strField
                    dr(strField) = rdrExtract("num_order")
                Else
                    strField = rdrExtract("cde_question")
                    strField = "Q" & strField
                    If rdrExtract("cde_question_type") = 5 Or rdrExtract("cde_question_type") = 6 _
                    Or rdrExtract("cde_question_type") = 7 Then
                        dr(strField) = rdrExtract("txt_response")
                    Else
                        dr(strField) = rdrExtract("num_order")
                    End If
                End If
                If rdrExtract.Read() Then
                    intPrevPerson = rdrExtract("cde_applicant_id")
                Else
                    blnEnd = True
                    Exit Do
                End If
            Loop
            dsTbl.Tables(0).Rows.Add(dr)
            dr = dsTbl.Tables(0).NewRow
            'If the cursor is not at the end of the datareader and there are greater than 1 record per person then,
            'we need to add the first record of the next applicant below because due to the while(rdrextract.read)
            'the cursor skips over that when the loop begins anew
            If blnEnd = False Then
                dr("cde_applicant_id") = rdrExtract("cde_applicant_id")
                dr("txt_last") = rdrExtract("txt_last")
                dr("txt_first") = rdrExtract("txt_first")
                dr("dte_application_date") = rdrExtract("dte_application_date")
                intAppIdSingleResp = rdrExtract("cde_applicant_id")
                If rdrExtract("cde_question_type") = 2 Or rdrExtract("cde_question_type") = 4 _
                Or rdrExtract("cde_question_type") = 9 Or rdrExtract("cde_question_type") = 11 Or _
                rdrExtract("cde_question_type") = 13 Or rdrExtract("cde_question_type") = 14 Then
                    strField = rdrExtract("cde_question") & "_" & rdrExtract("cde_choice")
                    strField = "Q" & strField
                    dr(strField) = rdrExtract("num_order")
                Else
                    strField = rdrExtract("cde_question")
                    strField = "Q" & strField
                    If rdrExtract("cde_question_type") = 5 Or rdrExtract("cde_question_type") = 6 _
                    Or rdrExtract("cde_question_type") = 7 Then
                        dr(strField) = rdrExtract("txt_response")
                    Else
                        dr(strField) = rdrExtract("num_order")
                    End If
                End If
            End If
            blnEnd = False
            intRecordsPer = 0
        End While
0
 
Bob LearnedCommented:
Did you time this method, and then compare to a DataTable?

Bob
0
 
FeyoAuthor Commented:
No, just an assumption. The amount of data the is processed is huge, so I assume that it will be faster being read from the database as it gets to it rather than being loaded into a dataset and then processed. I may give it a try, though. Won't hurt.
0
 
Bob LearnedCommented:
Yes, I would normally make that assumption, too, but you're processing looks complicated, and actually be faster to use a DataTable versus a DataReader.

Bob
0
 
FeyoAuthor Commented:
I got around to re-write the code for a dataset. Turns out the code is infinitely easier to follow and keep up with - the time difference between the datareader and dataset is negligible. Thanks for the suggestion. I probably wouldn't have tried it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now