asked on

Gridview display multiple fields in one column

How can you display the values from multiple fields in one column in a gridview? I've found examples of grouping data in a gridview, but that is always using one field. I want to show up to three values from 3 separate fields in one column in the grid. Any ideas?

Tom Beck

Here's one way:

<asp:TemplateField HeaderText="Random" HeaderStyle-HorizontalAlign="Left">
<ItemTemplate>
<asp:Label ID="lblthingsName" runat="server" Text = '<%# Eval("randomData") & ", " & Eval("sort") & ", " & Eval("id") %>' />
</ItemTemplate>
</asp:TemplateField>

I've combine data from three different columns in the database table into one column in the Gridview and added some commas between.

You could also retrieve your data from the database and put it into a new datatable. Then you can re-order it any way you like and bind the new datatable to the gridview.

Carlos Villegas

Yes, you can, see this example:

<%@ Import Namespace="System.Data" %>

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            // Test data...
            DataTable dtt = new DataTable();
            dtt.Columns.Add("MyText", typeof(string));
            dtt.Columns.Add("MyText2", typeof(string));
            dtt.Rows.Add("My record 1", "123");
            dtt.Rows.Add("My record 2", "456");
            dtt.Rows.Add("My record 3", "789");
            GridView1.DataSource = dtt;
            GridView1.DataBind();
        }
    }

</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:TemplateField HeaderText="My Column">
            <ItemTemplate>
                <asp:Label Text='<%# Eval("MyText").ToString() + " - " + Eval("MyText2").ToString() %>' runat="server" />
            </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    </form>
</body>
</html>

Open in new window

Download aspx page:
GroupFieldExample.aspx

Tom Beck

Also, you could use the ItemDataBound event to intercept the data just before it goes to the Gridview control and combine columns there, for example, if you needed to add numbers from different columns together.

Carlos Villegas

Ok tommyBoy was first in answer :)

Tom Beck

Yes @yv989c, but you created an example of method two as I was describing it. Very quick!

CtrlAltDl

You could just concatenate your fields in you query (assuming you are using one).

SELECT dateMonthField + '/' + dateDayField + '/' + dateYearField FROM tblOrderDates

Tom Beck

Good point @CtrlAltDl. I'm a little weak on SQL, but might you also want to add an alias to that so you have it to plug into the Gridview.

SELECT (dateMonthField + '/' + dateDayField + '/' + dateYearField) AS myDate FROM tblOrderDates

Then in Gridview markup:

<asp:TemplateField HeaderText="Date">
<ItemTemplate>
<asp:Label ID="lblDate" runat="server" Text = '<%# Eval("myDate") %>' />
</ItemTemplate>
</asp:TemplateField>

dodgerfan

ASKER

All of this is a great help, but I don;t think I got my original thoughts across very well. I want to display data with the gridview, where each record has sub records. Each sub record is displayed vertically in a column, with other values for each one displayed. It's hard to describe, but I've created a table in word to show what I'm looking for. I use a stored procedure to pull the required records from the database, but dispalying them well is not that easyl. Thanks for all of the help.
Sample.docx

Ramkisan Jagtap

you can achive this using nesting of gridview. Please follow the link you will find a good example.

http://www.codeproject.com/KB/aspnet/MultiNestMDGridview.aspx

Carlos Villegas

Hello dodgerfan, I did this another example, it does not use an GridView, instead I use the Table class to get the result that you want, the only problem that I see with this (as is) is that it does not implement persistence, so you will need to recreate the table if a postback occur:

