Back to discussion

show data in gridview in groups

Last post was 22 Feb 2008 at 12:09
asp.net , db Chicoutimi-Jonquière, Canada
  • 13 years ago

    hi all,

    I am trying to display state name and city name in gridview. My Datbase table has three fields citystate_code, city_state_name, statelink_code. my desired output in my girdview would be:

    - first column will have the state name.

    - the second column will have the city name.

    * Now here the state name should only be displayed once i.e. if there are 10 cites in a state the state name should appear only in front of the first city name.

     Regards,

    Royal

    Report abuse
  • 13 years ago

    Royal,

    Try something like:

            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowIndex > 0)
                {
                    if (e.Row.Cells[0].Text.Equals(GridView1.Rows[e.Row.RowIndex-1].Cells[0].Text))
                    {
                        e.Row.Cells[0].Text = "";
                    }
                }
            }

    I'm using a dataview here:

            private void LoadGrid()
            {
                string con = ConfigurationManager.AppSettings["NorthwindDatabase"];
                string sql = "select region, city from Suppliers where region is not null order by region";

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);

                DataView dv = new DataView(ds.Tables[0]);
                //dv.Sort = SortExpression;

                GridView1.DataSource = dv;
                GridView1.DataBind();
            }
     

    Joe 

    Report abuse
  • 13 years ago

    hi Joe,

    Acutally I am using three fields in my database : citystate_cd, citystate_nm and statelink_cd. Here I am saving cities and states. The states will have a statelink_cd as 0. whereas the cities will have corresponding citystate_cd of the state in statelink_cd.

    Now I am unable to group them.......Like you used ORDER BY clause. Coz after performing the Order By, all the states are displayed first then the cities are displayed. But I am looking for an output which will have each state followed by the cities in that state.

    Regards,

    Royal  

    Report abuse
  • 13 years ago

     Hi Royal,

    One thing you can do is just get the dataset filled with records from database then iterate the dataset as per condition required and insert into datatable in your program. after finalizing bind the datatable with grid.

    In this way you can customize your records and display your records in datagrid as per your need.
    But this is a bit slow process as the system will iterate the whole dataset. This is not good approach but in this way you can do what you want.

    Report abuse
  • 13 years ago

    Try this Royal:

    select m.citystate_nm, x.citystate_nm
    from dbo.tb_citystate_master m
    inner join
    (
        select statelink_cd, citystate_nm
        from dbo.tb_citystate_master
    )
    x on (m.citystate_cd = x.statelink_cd)
    where (m.statelink_cd = 0)
    order by m.citystate_nm, x.citystate_nm

    that should give you states with cities

    If you then add a conditioning statement to the rowbound event that says something like:

    if rowcount > 1 then ..

        if previous row cell "state name" = current row cell "state name" then ...

            set current row cell "state name"  = "".

    you should end up with a grid that has states and cities - but with only the first state listed. 

    Joe 

    Report abuse
  • 13 years ago

    hi Joe,

    As per your saying that we should compare current row( e.row.cells[].text ) with the same cell in previous row( e.row.cells[e.row.rowindex-1].cells[].text ) and if they match then the text for the current row cell should be blank( e.row.cells[].text = " " ).

    But, don't you think that this won't work. Coz this is fine (according to me) if we have two rows to comapare. But once we have set the text to blank then when we perform the same check in next row then it will have a blank cell to compare with. So it will again display the same state name.

    Regards,

    Royal

    Report abuse
  • 13 years ago

    Thats true Royal. My bad - thanks for double checking that. Smiley Face

    What I should have said is that you check the source data (previous row) with the current grid row. I did try this and it worked fine:

            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                DataView dv = (DataView)GridView1.DataSource;

                if (e.Row.RowIndex > 0)
                {
                    if (e.Row.Cells[0].Text.Equals(dv.Table.Rows[e.Row.RowIndex-1][0].ToString()))
                    {
                        e.Row.Cells[0].Text = "";
                    }
                }
            }



    There's probably a "sweeter" way of doing this.. but at least this will give you a head start.

    Joe 

    Report abuse
  • 13 years ago

    Hi Joe,

    The above code gives me the following casting error.

    Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.DataView'.

    DataView dv = (DataView)GridView1.DataSource;

    This is probably coz we are trying to bind an object of type DataView to datasource which is of type Dataset(as we have binded the datasource with dataset).

    I tried few things but it didn't work.

    Regards,

    Royal

    Report abuse
  • 13 years ago

    Hi royal, Thats probably due to the fact that I'm using a DataView for my datagrid... so:

            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                //DataView dv = (DataView)GridView1.DataSource;
                DataSet ds = (DataSet)GridView1.DataSource;

                if (e.Row.RowIndex > 0)
                {
                    //if (e.Row.Cells[0].Text.Equals(dv.Table.Rows[e.Row.RowIndex - 1][0].ToString()))
                    if (e.Row.Cells[0].Text.Equals(ds.Tables[0].Rows[e.Row.RowIndex - 1][0].ToString()))
                    {
                        e.Row.Cells[0].Text = "";
                    }
                }
            }
     

    Joe 

    Report abuse
  • 13 years ago

    hi Joe,

    Ya that thing worked. But the query doesn't give me the desired result. I tried the query in sql server query builder there it gives me the desired result. i.e. State followed by city name which falls under it.

    the query is:

    select a.citystate_nm, b.citystate_nm from tb_citystate_master a

    inner join(select citystate_nm, statelink_cd from tb_citystate_master)b on

    (a.citystate_cd = b.statelink_cd) where (a.statelink_cd=0) order by a.citystate_nm, b.citystate_nm

    But now when I try to run the same query in my application , it display only the state name .i.e. in the state column it shows state name and in city column also it shows state name. Like:

    State                            City

    New FoundLand

                                        New FoundLand

                                        New FoundLand

    I have used bound column to display the data. Both the columns have the same data field name ( citystate_nm ), is this creating a problem.

    Regards,

    Royal

    Report abuse
  • 13 years ago

     [quote]Both the columns have the same data field name ( citystate_nm ), is this creating a problem.[/quote]

    I'd say yes. Better to differentiate the names accordingly

    Joe 

    Report abuse

Post a reply

Enter your message below

Sign in or Join us (it's free).

ASP.NET forum discussion

ASP.NET tutorials

Quick links

Recent activity

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Web Development

Developer Jobs

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

Cancel
“C++ : Where friends have access to your private members.” - Gavin Russell Baker