Search code examples
sql-servervb.netdatareader

Nested Datareader in VB.Net

I am Using .net 4.0 Framework (VB) with SQL Express 2005. I need to use do while nesting with datareader. some thing like.......

SQLQuery1="Select * from Table1"
cmd = New Data.SqlClient.SqlCommand(SQLQuery1, myConn)
If cmd.Connection.State = 1 Then cmd.Connection.Close()
cmd.Connection.Open()
dr = cmd.ExecuteReader
Do While dr.Read
  SQLQuery2="Select * from Table2 where ID=" & dr.item("field1")
  cmd2 = New Data.SqlClient.SqlCommand(SQLQuery2, myConn)
  If cmd2.Connection.State = 1 Then cmd2.Connection.Close()
  cmd2.Connection.Open()
  dr2 = cmd2.ExecuteReader
  Do While dr2.Read
    --------- Execute Insert/Update Statement here ----------------
  loop
  dr2.close
loop
dr.close

It execute first time but when it return to "Do While dr.Read" second time it gives error Invalid attempt to call Read when reader is closed. i am using MARS=true in connectionstring but not seen any difference.

Where i am doing wrong?

Thanks

Solution

  • From Microsoft help:

    When you use versions of SQL Server before SQL Server 2005, while the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader. While in this state, no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. Starting with SQL Server 2005, the multiple active result set (MARS) feature allows for multiple actions using the same connection.

    So, I'm afraid that you need different connections to nest data readers