<%@ Import Namespace="System.Data" %>

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dtt = new DataTable();
        dtt.Columns.Add("NameDepartment", typeof(string));
        dtt.Columns.Add("DateCreated", typeof(string));
        dtt.Columns.Add("Form", typeof(string));
        dtt.Columns.Add("FormId", typeof(int));
        dtt.Columns.Add("Status", typeof(string));
        dtt.Rows.Add("Dan Smith Engineering", new DateTime(2011, 5, 23), "Pre", 1, "Closed");
        dtt.Rows.Add("Dan Smith Engineering", new DateTime(2011, 5, 23), "Post", 2, "Closed");
        dtt.Rows.Add("Mark Jones Accounting", new DateTime(2011, 6, 1), "Pre", 10, "Closed");
        dtt.Rows.Add("Mark Jones Accounting", new DateTime(2011, 6, 1), "Post", 15, "Open");
        dtt.Rows.Add("Mark Jones Accounting", new DateTime(2011, 6, 1), "Test", 18, "Pending");

        // I'm using a DataReader to make it more DB friendly.
        using (IDataReader dr = dtt.CreateDataReader())
        {
            TableCell lastCellNameDeparment = null;
            TableCell lastCellCreatedTableCell = null;
            int innerRecordCount = 0;

            while (dr.Read())
            {
                TableRow tbRow = new TableRow();
                if (lastCellNameDeparment == null || lastCellNameDeparment.Text != dr["NameDepartment"].ToString())
                {
                    if (lastCellNameDeparment != null)
                    {
                        lastCellNameDeparment.RowSpan = innerRecordCount;
                        lastCellCreatedTableCell.RowSpan = innerRecordCount;
                        innerRecordCount = 0;
                    }

                    lastCellNameDeparment = new TableCell();
                    lastCellNameDeparment.Text = dr["NameDepartment"].ToString();
                    tbRow.Cells.Add(lastCellNameDeparment);

                    lastCellCreatedTableCell = new TableCell();
                    lastCellCreatedTableCell.Text = Convert.ToDateTime(dr["DateCreated"]).ToString("d");
                    tbRow.Cells.Add(lastCellCreatedTableCell);
                }

                TableCell cellForm = new TableCell();
                cellForm.Text = dr["Form"].ToString();
                tbRow.Cells.Add(cellForm);

                TableCell cellViewFileCopy = new TableCell();
                HyperLink myLink = new HyperLink();
                myLink.Text = dr["FormId"].ToString();
                myLink.NavigateUrl = "View.aspx?Id=" + dr["FormId"].ToString();
                cellViewFileCopy.Controls.Add(myLink);
                tbRow.Cells.Add(cellViewFileCopy);

                TableCell cellStatus = new TableCell();
                cellStatus.Text = dr["Status"].ToString();
                tbRow.Cells.Add(cellStatus);

                TableCell cellEdit = new TableCell();
                HyperLink myLink2 = new HyperLink();
                myLink2.Text = "Edit";
                myLink2.NavigateUrl = "Edit.aspx?Id=" + dr["FormId"].ToString();
                cellEdit.Controls.Add(myLink2);
                tbRow.Cells.Add(cellEdit);

                Table1.Rows.Add(tbRow);

                innerRecordCount++;
            }
            lastCellNameDeparment.RowSpan = innerRecordCount;
            lastCellCreatedTableCell.RowSpan = innerRecordCount;
        }
    }

</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Table ID="Table1" runat="server" GridLines="Both">
        <asp:TableHeaderRow>
            <asp:TableHeaderCell>
                Name & Department
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Date Created
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Form
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                View File Copy
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Status
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Edit
            </asp:TableHeaderCell>
        </asp:TableHeaderRow>
    </asp:Table>
    </form>
</body>
</html>

Open in new window

Full aspx page:
BuildTableExample.aspx

dodgerfan

ASKER

yv989c,
Thanks for the sample. It looks like what I am looking for. I had not thought of anything other than a gridview to use. I have not used the Table class yet at all. How would you do something like center the data in the cells, or use an image in the hyperlink fields? Thanks.

Carlos Villegas

Hello dodgerfan, you can use the following object properties (examples):

// TableCell.HorizontalAlign
TableCell cellStatus = new TableCell();
cellStatus.HorizontalAlign = HorizontalAlign.Center;

