Wrox Programmer Forums
|
BOOK: Professional ASP.NET 2.0 and Special Edition; ISBN: 978-0-7645-7610-2; ISBN: 978-0-470-04178-9
This is the forum to discuss the Wrox book Professional ASP.NET 2.0 Special Edition by Bill Evjen, Scott Hanselman, Devin Rader, Farhan Muhammad, Srinivasa Sivakumar; ISBN: 9780470041789
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional ASP.NET 2.0 and Special Edition; ISBN: 978-0-7645-7610-2; ISBN: 978-0-470-04178-9 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 13th, 2006, 01:40 PM
Registered User
 
Join Date: Feb 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Chapter 12 - Multiple Active Result Sets

I tried to run the page in Listing 12-8, and received the following error:

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

This occurred in the gvOrders_RowDataBound procedure, on the "OrderDetailReader = Command2.ExecuteReader()" line.

Does anyone have suggestions for getting this code to execute successfully? I tried renaming the command object to Command1 in the Form_Load procedure, and Command2 in the gvOrders_RowDataBound procedure. However, that doesn't make any difference.


 
Old February 15th, 2006, 10:52 AM
Registered User
 
Join Date: Feb 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Still curious... I experimented a little further, and came up with an interesting result. This is not an answer to the original question, but it may give further insight to any readers who are interested. Answers to the original question will still be greatly appreciated.

Using Listing 12-8, I changed all the SQL objects (Connection, Command, Reader, etc.) to OleDB objects. Still connecting to a SQL database (2005), I ran the page with the modified code. It now works error-free.

Why not before? Anyone have a clue?

Here is the new code which works:

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Configuration" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    ' Declaring connection here allows us to use it inside all methods
    ' of this class
    Dim DBCon As OleDbConnection

    Protected Sub Page_Load(ByVal sender As Object, _
                    ByVal e As System.EventArgs)

        Dim Command As OleDbCommand = New OleDbCommand()
        Dim OrdersReader As OleDbDataReader

        DBCon = New OleDbConnection()
        DBCon.ConnectionString = "provider=SqlOleDb;server=localhost;database=North wind;uid=sa;password=Password1;"

        Command.CommandText = _
                " SELECT TOP 5 Customers.CompanyName, Customers.ContactName, " & _
                " Orders.OrderID, Orders.OrderDate, " & _
                " Orders.RequiredDate, Orders.ShippedDate " & _
                " FROM Orders, Customers " & _
                " WHERE Orders.CustomerID = Customers.CustomerID " & _
                " ORDER BY Customers.CompanyName, Customers.ContactName "

        Command.CommandType = CommandType.Text
        Command.Connection = DBCon

        ' Opening the connection and executing the SQL query.
        DBCon.Open()
        OrdersReader = Command.ExecuteReader()

        ' Binding the Data Reader to the GridView control
        gvOrders.DataSource = OrdersReader
        gvOrders.DataBind()

        ' Closing connection after we are done processing all order records
        DBCon.Close()

    End Sub

    ' This event handler is called for each record being bound to the
    ' GridView control
    Protected Sub gvOrders_RowDataBound(ByVal sender As Object, _
                 ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)

        Dim OrderRecord As IDataRecord
        Dim lblOrderDetail As Label

        ' Retrieving the currently bound record from the Data Reader
        ' using the IDataRecord interface
        OrderRecord = CType(e.Row.DataItem, IDataRecord)

        ' Retrieving reference to the Label Control inside the current
        ' GridView row. This Label will be populated with Order Details
        lblOrderDetail = CType(e.Row.FindControl("lblOrderDetail"), Label)

        If OrderRecord Is Nothing Or lblOrderDetail Is Nothing Then
            Return
        End If

        Dim Command As OleDbCommand = New OleDbCommand()
        Dim OrderDetailReader As OleDbDataReader

        ' Creating an SQL query to retrieve details
        ' for the currently processed order
        Command.CommandText = _
                "SELECT Products.ProductName, [Order Details].UnitPrice, " & _
                " [Order Details].Quantity, [Order Details].Discount " & _
                " FROM [Order Details], Products " & _
                " WHERE [Order Details].ProductID = Products.ProductID " & _
                " AND [Order Details].OrderID = " + _
                Convert.ToString(OrderRecord("OrderID"))

        Command.CommandType = CommandType.Text

        ' Reusing the same connection object that was used in retrieving
        ' allorder records from the Orders table
        Command.Connection = DBCon

        ' Executing SQL query without passing CommandBehavior.CloseConnection
        ' as parameter to ExecuteReader. We don't want the connection
        ' to automatically close because we want to reuse it for more operations
        OrderDetailReader = Command.ExecuteReader()

        While OrderDetailReader.Read()
            ' Populating the lable control with the product name field
            lblOrderDetail.Text += OrderDetailReader(0).ToString() + "<Br>"
        End While

    End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Multiple Active Result Sets</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblCounter" Runat="server"></asp:Label>
        <br />
        <asp:GridView ID="gvOrders" Runat="server" AutoGenerateColumns="False"
                OnRowDataBound="gvOrders_RowDataBound" Width="100%">
            <Columns>
        <asp:BoundField HeaderText="Company Name"
                DataField="CompanyName"></asp:BoundField>
        <asp:BoundField HeaderText="Contact Name"
                DataField="ContactName"></asp:BoundField>
        <asp:TemplateField>
        <HeaderTemplate>
                Order Detail
        </HeaderTemplate>
        <ItemTemplate>
                <asp:Label ID="lblOrderDetail" runat="server"></asp:Label>
        </ItemTemplate>

        </asp:TemplateField>
                <asp:BoundField HeaderText="Order Date" DataField="orderdate"
                        DataFormatString="{0:d}"></asp:BoundField>
                <asp:BoundField HeaderText="Required Date" DataField="requireddate"
                        DataFormatString="{0:d}"></asp:BoundField>
                <asp:BoundField HeaderText="Shipped Date" DataField="shippeddate"
                        DataFormatString="{0:d}"></asp:BoundField>
            </Columns>
        </asp:GridView><br />
        <br />
    </div>
    </form>
