|
Subject:
|
Chapter 12 - Multiple Active Result Sets
|
|
Posted By:
|
mdrake
|
Post Date:
|
2/13/2006 12:40:34 PM
|
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.
|
|
Reply By:
|
mdrake
|
Reply Date:
|
2/15/2006 9:52:04 AM
|
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=Northwind;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>
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/15/2006 10:42:17 AM
|
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.
|
|
Reply By:
|
mdrake
|
Reply Date:
|
2/15/2006 12:29:51 PM
|
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.
|
|
Reply By:
|
Imar
|
Reply Date:
|
2/15/2006 1:26:25 PM
|
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/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.asp
It describes Mars and how to use it. It also supports my suspicion that for OleDb another connection is opened: 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.
|
|
Reply By:
|
stubid_cat
|
Reply Date:
|
4/23/2006 2:58:35 AM
|
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=True;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.
|
|
Reply By:
|
mdrake
|
Reply Date:
|
4/23/2006 10:15:35 AM
|
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.
|
|
Reply By:
|
Anderson Mark
|
Reply Date:
|
5/13/2006 6:07:38 AM
|
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
|
|
Reply By:
|
Imar
|
Reply Date:
|
5/13/2006 6:25:11 AM
|
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
|
|
Reply By:
|
Anderson Mark
|
Reply Date:
|
5/13/2006 6:33:46 AM
|
Imar,
Thanks! It's been driving me crazy! now at least I know the solutiuon!
Mark
|