// HyperLink.ImageUrl
HyperLink myLink = new HyperLink();
myLink.ImageUrl = "Images/MyImage.gif";

Open in new window

Also, all the web controls has the CssClass property, that you can use to set multiple CSS (Cascading Style Sheets) classes.

dodgerfan

ASKER

yv989c,
Thanks again. This works great. How would you make the status conditional? For example, I have 6 different status's, so a different image depending on the value will display. Would if be a simple if then statement, and can you do that within a table like this? Again, thanks.

ASKER CERTIFIED SOLUTION

Carlos Villegas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dodgerfan

ASKER

yv989c:
This is absolutely great. It works just the way I was trying to get it to do. Your examples are perfect. Thanks for all of you help and patience. This has proven to be very educational with regards to some things I have not worked with before. Now all I need to do is figure out how to populate the datatable using my stored procedure.

Carlos Villegas

Glad to help buddy, hey but you dont need the data table! I'm using a DataReader in the example to be more clear about that, here another example that use a SqlDataReader:

using (SqlConnection cn = new SqlConnection("MyConnectionString"))
{
    SqlCommand cm = new SqlCommand("MyStoredProcedure", cn);
    cm.CommandType = CommandType.StoredProcedure;
    cn.Open();
    using (IDataReader dr = cm.ExecuteReader())
    {
        TableCell lastCellNameDeparment = null;
        TableCell lastCellCreatedTableCell = null;
        int innerRecordCount = 0;

        while (dr.Read())
        {
            TableRow tbRow = new TableRow();
            if (lastCellNameDeparment == null || lastCellNameDeparment.Text != dr["NameDepartment"].ToString())
            {
                if (lastCellNameDeparment != null)
                {
                    lastCellNameDeparment.RowSpan = innerRecordCount;
                    lastCellCreatedTableCell.RowSpan = innerRecordCount;
                    innerRecordCount = 0;
                }

                lastCellNameDeparment = new TableCell();
                lastCellNameDeparment.Text = dr["NameDepartment"].ToString();
                tbRow.Cells.Add(lastCellNameDeparment);

                lastCellCreatedTableCell = new TableCell();
                lastCellCreatedTableCell.Text = Convert.ToDateTime(dr["DateCreated"]).ToString("d");
                tbRow.Cells.Add(lastCellCreatedTableCell);
            }

            TableCell cellForm = new TableCell();
            cellForm.Text = dr["Form"].ToString();
            tbRow.Cells.Add(cellForm);

            TableCell cellViewFileCopy = new TableCell();
            HyperLink myLink = new HyperLink();
            myLink.Text = dr["FormId"].ToString();
            myLink.NavigateUrl = "View.aspx?Id=" + dr["FormId"].ToString();
            cellViewFileCopy.Controls.Add(myLink);
            tbRow.Cells.Add(cellViewFileCopy);

            TableCell cellStatus = new TableCell();
            cellStatus.Text = dr["Status"].ToString();
            tbRow.Cells.Add(cellStatus);

            TableCell cellEdit = new TableCell();
            HyperLink myLink2 = new HyperLink();
            myLink2.Text = "Edit";
            myLink2.NavigateUrl = "Edit.aspx?Id=" + dr["FormId"].ToString();
            cellEdit.Controls.Add(myLink2);
            tbRow.Cells.Add(cellEdit);

            Table1.Rows.Add(tbRow);

            innerRecordCount++;
        }
        lastCellNameDeparment.RowSpan = innerRecordCount;
        lastCellCreatedTableCell.RowSpan = innerRecordCount;
    }
}

Open in new window

dodgerfan

ASKER

I thought I had it but you last excerpt helped me get it right. In getting this to work with this sample code, I used example data. My stored procedure will return data with different field names. I will need to make those corrections in the code for the table, correct? Anyway, thanks again.

Carlos Villegas

Yes, just that:

dr["YourCorrectColumnName"]

Open in new window

dodgerfan

ASKER

