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>
Chapter 12 - Multiple Active Result Sets
. Gues i missed that part in the book ore its not there who knows.