there is already an open DataReader associated with this Command which must be closed

[ikhlas]

i am using vb asp.net

error: there is already an open DataReader associated with this Command which must be closed first.

working on this issue for couple weeks now and i have no idea how to fix this. any help will be helpful

i am using 2 reader on page load to display some information, but each of them i am closing it.

after that i am using 3rd reader on button click, which is giving me this error

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ...
            Dim reader As SqlDataReader = cmd1.ExecuteReader()
            While reader.Read()
                ...
            End While
            reader.Close()
            reader.Dispose()
 
            ...
            Dim reader2 As SqlDataReader = cmd1.ExecuteReader()
            dt.Load(reader2)
            reader2.Close()
            reader2.Dispose()
 
            BagRepeater.DataSource = dt
            BagRepeater.DataBind()
        End If
End Sub
 
Public Sub CHECKOUTLB_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CHECKOUTLB.Click
 Dim Order_ID As Long
            Dim InsertOrderTBSelect As String = "Select @@Identity"
            Dim InsertOrderTB = "INSERT INTO [ORDER_TB] ... "
            Dim cmd1 = New SqlCommand(InsertOrderTB, sqlConn)
            cmd1.ExecuteNonQuery()
            cmd1.CommandText = InsertOrderTBSelect
            Order_ID = cmd1.ExecuteScalar()
 
            Dim reader3 As SqlDataReader = cmd3.ExecuteReader()
            While reader3.Read()
                Dim InsertOrderDetailTB = "INSERT INTO [ORDER_Detail_TB] ... "
                Dim cmd4 = New SqlCommand(InsertOrderDetailTB, sqlConn)
                cmd4.Parameters.AddWithValue("@Order_ID", Order_ID)
                cmd4.Parameters.AddWithValue("@Product_ID", reader3("Product_ID"))
                cmd4.Parameters.AddWithValue("@QTY", reader3("QTY"))
                cmd4.Parameters.AddWithValue("@Color", reader3("Color"))
                cmd4.ExecuteNonQuery()
            End While
            reader3.Close()
 

[Frinavale]

I believe your problem is that you are using the same command cmd1 for readers.

Consider making use of the using statement to properly close and dispose of unmanaged code supporting the .NET objects used for getting and setting data from/to the database.



For example:

If Not IsPostBack Then
  ' ...
  Using connection As New New SqlConnection(connectionString)
    Using cmd1 as New SqlCommand("Select * From Table", connection)
      Using reader As SqlDataReader = cmd1.ExecuteReader()
        If reader.HasRows Then
          While reader.Read()
            '...
          End While
        End If
      End Using
    End Using
  End Using
 
 ' ...
  Using connection As New New SqlConnection(connectionString)
    Using cmd2 as New SqlCommand("Select * From AnotherTable", connection)
      Using reader2 As SqlDataReader = cmd2.ExecuteReader()
        If reader2.HasRows Then
          While reader2.Read()
            '...
          End While
        End If
      End Using
    End Using
  End Using
 
  BagRepeater.DataSource = dt
  BagRepeater.DataBind()
End If