By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,045 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

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

P: 3
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

Expand|Select|Wrap|Line Numbers
  1. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  2.         If Not IsPostBack Then
  3.             ...
  4.             Dim reader As SqlDataReader = cmd1.ExecuteReader()
  5.             While reader.Read()
  6.                 ...
  7.             End While
  8.             reader.Close()
  9.             reader.Dispose()
  10.  
  11.             ...
  12.             Dim reader2 As SqlDataReader = cmd1.ExecuteReader()
  13.             dt.Load(reader2)
  14.             reader2.Close()
  15.             reader2.Dispose()
  16.  
  17.             BagRepeater.DataSource = dt
  18.             BagRepeater.DataBind()
  19.         End If
  20. End Sub
  21.  
  22. Public Sub CHECKOUTLB_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CHECKOUTLB.Click
  23.  Dim Order_ID As Long
  24.             Dim InsertOrderTBSelect As String = "Select @@Identity"
  25.             Dim InsertOrderTB = "INSERT INTO [ORDER_TB] ... "
  26.             Dim cmd1 = New SqlCommand(InsertOrderTB, sqlConn)
  27.             cmd1.ExecuteNonQuery()
  28.             cmd1.CommandText = InsertOrderTBSelect
  29.             Order_ID = cmd1.ExecuteScalar()
  30.  
  31.             Dim reader3 As SqlDataReader = cmd3.ExecuteReader()
  32.             While reader3.Read()
  33.                 Dim InsertOrderDetailTB = "INSERT INTO [ORDER_Detail_TB] ... "
  34.                 Dim cmd4 = New SqlCommand(InsertOrderDetailTB, sqlConn)
  35.                 cmd4.Parameters.AddWithValue("@Order_ID", Order_ID)
  36.                 cmd4.Parameters.AddWithValue("@Product_ID", reader3("Product_ID"))
  37.                 cmd4.Parameters.AddWithValue("@QTY", reader3("QTY"))
  38.                 cmd4.Parameters.AddWithValue("@Color", reader3("Color"))
  39.                 cmd4.ExecuteNonQuery()
  40.             End While
  41.             reader3.Close()
  42.  
Feb 21 '18 #1
Share this Question
Share on Google+
1 Reply


Frinavale
Expert Mod 5K+
P: 9,731
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:
Expand|Select|Wrap|Line Numbers
  1. If Not IsPostBack Then
  2.   ' ...
  3.   Using connection As New New SqlConnection(connectionString)
  4.     Using cmd1 as New SqlCommand("Select * From Table", connection)
  5.       Using reader As SqlDataReader = cmd1.ExecuteReader()
  6.         If reader.HasRows Then
  7.           While reader.Read()
  8.             '...
  9.           End While
  10.         End If
  11.       End Using
  12.     End Using
  13.   End Using
  14.  
  15.  ' ...
  16.   Using connection As New New SqlConnection(connectionString)
  17.     Using cmd2 as New SqlCommand("Select * From AnotherTable", connection)
  18.       Using reader2 As SqlDataReader = cmd2.ExecuteReader()
  19.         If reader2.HasRows Then
  20.           While reader2.Read()
  21.             '...
  22.           End While
  23.         End If
  24.       End Using
  25.     End Using
  26.   End Using
  27.  
  28.   BagRepeater.DataSource = dt
  29.   BagRepeater.DataBind()
  30. End If
  31.  
Feb 21 '18 #2

Post your reply

Sign in to post your reply or Sign up for a free account.