DataReader Already Open

Discussion in 'ASP .Net' started by rn5a@rediffmail.com, Nov 24, 2006.

  1. Guest

    The following code resides in a VB class file name GetOrder.vb (this
    class file exists in the App_Code directory):

    Namespace Shop
    Public Class Orders
    Public Function ViewOrder(ByVal UserID As Integer, ByVal
    OrderID As Integer) As SqlDataAdapter
    Dim sqlDapter As SqlDataAdapter
    Dim sqlConn As SqlConnection

    sqlConn = New SqlConnection("......")

    sqlDapter = New SqlDataAdapter
    sqlDapter.SelectCommand = New SqlCommand("spViewOrder",
    sqlConn)
    sqlDapter.SelectCommand.CommandType =
    CommandType.StoredProcedure

    Try
    With sqlDapter.SelectCommand
    .Parameters.Add("@UserID", SqlDbType.Int).Value =
    UserID
    .Parameters.Add("@OrderID", SqlDbType.Int).Value =
    OrderID
    End With

    sqlConn.Open()
    If (sqlDapter.SelectCommand.ExecuteReader.HasRows) Then
    Return sqlDapter
    Else
    Return Nothing
    End If
    Catch ex As Exception
    Throw ex
    End Try
    End Function
    End Class
    End Namespace

    This is how an ASPX page named GetOrder.aspx accesses the above
    function which returns a SqlDataAdapter:

    <%@ Import Namespace="Shop" %>
    'import other namespaces

    Sub UserCart()
    Dim dSet As DataSet
    Dim boOrders As Orders
    Dim sqlDapter As SqlDataAdapter

    dSet = New DataSet
    boOrders = New Orders

    sqlDapter = boOrders.ViewOrder(iUserID, iOrderID)
    sqlDapter.Fill(dSet)
    End Sub

    When I run the ASPX page, the following error gets generated:

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

    pointing to the sqlDapter.Fill(dSet) line.

    How do I overcome this error?
    , Nov 24, 2006
    #1
    1. Advertising

  2. Consume the data it is already getting or close the reader or use a
    different adapter. The pattern used passes back is in the midst of a query
    (how many rows) that is pulled by a reader.

    --
    Gregory A. Beamer
    MVP; MCP: +I, SE, SD, DBA
    http://gregorybeamer.spaces.live.com

    ********************************************
    Think outside the box!
    ********************************************
    <> wrote in message
    news:...
    > The following code resides in a VB class file name GetOrder.vb (this
    > class file exists in the App_Code directory):
    >
    > Namespace Shop
    > Public Class Orders
    > Public Function ViewOrder(ByVal UserID As Integer, ByVal
    > OrderID As Integer) As SqlDataAdapter
    > Dim sqlDapter As SqlDataAdapter
    > Dim sqlConn As SqlConnection
    >
    > sqlConn = New SqlConnection("......")
    >
    > sqlDapter = New SqlDataAdapter
    > sqlDapter.SelectCommand = New SqlCommand("spViewOrder",
    > sqlConn)
    > sqlDapter.SelectCommand.CommandType =
    > CommandType.StoredProcedure
    >
    > Try
    > With sqlDapter.SelectCommand
    > .Parameters.Add("@UserID", SqlDbType.Int).Value =
    > UserID
    > .Parameters.Add("@OrderID", SqlDbType.Int).Value =
    > OrderID
    > End With
    >
    > sqlConn.Open()
    > If (sqlDapter.SelectCommand.ExecuteReader.HasRows) Then
    > Return sqlDapter
    > Else
    > Return Nothing
    > End If
    > Catch ex As Exception
    > Throw ex
    > End Try
    > End Function
    > End Class
    > End Namespace
    >
    > This is how an ASPX page named GetOrder.aspx accesses the above
    > function which returns a SqlDataAdapter:
    >
    > <%@ Import Namespace="Shop" %>
    > 'import other namespaces
    >
    > Sub UserCart()
    > Dim dSet As DataSet
    > Dim boOrders As Orders
    > Dim sqlDapter As SqlDataAdapter
    >
    > dSet = New DataSet
    > boOrders = New Orders
    >
    > sqlDapter = boOrders.ViewOrder(iUserID, iOrderID)
    > sqlDapter.Fill(dSet)
    > End Sub
    >
    > When I run the ASPX page, the following error gets generated:
    >
    > There is already an open DataReader associated with this Command which
    > must be closed first.
    >
    > pointing to the sqlDapter.Fill(dSet) line.
    >
    > How do I overcome this error?
    >
    Cowboy \(Gregory A. Beamer\), Nov 24, 2006
    #2
    1. Advertising

  3. Scott M. Guest

    This line:

    If (sqlDapter.SelectCommand.ExecuteReader.HasRows) Then

    causes the DataReader to be created and opened.

    What you should do is change the function to fill a dataset and return that,
    rather than returning a DataAdapter. The DataSet can then be checked to see
    if it contains any data or not. A DataReader is not needed here since you
    have a DataAdapter which will do the work.



    <> wrote in message
    news:...
    > The following code resides in a VB class file name GetOrder.vb (this
    > class file exists in the App_Code directory):
    >
    > Namespace Shop
    > Public Class Orders
    > Public Function ViewOrder(ByVal UserID As Integer, ByVal
    > OrderID As Integer) As SqlDataAdapter
    > Dim sqlDapter As SqlDataAdapter
    > Dim sqlConn As SqlConnection
    >
    > sqlConn = New SqlConnection("......")
    >
    > sqlDapter = New SqlDataAdapter
    > sqlDapter.SelectCommand = New SqlCommand("spViewOrder",
    > sqlConn)
    > sqlDapter.SelectCommand.CommandType =
    > CommandType.StoredProcedure
    >
    > Try
    > With sqlDapter.SelectCommand
    > .Parameters.Add("@UserID", SqlDbType.Int).Value =
    > UserID
    > .Parameters.Add("@OrderID", SqlDbType.Int).Value =
    > OrderID
    > End With
    >
    > sqlConn.Open()
    > If (sqlDapter.SelectCommand.ExecuteReader.HasRows) Then
    > Return sqlDapter
    > Else
    > Return Nothing
    > End If
    > Catch ex As Exception
    > Throw ex
    > End Try
    > End Function
    > End Class
    > End Namespace
    >
    > This is how an ASPX page named GetOrder.aspx accesses the above
    > function which returns a SqlDataAdapter:
    >
    > <%@ Import Namespace="Shop" %>
    > 'import other namespaces
    >
    > Sub UserCart()
    > Dim dSet As DataSet
    > Dim boOrders As Orders
    > Dim sqlDapter As SqlDataAdapter
    >
    > dSet = New DataSet
    > boOrders = New Orders
    >
    > sqlDapter = boOrders.ViewOrder(iUserID, iOrderID)
    > sqlDapter.Fill(dSet)
    > End Sub
    >
    > When I run the ASPX page, the following error gets generated:
    >
    > There is already an open DataReader associated with this Command which
    > must be closed first.
    >
    > pointing to the sqlDapter.Fill(dSet) line.
    >
    > How do I overcome this error?
    >
    Scott M., Nov 25, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. James Brown
    Replies:
    1
    Views:
    5,412
    bruce barker
    Jun 26, 2003
  2. =?Utf-8?B?R3JhbnQ=?=
    Replies:
    2
    Views:
    940
    Joanna
    Sep 13, 2006
  3. Rob Nicholson
    Replies:
    15
    Views:
    20,400
    Rob Nicholson
    Sep 24, 2005
  4. jason
    Replies:
    3
    Views:
    589
    jason
    Aug 7, 2006
  5. Bart
    Replies:
    13
    Views:
    804
    =?ISO-8859-1?Q?G=F6ran_Andersson?=
    Mar 28, 2007
Loading...

Share This Page