I get this FieldCount error when I attempt to bind a datagrid with a
dataset, not a datareader object.
The code snippet is as belows:
'PopulateForm called in Page Load event
Private Sub PopulateForm(ByVal MyOrderDetails As MyChannel.OrderDetails)
Me.lblOrderID.Text = CStr(MyOrderDetails.OrderID)
Dim customer As MyChannel.CustomersDB = New MyChannel.CustomersDB()
Dim DR As SqlClient.SqlDataReader =
customer.GetCustomerList(CInt(Me.Page.User.Identit y.Name))
Me.DDLCustomer.DataSource = DR
Me.DDLCustomer.DataValueField = "CustomerID"
Me.DDLCustomer.DataTextField = "FullName"
Me.DDLCustomer.DataBind()
DR.Close()
Me.DDLCustomer.SelectedIndex = GetDDLItemIndexByValue(Me.DDLCustomer,
CStr(MyOrderDetails.CustomerID))
Me.lblOrderDate.Text = Format(MyOrderDetails.OrderDate, "d")
Me.tbCustomerPO.Text = MyOrderDetails.CustomerPO
Me.tbDelAddLine1.Text = MyOrderDetails.DelAddLine1
Me.tbDelAddLine2.Text = MyOrderDetails.DelAddLine2
Me.tbDelAddLine3.Text = MyOrderDetails.DelAddLine3
Me.tbDelAddLine4.Text = MyOrderDetails.DelAddLine4
Me.tbShipDate.Text = Format(MyOrderDetails.ShipDate, "d")
Me.tbShipVia.Text = MyOrderDetails.ShipVia
Me.tbComments.Text = MyOrderDetails.Comment
Me.tbFreight.Text = Format(MyOrderDetails.Freight, "###0.00")
Dim Tax As MyChannel.Tax = New MyChannel.Tax()
DR = Tax.GetTaxType
Me.DDLFreightTaxType.DataSource = DR
Me.DDLFreightTaxType.DataValueField = "taxtype"
Me.DDLFreightTaxType.DataTextField = "taxcode"
Me.DDLFreightTaxType.DataBind()
DR.Close()
Me.DDLFreightTaxType.SelectedIndex =
GetDDLItemIndexByValue(Me.DDLFreightTaxType,
CStr(MyOrderDetails.FreightTaxType))
Me.lblExTaxTotal.Text = Format(MyOrderDetails.OrderTotalExTax, "###0.00")
Me.lblTotal.Text = Format(MyOrderDetails.OrderTotal, "###0.00")
Me.lblTax.Text = Format(MyOrderDetails.OrderTotal -
MyOrderDetails.OrderTotalExTax, "###0.00")
Me.dgOrderDetails.DataSource = MyOrderDetails.OrderItems.Tables(0)
Me.dgOrderDetails.DataBind()
End Sub
'MyOrderDetails class is populated as follows:
Public Function GetOrderDetails(ByVal orderID As Integer) As OrderDetails
' Create Instance of Connection and Command Object
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("C onnectionString"))
Dim myCommand As New SqlDataAdapter("OrdersDetail", myConnection)
' Mark the Command as a SPROC
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterOrderID As New SqlParameter("@OrderID", SqlDbType.Int, 4)
parameterOrderID.Value = orderID
parameterOrderID.Direction = ParameterDirection.InputOutput
myCommand.SelectCommand.Parameters.Add(parameterOr derID)
Dim parameterCustomerID As New SqlParameter("@CustomerID", SqlDbType.Int, 4)
parameterCustomerID.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterCu stomerID)
Dim parameterOrderDate As New SqlParameter("@OrderDate", SqlDbType.DateTime,
8)
parameterOrderDate.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterOr derDate)
Dim parameterShipDate As New SqlParameter("@ShipDate", SqlDbType.DateTime,
8)
parameterShipDate.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterSh ipDate)
Dim parameterOrderTotal As New SqlParameter("@OrderTotal", SqlDbType.Money,
8)
parameterOrderTotal.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterOr derTotal)
Dim parameterOrderTotalExTax As New SqlParameter("@OrderTotalExTax",
SqlDbType.Money, 8)
parameterOrderTotalExTax.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterOr derTotalExTax)
Dim parameterDelAddLine1 As New SqlParameter("@deladdline1",
SqlDbType.NVarChar, 50)
parameterDelAddLine1.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterDe lAddLine1)
Dim parameterDelAddLine2 As New SqlParameter("@deladdline2",
SqlDbType.NVarChar, 50)
parameterDelAddLine2.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterDe lAddLine2)
Dim parameterDelAddLine3 As New SqlParameter("@deladdline3",
SqlDbType.NVarChar, 50)
parameterDelAddLine3.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterDe lAddLine3)
Dim parameterDelAddLine4 As New SqlParameter("@deladdline4",
SqlDbType.NVarChar, 50)
parameterDelAddLine4.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterDe lAddLine4)
Dim parameterInvoiceNo As New SqlParameter("@invoiceno", SqlDbType.NVarChar,
8)
parameterInvoiceNo.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterIn voiceNo)
Dim parameterCustomerPO As New SqlParameter("@customerpo",
SqlDbType.NVarChar, 20)
parameterCustomerPO.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterCu stomerPO)
Dim parameterShipVia As New SqlParameter("@shipvia", SqlDbType.NVarChar, 20)
parameterShipVia.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterSh ipVia)
Dim parameterComment As New SqlParameter("@comment", SqlDbType.NVarChar,
255)
parameterComment.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterCo mment)
Dim parameterFreight As New SqlParameter("@freight", SqlDbType.Money, 8)
parameterFreight.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterFr eight)
Dim parameterTaxTypeOnFreight As New SqlParameter("@taxtypeonfreight",
SqlDbType.Int, 4)
parameterTaxTypeOnFreight.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterTa xTypeOnFreight)
Dim parameterOrderStatus As New SqlParameter("@Orderstatus", SqlDbType.Int,
4)
parameterOrderStatus.Direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parameterOr derStatus)
' Create and Fill the DataSet
Dim myDataSet As New DataSet()
myCommand.Fill(myDataSet, "OrderDetails")
' ship date is null if order doesn't exist, or belongs to a different user
If Not parameterShipDate.Value Is DBNull.Value Then
' Create and Populate OrderDetails Struct using
' Output Params from the SPROC, as well as the
' populated dataset from the SqlDataAdapter
Dim myOrderDetails As New OrderDetails()
myOrderDetails.OrderDate = CType(parameterOrderDate.Value, DateTime)
myOrderDetails.ShipDate = CType(parameterShipDate.Value, DateTime)
myOrderDetails.OrderTotal = CDec(parameterOrderTotal.Value)
myOrderDetails.OrderItems = myDataSet
myOrderDetails.OrderID = orderID
myOrderDetails.CustomerID = CInt(parameterCustomerID.Value)
myOrderDetails.DelAddLine1 = CStr(parameterDelAddLine1.Value)
myOrderDetails.DelAddLine2 = CStr(parameterDelAddLine2.Value)
myOrderDetails.DelAddLine3 = CStr(parameterDelAddLine3.Value)
myOrderDetails.DelAddLine4 = CStr(parameterDelAddLine4.Value)
myOrderDetails.InvoiceNo = CStr(parameterInvoiceNo.Value)
myOrderDetails.CustomerPO = CStr(parameterCustomerPO.Value)
myOrderDetails.ShipVia = CStr(parameterShipVia.Value)
myOrderDetails.Comment = CStr(parameterComment.Value)
myOrderDetails.Freight = CDec(parameterFreight.Value)
myOrderDetails.FreightTaxType = CInt(parameterTaxTypeOnFreight.Value)
myOrderDetails.OrderStatus = CInt(parameterOrderStatus.Value)
myOrderDetails.OrderTotalExTax = CDec(parameterOrderTotalExTax.Value)
' Return the DataSet
Return myOrderDetails
Else
Return Nothing
End If
End Function
'The OrdersDetails class is defined as follows:
Public Class OrderDetails
Public OrderDate As DateTime
Public ShipDate As DateTime
Public OrderTotal As Decimal
Public OrderItems As DataSet
Public OrderID As Integer
Public CustomerID As Integer
Public DelAddLine1 As String
Public DelAddLine2 As String
Public DelAddLine3 As String
Public DelAddLine4 As String
Public InvoiceNo As String
Public CustomerPO As String
Public ShipVia As String
Public Comment As String
Public Freight As Decimal
Public FreightTaxType As Integer
Public OrderStatus As Integer
Public OrderTotalExTax As Decimal
End Class
'the stored proc OrdersDetails is shown below for the sake completeness
CREATE Procedure OrdersDetail
(
@OrderID int output,
@CustomerID int output,
@OrderDate datetime OUTPUT,
@ShipDate datetime OUTPUT,
@OrderTotal money OUTPUT,
@OrderTotalExTax money output,
@deladdline1 nvarchar(50) output,
@deladdline2 nvarchar(50) output,
@DelAddLine3 nvarchar(50) output,
@DelAddLine4 nvarchar(50) output,
@invoiceno nvarchar(8) output,
@customerpo nvarchar(20) output,
@shipvia nvarchar(20) output,
@comment nvarchar(255) output,
@freight money output,
@taxtypeonfreight int output,
@orderstatus int output
)
AS
/* Return the order dates from the Orders
Also verifies the order exists for this customer. */
SELECT
@CustomerID = CustomerID,
@OrderDate = OrderDate,
@ShipDate = ShipDate,
@deladdline1=deladdline1,
@deladdline2=deladdline2,
@deladdline3=deladdline3,
@deladdline4=deladdline4,
@invoiceno=invoiceno,
@customerpo=customerpo,
@shipvia=shipvia,
@comment=comment,
@freight=freight,
@taxtypeonfreight=taxtypeonfreight,
@orderstatus=orderstatus
FROM
Orders
WHERE
OrderID = @OrderID
IF @@Rowcount = 1
BEGIN
/* First, return the OrderTotal out param */
select
@ordertotal=convert(money,isnull((
select
sum(b.quantity*b.unitcost*(1+c.taxrate))
from
orderdetails b inner join tax c on
b.linetaxtype=c.taxtype
where
b.orderid=a.orderid
),0)+a.freight*(1+d.taxrate)),
@ordertotalextax=convert(money,isnull((
select
sum(b.quantity*b.unitcost)
from
orderdetails b inner join tax c on
b.linetaxtype=c.taxtype
where
b.orderid=a.orderid
),0)+a.freight)
from
orders a inner join tax d on
a.taxtypeonfreight=d.taxtype
where
a.orderid=@orderid and
a.customerid=@customerid
/* Then, return the recordset of info */
SELECT
OrderDetails.OrderLineID,
Products.ProductID,
Products.ModelNumber,
OrderDetails.LineDescription,
OrderDetails.UnitCost,
OrderDetails.Quantity,
(OrderDetails.Quantity * OrderDetails.UnitCost) as ExtendedAmount,
LineTaxType
FROM
OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE
OrderID = @OrderID
END
else
-- if @orderid doesn't exist then set @orderid to 0 to signal it
set @orderid=0
GO
As the code above shows, there is no reference to a datareader object at
all. Any idea what is going on?
