I have a simple GridView that uses 3 session variables; CoName that is captured when the user logs in and PurchaseOrderDate, and HeatNumber that are captured in text boxes (both of these are optional) as input parameters for a stored procedure on a SQLServer 2012 database. I have a CustomerLanding.aspx page that has a search button on it that, when clicked, redirects the user to a TestReportLanding.aspx page that currently has the GridView on it. Everything works as it should in this scenario.
I want to move the GridView to CustomerLanding.aspx so I can display the search results on the same page that has the search parameters and the search button. Eventually I want to use AJAX for the update, but for now I'm just putting the GridView in a different div. When I use this setup, the query only returns results when the optional parameters (PurchaseOrderDate and HeatNumber) are provided. My stored procedure only requires CoName for it's search, so I should get a lot more rows returned with the optional parameters left empty.
Being somewhat new to .NET, I'm hoping that I'm missing something simple here.
Here is my GridView code in CustomerLanding along with the SqlDataSource:
<div class="col-xs-12 col-lg-10">
<asp:Label ID="NoRecordsFound" runat="server" Visible ="false" Text="No records found."></asp:Label>
<asp:GridView ID="GridView1" runat="server" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" GridLines="Vertical" BackColor="White" ForeColor="Black" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<AlternatingRowStyle BackColor="#CCCCCC" />
<Columns>
<asp:BoundField DataField="doc" HeaderText="doc" SortExpression="doc" />
<asp:BoundField DataField="Date" HeaderText="Date" SortExpression="Date" />
<asp:BoundField DataField="Heat" HeaderText="Heat" SortExpression="Heat" />
<asp:BoundField DataField="Dir" HeaderText="Dir" SortExpression="Dir" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:iiConnectionString %>" SelectCommand="sp_test_reports" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:SessionParameter Name="CustomerName" SessionField="CoName" Type="String" />
<asp:SessionParameter DbType="Date" Name="Date" SessionField="PurchaseOrderDate" />
<asp:SessionParameter Name="Heat" SessionField="HeatNumber" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
And for what it's worth here is my stored procedure (I'm certain this is not the problem because it works when the DataGrid is on a seperate page:
CREATE PROCEDURE [dbo].[sp_test_reports]
-- Add the parameters for the stored procedure here
@CustomerName varchar(50),
@Date date,
@Heat varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @Heat IS NULL AND @Date IS NULL
SELECT DISTINCT TOP 1000 d.doc, d.Date, dl.Heat, d.Dir
FROM documents d
INNER JOIN Documents_Line_Items dl ON d.Doc = dl.Doc
WHERE d.type = 2 and d.Customer = @CustomerName;
ELSE IF @Heat IS NOT NULL AND @Date IS NULL
SELECT DISTINCT(d.doc), d.Date, dl.Heat, d.Dir
FROM documents d
INNER JOIN Documents_Line_Items dl ON d.Doc = dl.Doc
WHERE d.type = 2 AND d.Customer = @CustomerName AND dl.Heat = @Heat;
ELSE IF @Heat IS NULL AND @Date IS NOT NULL
SELECT DISTINCT(d.doc), d.Date, dl.Heat, d.Dir
FROM documents d
INNER JOIN Documents_Line_Items dl ON d.Doc = dl.Doc
WHERE d.type = 2 AND d.Customer = @CustomerName AND d.Date = @Date;
ELSE IF @Heat IS NOT NULL AND @Date IS NOT NULL
SELECT DISTINCT(d.doc), d.Date, dl.Heat, d.Dir
FROM documents d
INNER JOIN Documents_Line_Items dl ON d.Doc = dl.Doc
WHERE d.type = 2 AND d.Customer = @CustomerName AND d.Date = @Date AND dl.Heat = @Heat;
END
And here is the code behind my search button: Note that if I uncomment the Response.Redirect here, the GridView works like it should.
protected void TestReports_Click(object sender, EventArgs e)
{
Session["PurchaseOrderDate"] = DatePurchaseOrder.Text;
Session["HeatNumber"] = HeatText.Text;
GridView1.Visible = true;
MessageBox.Show(Session["CoName"].ToString());
MessageBox.Show(Session["HeatNumber"].ToString());
if (GridView1.Rows.Count == 0)
{
NoRecordsFound.Visible = true;
}
else
{
NoRecordsFound.Visible = false;
}
//Response.Redirect("TestReportLanding.aspx", false);
}
Thanks in advance for the help.
