I have a Gridview in a C# ASPX Page called Gridview1 that pulls a Table from a SQL Server and displays it on PageLoad.

There are 10 columns in the Gridview and I need all the distinct values in column 7 which is Code

Code

A   
C
D
A
A
D
B
E
R
A
A
C
B

Basically I need some kind of structure, list or even a datatable to get all the distinct values from the Column "Code". From the example above it would be a list or datatable with 6 entries since there are 6 unique codes in the Gridview column.

Distinct A B C D E R

Any ideas how this can be implemented?

CodePudding user response：

Well, ALWAYS but always try to do such code against the data source and NOT the web page HTML (be it a list view, or grid view, repeater or whatever). The web page is for display of the data, not data base stuff.

Now, I suppose in this case, we could operate against the grid, but it usually a WHOLE lot easier to operate against the data.

So, say this grid:

     <div style="float:left;width:40%">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
            CssClass="table table-hover table-striped" 
            DataKeyNames="ID">
        <Columns>
            <asp:BoundField DataField="FirstName"   HeaderText="FirstName"   />
            <asp:BoundField DataField="LastName"    HeaderText="LastName"    />
            <asp:BoundField DataField="City"        HeaderText="City"        />
            <asp:BoundField DataField="HotelName"   HeaderText="HotelName"   />
            <asp:BoundField DataField="Description" HeaderText="Description" />
            <asp:TemplateField ItemStyle-HorizontalAlign="Center">
                <ItemTemplate>
                     <asp:Button ID="cmdDel" runat="server" 
                        Text="Delete" 
                        CssClass="btn"
                        onclick="cmdDel_Click"
                        onclientClick="return confirm('really delete this?');"/>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    </div>
    <div style="float:left;margin-left:40px">
        <h4>City list</h4>
        <asp:ListBox ID="ListBox1" runat="server"
            DataTextField="City"
            DataValueField="City" Width="163px" Height="159px"
            ></asp:ListBox>
    </div>
  </div>

So, right next to the grid, I have a list box we will list out each seperate city.

So, code to load is this:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid()
    {
        DataTable rstData = MyRst("SELECT * FROM tblHotelsA ORDER BY HotelName");
        GridView1.DataSource = rstData;
        GridView1.DataBind();

        // now fill out a list box of each city
        DataTable rstCity = new DataTable();
        rstCity = rstData.DefaultView.ToTable(true, "City");
        rstCity.DefaultView.Sort = "City";

        ListBox1.DataSource = rstCity;
        ListBox1.DataBind();
    }

And we get this:

So, NOTE how we went to the data source - NOT the grid (the grid is not a database and in most cases we should not think of the grid as such).

However, we could also pull the values out of the grid (can't see why we would do this).

So, lets drop in listbox 2, and add this code that operates against the grid:

our 2nd listbox - this time NOT data bound.

So, this:

    <div style="float:left;margin-left:40px">
        <h4>City list 2nd example</h4>
        <asp:ListBox ID="ListBox2" runat="server"
            DataTextField="Text"
            DataValueField="Value"
            Width="163px" Height="159px"
            ></asp:ListBox>
    </div>

And say a button click or whatever - we run this code to fill out 2nd listbox from the data grid.

FYI: If you using templated fields, then you have to use findcontrol to get the control. If you using data fields, then you use .cells[] array/collection.

but, CAUTION, since empty cells will render as a non breaking space &nsb;

So, we convert from html, just to be save, and we have this code:

   protected void Button1_Click1(object sender, EventArgs e)
    {
        List<string> CityList = new List<string>();
        foreach (GridViewRow gRow in GridView1.Rows)
        {
            string sCity = Server.HtmlDecode(gRow.Cells[2].Text);
            if (!CityList.Contains(sCity))
                CityList.Add(sCity);
        }
        // display our list in 2nd listbox
        CityList.Sort();
        foreach (string sCity in CityList)
        {
            ListBox2.Items.Add(new ListItem(sCity, sCity));
        }
    }

So, now we have this:

So try to operatee against the data. I mean, we could even say get a list of distinct citys wiht a query, say this:

 string strSQL = "SELECT City from tblHotels GROUP BY City";
 Datatable rstCity = MyRst(strSQL);
 ListBox1.DataSource = rstCity;
 ListBox1.DataBind();

So, in most cases??

Much less effort and better to get this data from the data source, and NOT go to the GV - as it is a display and render system - not a data base, nor is it a data source.

Now, in above, I used a helper function MyRst. All that does is return a table based on sql - (became VERY tired very fast having to type that type of code over and over). So that was this:

DataTable MyRst(string strSQL)
{
    DataTable rstData = new DataTable();
    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
    {
        using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
        {
            conn.Open();
            rstData.Load(cmdSQL.ExecuteReader());
        }
    }
    return rstData;
}