DataReader Already Open

[rn5a]

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\)]

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!
********************************************
<rn**@rediffmail.comwrote in message
news:11**********************@45g2000cws.googlegro ups.com...

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.]

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.

<rn**@rediffmail.comwrote in message
news:11**********************@45g2000cws.googlegro ups.com...

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?