weird gridview problem in asp.net

Question

Hello everyone - I have this weird gridview problem in .net, I haven't found what can be the issue so far. Here is what is it about: I have a stored procedure which is consumed by sqldatareader. Then I do have reader read all the values and display from the stored procedure. Problem is happening now. When I am entering some specific values for parameters, everything is rendered fine to the screen. For example, I have dates and min/max amount and the gridview is populated correctly. When I am including one more parameter, lets say customer email, the reader says that is has rows, but the gridview stays blank:

SqlDataReader rdrAdvancedReport = commAdvanced.ExecuteReader();
            while (rdrAdvancedReport.Read())
            {
                gvResults.DataSource = rdrAdvancedReport;
                gvResults.DataBind();
            }
            rdrAdvancedReport.Close();

What is more "interesting" is that I have another loop once the gridview loop is done, to give me the total amount for all the orders, and the label is populated correctly.

  if (rdrTotal.HasRows)
                {
                    while (rdrTotal.Read())
                    {
                        orderTotalSum = orderTotalSum + Convert.ToDouble(rdrTotal["OrderTotal"]);

Any idea guys what can be the issue?

Here is the stored procedure too:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE SearchCust
(
@StartTime datetime = null,
@EndTime datetime = null,
@CustomerEmail nvarchar(255) = null,    
@Username nvarchar(255) = null,
@CustomerName nvarchar(255) = null, 
@MinimumOrderAmount decimal = null, 
@MaximumOrderAmount decimal = null, 
)
 AS
BEGIN
SET NOCOUNT ON

SELECT DISTINCT o.OrderID, o.OrderTotal, o.ShippingCountry, n.Name AS OrderStatus, p.Name AS PaymentStatus FROM Order o
JOIN OrderStatus n ON o.OrderStatusID = n.OrderStatusID
JOIN PaymentStatus p ON o.OrderStatusID = p.PaymentStatusID 
JOIN Customer c ON o.CustomerID = c.CustomerID
JOIN OrderProduct op ON o.OrderID = op.OrderID
JOIN ProductValue pv ON op.ProductVariantID = pv.ProductVariantId
WHERE (o.CreatedOn > @StartTime OR @StartTime IS NULL)
AND (o.CreatedOn < @EndTime OR @EndTime IS NULL)
AND (o.ShippingEmail = @CustomerEmail OR @CustomerEmail IS NULL)    
AND (c.Username = @Username OR @Username IS NULL)
AND (o.BillingFirstName + ' ' + o.BillingLastName = @CustomerName OR @CustomerName IS NULL)
AND (o.ShippingFirstName + ' ' + o.ShippingLastName = @CustomerName OR @CustomerName IS NULL)   
AND (o.OrderTotal > @MinimumOrderAmount or @MinimumOrderAmount IS NULL)
AND (o.OrderTotal < @MaximumOrderAmount OR @MaximumOrderAmount IS NULL) 
ORDER BY o.OrderID
END

Thanks in advance

Answer 1

You can only read a SqlDataReader once.

After you call Read() through all of the returned rows, the reader is done; it cannot go back and read the rows again.

Instead, you should probably use a SqlDataAdapter to populate a DataTable.

Answer 2

You shouldn't databind your grid inside a while loop.

You're currently databinding it again for each row, which will result in unexpected behavior.

Answer 3

try this instead...

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand commAdvanced= new SqlCommand(commandString);
conn.Open();
commAdvanced.Connection = conn;
SqlDataReader rdrAdvancedReport = commAdvanced.ExecuteReader(CommandBehavior.CloseConnection);
gvResults.DataSource = rdrAdvancedReport;
gvResults.DataBind();

Since you are databinding it to a gridview, you don't have to loop through the result set first.