[RESOLVED] I thought my ADO.NET problem was resolved

[Pasvorto]

Looking at the following code.

            Using cnxn2 As New SqlClient.SqlConnection(cnxnstring)
                cnxn2.Open()
                Using sql2 As New SqlClient.SqlCommand
                    sql2.CommandText = "SELECT * FROM PODETAIL WHERE [PONUM] = " & glbPONum
                    sql2.CommandText &= " AND [STATUS] = " & "'" & "A" & "'"
                    sql2.CommandText &= " ORDER BY [LINENUM]"
                    sql2.CommandType = CommandType.Text
                    sql2.Connection = cnxn2
                    Dim reader2 As SqlDataReader
                    reader2 = sql2.ExecuteReader
                    Using reader2
                        Dim totalreceived As Integer = 0
                        If reader2.HasRows Then
                            While reader2.Read
                                x = CShort(reader2("LINENUM") - 1 & "")
                                txtPart(x).Text = reader2("partnum") & ""
                                txtDescription(x).Text = Trim(reader2("NOTES")) & ""
                                txtDeliveryDate(x).Text = VB6.Format(reader2("DELIVERYDATE"), "mm/dd/yyyy") & ""
                                txtQuantity(x).Text = reader2("QUANTITY") & ""
                                txtUnitPrice(x).Text = reader2("UNITPRICE") & ""
                                txtUOM(x).Text = Trim(reader2("UOM")) & ""
                                '
                                '--------------
                                'get receipts
                                '--------------
                                '
                                Using sql3 As New SqlClient.SqlCommand
                                    sql3.CommandText = "SELECT * FROM RECEIPTHISTORY WHERE [PONUMBER] = " & CInt(glbPONum)
                                    sql3.CommandText &= " AND [PARTNUMBER] = " & "'" & reader2("partnum") & "'"
                                    sql3.CommandType = CommandType.Text
                                    sql3.Connection = cnxn2
                                    Dim reader3 As SqlDataReader
                                    reader3 = sql3.ExecuteReader
                                    Using reader3
                                        If reader3.HasRows Then
                                            totalreceived = 0
                                            While reader3.Read
                                                totalreceived = totalreceived + reader3("QUANTITY")
                                            End While
                                            lblQtyRcvd(x).Text = CStr(totalreceived)
                                        Else
                                            lblQtyRcvd(x).Text = "0"
                                        End If
                                    End Using
                                End Using
                                '===============
                            End While
                        End If
                    End Using
                End Using
            End Using
            '===============

when it gets to this line:

reader3 = sql3.ExecuteReader

I get an error : There is already an open DataReader associated with this command which must be closed first.

I might add that I get the error AFTER the code has executed once. So, it is tripping when going through the While Loop the 2nd time.

I tried adding a reader3.close after the using. That didn't work. I tried moving the reader3 Dim statement outside the While loop and that didn't work either.

ideas?

[techgnome]

If a reader is using a connection, that connection is locked, nothing else can use it.
Since cnxn2 is tied up with reader2, you can't re-use it with reader3.

The solution is to not use a reader on the outside for the looping. Just get a datatable, which will be disconnected by nature, loop through that and use the reader (and same connection) for the inner loop.

-tg

[GaryMazzone]

Since reader2 and reader3 are using the same connection ( cnxn2 ) that is not allowed. Only one reader at a time is allowed on a connection


--I'm slow

[Pasvorto]

That's what i thought. But, for some reason, the responses I got to an earlier query on datasets lead me to believe that one a connection was open, it could be used by readers at one time. Arghhh. I spent a good portion of the last 2 days trying to "optimize" my apps by opening multiple readers on one connection. I guess I need to put it back where it was.

[Pasvorto]

Fortunately I have good backups and did not need to undo everything I had done over the last 2 days. <phew>