</body>
</html>


 
Old February 15th, 2006, 11:42 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

On a SqlConnection, you cannot have more than one SqlDataReader executing at the same time. Not sure why that is, but it may have to do with performance. Whenever you try to do so (e.g. execute another Reader before you closed an earlier one) will lead to the error you posted.
For SQL Server, you may need to use something like MARS (multiple active result sets) to accomplish the same thing. I don't know much about MARS, so I could be very wrong, but I think this is what it's used for.

OleDb doesn't enforce this restriction, so you can have more than one DataReader on an open connection. I wouldn't be surprised if this was just an "optical issue". I think that under the hood, the OleDb dataprovider opens a second connection to the database to get the data. You can track this by looking at open connections in SQL Server.

Hope this helps a little.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old February 15th, 2006, 01:29 PM
Registered User
 
Join Date: Feb 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Very Interesting Imar, thank you. I wonder if you are right about the "under the hood" comment." I'll have to check it out.

But, i am a little unclear about your meaning when saying "For SQL Server, you may need to use something like MARS (multiple active result sets) to accomplish the same thing." Did you mean use something like MARS (including MARS)? Or, did you mean something 'like' but 'different' from MARS?

If your meaning was the latter, then I guess we have discovered an error in the author's code. The point of that section of the book is to illustrate new support in ASP.NET 2.0 for MARS using a single data connection.

If anyone else has a different experience with this code sample, please chime in.

 
Old February 15th, 2006, 02:26 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Sorry for the confusion in wording. What I meant was:

For SQL Server, you need MARS.

Check out this article: http://msdn.microsoft.com/library/de...ARSinSQL05.asp

It describes Mars and how to use it. It also supports my suspicion that for OleDb another connection is opened:
Quote:
quote:Interestingly enough, this code succeeds and seems to do what I wanted. How does it succeed if the lack of MARS seems to be a fundamental engine limitation? Upon some more inspection it becomes apparent that the SQLOLEDB driver is spawning a new connection under the covers and executing Command2 under it. This means I already have MARS, right? Not quite.
HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old April 23rd, 2006, 02:58 AM
Registered User
 
Join Date: Apr 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi I had the same problems width Listing 12-8 and wanted to tryout mdrake's example of changing to an oledb provider instead. This mendt that i had to find a connection string, and for that i used http://www.connectionstrings.com/. I looked under SQLConnection (.NET) just out of interest, and found:


Enabling MARS (multiple active result sets):

"Server=Aron1;Database=pubs;Trusted_Connection=Tru e;MultipleActiveResultSets=true"
Note! Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1

I added "MultipleActiveResultSets=true" to my connectionstring im my web.config file and then it worked . Gues i missed that part in the book ore its not there who knows.

 
Old April 23rd, 2006, 10:15 AM
Registered User
 
Join Date: Feb 2006
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Great find stubid_cat! This may be the answer to the original question. If so, it means the author left out a critical piece of the puzzle.

Imar's reply -- while interesting and informative -- was less than satisfying, because the point of the exercise was to illustrate the use of MARS in SQL 2005 using a SQLConnection object.

Thanks.


 
Old May 13th, 2006, 06:07 AM
Registered User
 
Join Date: Dec 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Has anyone got this to work without using SQL Server 2005 and Trusted_Connection=True?

I'm using a hosted SQL Server 2000 account and it will not work I've worked through all the above suggestions BUT I'm still getting the following error "There is already an open DataReader associated with this Command which must be closed first."

Can anyone offer a suggestion or point me at some links?

Thanks

Mark
 
Old May 13th, 2006, 06:25 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi there,

MARS is a new feature of SQL Server 2005, so it won't work with SQL 2000.

If you need simultaneous data streams with SQL Server 2000, simply create a new connection object, open it and use that second connection to get the rest of the data.

Does that help?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
 
Old May 13th, 2006, 06:33 AM
Registered User
 
Join Date: Dec 2003
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Imar,
Thanks! It's been driving me crazy! now at least I know the solutiuon!

Mark





Similar Threads
Thread Thread Starter Forum Replies Last Post
stored procedure with two result sets joxa83 SQL Server 2000 14 July 18th, 2007 01:03 AM
Generics chapter 12 difficult chapter i found ...? Larryz C# 2005 1 July 4th, 2007 09:40 PM
handling multiple record sets Phrozt Classic ASP Professional 1 October 12th, 2006 04:33 AM
Errors on Chapter 12 example(12.8) sonnie ASP.NET 2.0 Professional 2 June 7th, 2006 10:55 AM
Ch 12, Try It Out, p.456, Line 8 Different result VictorVictor BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 0 February 23rd, 2006 02:52 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.