Urgent! trying to get a recursive datareader to work

this grabs a number and a product, if the number and product exists, it processes it in get_row(), then it checks for a last job number, if it exists, calls itself again, otherwise it exists. Dont know what i'm doing wrong. I'm closing it, and I get the error message

Invalid attempt to Read when reader is closed.
happens on the line --->While r.Read()

    Public Shared Sub recursive(ByVal strVT_Number As String, ByVal strVT_Product As String)
        Dim strVT_Last As String
        Dim strVT_Prod As String
        Dim sqlCommtest As New SqlCommand("SELECT vt_job_number, vt_product, vt_last_job_number FROM vt_newsletters WHERE (vt_print_outside = 1) AND vt_product = '" & strVT_Product & "' and vt_job_number = " & strVT_Number, conRow)
        Dim r As SqlDataReader = sqlCommtest.ExecuteReader()
        While r.Read()
            Get_Row(r("vt_job_number"), r("vt_product"))
            If r("vt_last_job_number") <> "" Then
                strVT_Last = r("vt_last_job_number")
                strVT_Prod = r("vt_product")
                r.Close()
                recursive(strVT_Last, strVT_Prod)
            Else
                r.Close()
            End If
        End While
        DR = DSGrid.Tables(0).NewRow
        DSGrid.Tables(0).Rows.Add(DR)
    End Sub
Term56Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Raju SrivatsavayeSoftware EngineerCommented:
Do this:

Dim r As SqlDataReader = sqlCommtest.ExecuteReader(CommandBehavior.CloseConnection)
That should work
0
 
Raju SrivatsavayeSoftware EngineerCommented:
It might be Data.CommandBehavior.CloseConnection..Sometimes its this way..

0
 
Raju SrivatsavayeSoftware EngineerCommented:
Also you closed the reader in the while loop. Thats not correct.

Reader should be closed only aftet the while r.read loop.

if you want to exit the loop after else use exit while or something like that
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Term56Author Commented:
none of the answers has helped, please cancel the open ticket

thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.