yv989c,
I hate coming back again, but I'm now thinking about how to do paging with this scenario. The recordset that gets returned will often be large (probably 100 records at the max). I was going to use the paging available to a gridview. Any idea how to do this with the data table?

Carlos Villegas

Hello, not a problem, I will give you an idea later, I'm from my phone right now... But I don't know if I can today, I needed to travel tomorrow...

Carlos Villegas

Basically first you need to define how many groups do you want to display per page, then with a parameter in your page query string you can know your current page and then with counters you can count how many rows you need to use from your DB result, also you can do this from your sp (is better but more complicated), I will try to give you an example later.....

dodgerfan

ASKER

Ok, great. And thanks.

Carlos Villegas

Hello, this is the modified example with a basic paging logic, give it a try:

<%@ Import Namespace="System.Data" %>

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
     // How many groups you want to show per page?
    const int GroupsPerPage = 10;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dtt = new DataTable();
        dtt.Columns.Add("NameDepartment", typeof(string));
        dtt.Columns.Add("DateCreated", typeof(string));
        dtt.Columns.Add("Form", typeof(string));
        dtt.Columns.Add("FormId", typeof(int));
        dtt.Columns.Add("Status", typeof(string));

        // Duplicate sample data...
        for (int i = 0; i < 100; i++)
        {
            dtt.Rows.Add("Dan Smith Engineering", new DateTime(2011, 5, 23), "Pre" + i, 1, "Closed");
            dtt.Rows.Add("Dan Smith Engineering", new DateTime(2011, 5, 23), "Post" + i, 2, "Closed");
            dtt.Rows.Add("Mark Jones Accounting", new DateTime(2011, 6, 1), "Pre" + i, 10, "Closed");
            dtt.Rows.Add("Mark Jones Accounting", new DateTime(2011, 6, 1), "Post" + i, 15, "Open");
            dtt.Rows.Add("Mark Jones Accounting", new DateTime(2011, 6, 1), "Test" + i, 18, "Pending");
        }

        int currentPage = this.CurrentPage;
        
        // I'm using a DataReader to make it more DB friendly.
        using (IDataReader dr = dtt.CreateDataReader())
        {
            TableCell lastCellNameDeparment = null;
            TableCell lastCellCreatedTableCell = null;
            int innerRecordCount = 0;
            int groupCount = 0;
            int groupCountStart = (currentPage - 1) * GroupsPerPage;

            while (dr.Read())
            {               
                TableRow tbRow = new TableRow();
                if (lastCellNameDeparment == null || lastCellNameDeparment.Text != dr["NameDepartment"].ToString())
                {
                    groupCount++;                        
                    
                    if (lastCellNameDeparment != null)
                    {
                        lastCellNameDeparment.RowSpan = innerRecordCount;
                        lastCellCreatedTableCell.RowSpan = innerRecordCount;
                        innerRecordCount = 0;
                    }

                    if (groupCount >= GroupsPerPage * currentPage)
                        break;
                    
                    lastCellNameDeparment = new TableCell();
                    lastCellNameDeparment.Text = dr["NameDepartment"].ToString();
                    tbRow.Cells.Add(lastCellNameDeparment);

                    lastCellCreatedTableCell = new TableCell();
                    lastCellCreatedTableCell.Text = Convert.ToDateTime(dr["DateCreated"]).ToString("d");
                    tbRow.Cells.Add(lastCellCreatedTableCell);
                }
                
                if (groupCount < groupCountStart)
                    continue;
                
                TableCell cellForm = new TableCell();
                cellForm.Text = dr["Form"].ToString();
                tbRow.Cells.Add(cellForm);

                TableCell cellViewFileCopy = new TableCell();
                HyperLink myLink = new HyperLink();
                myLink.Text = dr["FormId"].ToString();
                myLink.NavigateUrl = "View.aspx?Id=" + dr["FormId"].ToString();
                cellViewFileCopy.Controls.Add(myLink);
                tbRow.Cells.Add(cellViewFileCopy);

                TableCell cellStatus = new TableCell();
                cellStatus.Text = dr["Status"].ToString();
                tbRow.Cells.Add(cellStatus);

                TableCell cellEdit = new TableCell();
                HyperLink myLink2 = new HyperLink();
                myLink2.Text = "Edit";
                myLink2.NavigateUrl = "Edit.aspx?Id=" + dr["FormId"].ToString();
                cellEdit.Controls.Add(myLink2);
                tbRow.Cells.Add(cellEdit);

                Table1.Rows.Add(tbRow);

                innerRecordCount++;
            }
            
            if (innerRecordCount > 0)
            {
                lastCellNameDeparment.RowSpan = innerRecordCount;
                lastCellCreatedTableCell.RowSpan = innerRecordCount;
            }
        }

        // Create paging controls...
        TableRow tbFootRow = new TableRow();
        TableCell cellFoot = new TableCell();
        cellFoot.ColumnSpan = 6;
        cellFoot.HorizontalAlign = HorizontalAlign.Center;
        HyperLink linkBack = new HyperLink();
        linkBack.Text = "< Previous";
        linkBack.NavigateUrl = "BuildTableExample.aspx?page=" + (currentPage - 1);
        linkBack.Enabled = currentPage - 1 > 0;
        cellFoot.Controls.Add(linkBack);
        Label lblSep = new Label();
        lblSep.Text = " | ";
        cellFoot.Controls.Add(lblSep);
        HyperLink linkNext = new HyperLink();
        linkNext.Text = "Next >";
        linkNext.NavigateUrl = "BuildTableExample.aspx?page=" + (currentPage + 1);
        cellFoot.Controls.Add(linkNext);
        tbFootRow.Controls.Add(cellFoot);
        Table1.Rows.Add(tbFootRow);
    }

    public int CurrentPage
    {
        get
        {
            int value = 0;
            int.TryParse(Request.QueryString["page"], out value);
            if (value <= 0)
                value = 1;
            return value;
        }
    }
    
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Table ID="Table1" runat="server" GridLines="Both">
        <asp:TableHeaderRow>
            <asp:TableHeaderCell>
                Name & Department
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Date Created
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Form
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                View File Copy
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Status
            </asp:TableHeaderCell>
            <asp:TableHeaderCell>
                Edit
            </asp:TableHeaderCell>
        </asp:TableHeaderRow>
    </asp:Table>
    </form>
