Emmily Emmily - 6 months ago 10
SQL Question

Have Gridview show different columns based on button clicked

I want my gridview to show specific columns based on which button the user clicks. Right now with my below code I am getting the error message "A field or property with the name 'CaseNumber' was not found on the selected data source." Is there a setting I can change so that my select statement does not have to include every column?

Here is MasterTable.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MasterTable.aspx.cs" Inherits="WebApplication1.Table" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">

</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="mygv" runat="server" class="table table-inverse table-sm" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." EnableSortingAndPagingCallbacks="True">
<Columns>
<asp:BoundField DataField="VendorID" HeaderText="Vendor ID" SortExpression="VendorID" />
<asp:BoundField DataField="VendorName" HeaderText="Vendor Name" SortExpression="VendorName" />
<asp:BoundField DataField="OrgID" HeaderText="Org ID" SortExpression="OrgID" />
<asp:BoundField DataField="Organization" HeaderText="Organization" SortExpression="Organization" />
<asp:BoundField DataField="ProductID" HeaderText="Product ID" SortExpression="ProductID" />
<asp:BoundField DataField="ProductDescription" HeaderText="Product Description" SortExpression="ProductDescription" />
<asp:BoundField DataField="EffectiveDate" HeaderText="Effective Date" SortExpression="EffectiveDate" DataFormatString="{0:d}" HtmlEncode=false />
<asp:BoundField DataField="ProductYearStartDate" HeaderText="Product Year Start Date" SortExpression="ProductYearStartDate" DataFormatString="{0:d}" HtmlEncode=false />
<asp:BoundField DataField="OEOpen" HeaderText="OE Open" SortExpression="OEOpen" DataFormatString="{0:d}" HtmlEncode=false />
<asp:BoundField DataField="OEClose" HeaderText="OEC lose" SortExpression="OEClose" DataFormatString="{0:d}" HtmlEncode=false />
<asp:BoundField DataField="FileFeedAddDate" HeaderText="File Feed Add Date" SortExpression="FileFeedAddDate" DataFormatString="{0:d}" HtmlEncode=false />
<asp:BoundField DataField="WhichFile" HeaderText="Which File" SortExpression="WhichFile" />
<asp:BoundField DataField="ChangesAuditIndicator" HeaderText="Changes Audit Indicator" SortExpression="ChangesAuditIndicator" />
<asp:BoundField DataField="MapsIndicator" HeaderText="Maps Indicator" SortExpression="MapsIndicator" />
<asp:BoundField DataField="TestFileSentDate" HeaderText="Test File Sent Date" SortExpression="TestFileSentDate" DataFormatString="{0:d}" HtmlEncode=false />
<asp:BoundField DataField="ProdFileSentDate" HeaderText="Prod File Sent Date" SortExpression="ProdFileSentDate" DataFormatString="{0:d}" HtmlEncode=false />
<asp:BoundField DataField="GroupNumber" HeaderText="Group Number" SortExpression="GroupNumber" />
<asp:BoundField DataField="CaseNumber" HeaderText="Case Number" SortExpression="CaseNumber" />
<asp:BoundField DataField="BrokerID" HeaderText="Broker ID" SortExpression="BrokerID" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
<asp:BoundField DataField="AccType" HeaderText="Acc Type" SortExpression="AccType" />
<asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
<asp:BoundField DataField="TermedIndicator" HeaderText="Termed Indicator" SortExpression="TermedIndicator" />
</Columns>

</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ETLDataConnectionString %>" />
</div>
</form>





Here is MasterTable.aspx.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
public partial class Table : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

string vendor = "";
string sql = null;

vendor = Request.QueryString.Get("vendor");


if (vendor == "elephant")
{
sql = @"select [VendorID] , [VendorName] , [OrgID] , [Organization] , [ProductID] , [ProductTypeID] , [ProductDescription] , [EffectiveDate] , [ProductYearStartDate] ,
[OEOpen] , [OEClose] , [FileFeedAddDate] , [WhichFile] , [ChangesAuditIndicator] , [MapsIndicator] , [TestFileSentDate] , [ProdFileSentDate] , [GroupNumber] ,
[Notes] , [TermedIndicator] from [CarrierDatabase] where vendorid = 1234";
}
if (vendor == "piglet")
{
sql = @"select [VendorID] , [VendorName] , [OrgID] , [Organization] , [ProductID] , [ProductTypeID] , [ProductDescription] , [EffectiveDate] , [ProductYearStartDate] ,
[OEOpen] , [OEClose] ,[TestFileSentDate] , [ProdFileSentDate], [Notes] , [TermedIndicator] from [CarrierDatabase] where vendorid = 5678";
}


SqlDataSource1.SelectCommand = sql;

}
}
}

Answer

You should also hide the columns that you don't want to show by setting their Visible property to false:

   if (vendor == "elephant")
    {
        sql = @"select [VendorID] , [VendorName] , [OrgID] , [Organization] , [ProductID] , [ProductTypeID] , [ProductDescription] , [EffectiveDate] , [ProductYearStartDate] ,
        [OEOpen] , [OEClose] ,  [FileFeedAddDate] , [WhichFile] , [ChangesAuditIndicator] , [MapsIndicator] , [TestFileSentDate] , [ProdFileSentDate] , [GroupNumber] ,
        [Notes] , [TermedIndicator] from [CarrierDatabase] where vendorid = 1234";
        mygv.Columns[10].Visible = true;
        mygv.Columns[11].Visible = true;
        mygv.Columns[14].Visible = false;
        mygv.Columns[15].Visible = false;
        ...
    }
    if (vendor == "piglet")
    {
        sql = @"select [VendorID] , [VendorName] , [OrgID] , [Organization] , [ProductID] , [ProductTypeID] , [ProductDescription] , [EffectiveDate] , [ProductYearStartDate] ,
        [OEOpen] , [OEClose] ,[TestFileSentDate] , [ProdFileSentDate], [Notes] , [TermedIndicator] from [CarrierDatabase] where vendorid = 5678";
        mygv.Columns[10].Visible = false;
        mygv.Columns[11].Visible = false;
        mygv.Columns[14].Visible = true;
        mygv.Columns[15].Visible = true;
        ...
    }

In the example above, I also set the Visible property to true for columns that may have been hidden by a previous user selection but should be visible with the new selection.

Here is an alternative way of doing it, with boolean variables (to reduce the risk of errors due to typos in strings):

    bool isElephant = vendor == "elephant";
    bool isPiglet = vendor == "piglet";
    bool isOrange = vendor == "orange";
    ...

    if (isElephant)
    {
        ...
    }

    if (isPiglet)
    {
        ...
    }

    ...

    mygv.Columns[10].Visible = isElephant;
    mygv.Columns[11].Visible = isElephant;
    mygv.Columns[14].Visible = isPiglet;
    mygv.Columns[15].Visible = isPiglet;
    mygv.Columns[20].Visible = isElephant || isOrange;
    mygv.Columns[21].Visible = isPiglet || isOrange;
    ...

That method also ensures that the columns hidden in one case are made visible in the other. In my first code sample, one has to make sure that the columns in one if block also appear in the other if block (with the opposite value), which is quite error prone.

Comments