8 Replies - 700 Views - Last Post: 20 February 2018 - 09:26 PM Rate Topic: -----

#1 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 327
  • Joined: 20-May 16

There is already an open DataReader associated with this Command whic

Posted 19 February 2018 - 10:16 PM

i am using asp.net

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

what i think: i am using 2 readers on page load and 3rd when user click on a button so this maybe be a issue? or when user click on button, i am using nested sql statments so that me be also a issue?

it says reader is already open but i did close and dispose all my readers

how can i debug some thing like this?


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim sqlConn As New SqlConnection(myConnectionString)
        Dim dt As DataTable = New DataTable

        Try
            sqlConn.Open()

            Dim SelectQuery = "SELECT DISTINCT b.[bag_ID], b.[Account_ID], bi.[Prod... "

            Dim cmd1 = New SqlCommand(SelectQuery, sqlConn)
            cmd1.Parameters.AddWithValue("@Account_ID", Request.Cookies("Account_ID").Value)
            Dim reader As SqlDataReader = cmd1.ExecuteReader()

            Dim Total_QTY As Integer = 0
            Dim Merchandise_Subtotal As Decimal = 0.00
            While reader.Read()
                Total_QTY = Total_QTY + reader.GetInt32(8)
            End While
            Total_QTYL.Text = Total_QTY

            reader.Close()
            reader.Dispose()

            Dim reader2 As SqlDataReader = cmd1.ExecuteReader()
            dt.Load(reader2)
            reader2.close()
            reader2.Dispose()

            ' Get row count
            Dim a As Integer = dt.Rows.Count
            If a = 0 Then
                Response.Redirect("../Home.aspx")
            End If

            ' bind data to gridview
            Session("SaveData") = dt
            BagRepeater.DataSource = Session("SaveData")
            BagRepeater.DataBind()

            sqlConn.Close()
            ...
        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] (Account_ID, Total_AMT, Created_Date) " &
                                "VALUES (@Account_ID, @Total_AMT, @Created_Date); "
            Dim cmd1 = New SqlCommand(InsertOrderTB, sqlConn)
            cmd1.Parameters.AddWithValue("@Account_ID", Request.Cookies("Account_ID").Value)
            cmd1.Parameters.AddWithValue("@Total_AMT", Convert.ToDecimal(Session("Global_Total_AMT")))
            cmd1.Parameters.AddWithValue("@Created_Date", Date.Now)
            cmd1.ExecuteNonQuery()
            cmd1.CommandText = InsertOrderTBSelect
            Order_ID = cmd1.ExecuteScalar()


Dim SelectQuery2 = "SELECT DISTINCT b.[Account_ID], bi.[Product_ID], ... "
            Dim cmd3 = New SqlCommand(SelectQuery2, sqlConn)
           Dim reader3 As SqlDataReader = cmd3.ExecuteReader()
            While reader3.Read()
                Dim InsertOrderDetailTB = "INSERT INTO [ORDER_Detail_TB] (Order_ID, Product_ID, QTY,... "
                Dim cmd4 = New SqlCommand(InsertOrderDetailTB, sqlConn)
                cmd4.Parameters.AddWithValue("@Order_ID", Order_ID)
                ...
                cmd4.ExecuteNonQuery()
            End While
            reader3.Close()
end sub

This post has been edited by ikhlas06: 19 February 2018 - 10:42 PM


Is This A Good Question/Topic? 0
  • +

Replies To: There is already an open DataReader associated with this Command whic

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14244
  • View blog
  • Posts: 57,144
  • Joined: 12-June 08

Re: There is already an open DataReader associated with this Command whic

Posted 19 February 2018 - 11:44 PM

Copy/paste the exact error message.
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw   User is offline

  • Unprocessable Entity
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,724
  • Joined: 12-December 12

Re: There is already an open DataReader associated with this Command whic

Posted 20 February 2018 - 02:02 AM

Yes, provide error details and confirm on which line the error occurs.

However, there should be no need to execute the same statement twice. You can use Compute the determine the sum of a column.

(I also have a question mark over why you need reader3 and perform inserts for each row of of the reader?)
Was This Post Helpful? 1
  • +
  • -

#4 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 327
  • Joined: 20-May 16

Re: There is already an open DataReader associated with this Command whic

Posted 20 February 2018 - 07:24 AM

SelectQuery2 will return information about item what user has in his cart.

SelectQuery2 ex, results:
acount_ID | product_ID | Color | QTY | Size | Prize | 
2         | 2          | blk   | 1   | md   | 100   |
2         | 1          | red   | 2   | md   | 50    |
2         | 9          | yllw  | 1   | sm   | 10    |
2         | 5          | wht   | 5   | lg   | 10    |


than i want to insert these 4 rows in Order_Detail_TB table. this is where I used reader3 to loop though them.

error is with reader3

This post has been edited by ikhlas06: 20 February 2018 - 08:21 AM

Was This Post Helpful? 0
  • +
  • -

#5 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 327
  • Joined: 20-May 16

Re: There is already an open DataReader associated with this Command whic

Posted 20 February 2018 - 08:07 PM

i removed some of the code so its easy to see. i am closing each reader right after i am done using them. i am not sure why i am getting 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()
end sub	

Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14244
  • View blog
  • Posts: 57,144
  • Joined: 12-June 08

Re: There is already an open DataReader associated with this Command whic

Posted 20 February 2018 - 08:15 PM

I guess the entire error text won't be happening.. so it goes.

You can try adding 'MultipleActiveResultSets=true' to your connection string.

Alternatively re-instantiate the command object when used again.
Was This Post Helpful? 1
  • +
  • -

#7 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 327
  • Joined: 20-May 16

Re: There is already an open DataReader associated with this Command whic

Posted 20 February 2018 - 08:49 PM

i see, so i am gussing this error is caused by the following line:

cmd4.ExecuteNonQuery()



since this is in a while loop, it will make multi connections. i took your adviced and added 'multipleActiveResultSet' in my connecion string.

new error: No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.

Dim myConnectionString As String = "Data Source=111.11.111.111;Integrated Security=False;MultipleActiveResultSets=true;User ID=tsestID;password=pwwd;"

Dim reader3 As SqlDataReader = cmd3.ExecuteReader()
            While reader3.Read()
                Dim InsertOrderDetailTB = "INSERT INTO [ORDER_Detail_TB] (Order_ID, Product_ID, QTY, Color, Size, Product_Prize) " &
                                          "VALUES (@Order_ID, @Product_ID, @QTY, @Color, @Size, @Product_Prize); "
                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.Parameters.AddWithValue("@Size", reader3("Size"))
                cmd4.Parameters.AddWithValue("@Product_Prize", reader3("Price"))
                'cmd4.ExecuteNonQuery()
            End While
            reader3.Close()

This post has been edited by ikhlas06: 20 February 2018 - 08:54 PM

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14244
  • View blog
  • Posts: 57,144
  • Joined: 12-June 08

Re: There is already an open DataReader associated with this Command whic

Posted 20 February 2018 - 09:01 PM

Again, please post the entire error message and trace. It tends to help narrow down what you are showing and what is happening.

What is "Order_ID"?

08	                cmd4.Parameters.AddWithValue("@Order_ID", Order_ID)

Was This Post Helpful? 1
  • +
  • -

#9 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 1
  • View blog
  • Posts: 327
  • Joined: 20-May 16

Re: There is already an open DataReader associated with this Command whic

Posted 20 February 2018 - 09:26 PM

ops i was using textbox at buttom of my code... i just did textbox.text and it fixed the issue

This post has been edited by ikhlas06: 20 February 2018 - 09:52 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1