</body>
</html>

Open in new window

I hope this help.

dodgerfan

ASKER

yv989c,
Thanks for the paging code. I have not tried it yet becuase I got in this morning and realized I had a bigger problem when trying to utilize the data returned by the stored procedure. It's not as easy as I first thought with my sample layout I attached. In the sample I showed a field called Form, with the values Pre, Post and Test. In reality Form is the name used on the display, but this is the field which will show three different values. The returned values are PreID, PostID and TestID, all integers. In the table display on the web page, that column says Form, but in each cell say Pre, Post or Test (perhaps just one, but usually at least two and sometimes all 3). If it is a PreID returned it will say Pre and be a hyperlink using the PreID returned. Same thing for Post and Test, using their id's. The status of each record is returned also (a number), called Pre_Status, Post_Status, Test_Status. The File Copy, Status and Edit fields will show up the way I displayed earlier. My problem now is figuring out how to display the Form fields properly using the code you've provided so far. Is there a way to tweak it to do this? Also, this seems like a bit much, and I;ve already closed and awrded you the points. Would it ba better idea to post this as a new problem so there is more points? Thanks again.

Carlos Villegas

Hello dodgerfan, if you still needing help with this please open a new question related to this one and post on it a real example of the resultset returned by your SP (obfuscate any sensitive data).

dodgerfan

ASKER

yv989c,
I posted a new question related to this one. I was unable to get to it today, as I was trying some different approaches. Thanks.

https://www.experts-exchange.com/questions/27386584/Display-data-from-multiple-fields-in-one-column.html