Display data from multiple fields in one column

The absolute easiest way to do thus, if you are displaying data "as-is" directly from the database, is to modify the stored procedure to concatenate the values as desired.

If you need to do something based on the data first, such such as choose an image to display and then show that image instead, then you've got two options for the GridView:
1. Use a TemplateField - this will let you format a column however you see fit... But you'll have to tell it exactly what you want it to do.
More information: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.templatefield.aspx

2. Use the GridView's RowDatabound event - this will let you hook into the row after it has received data but before that data is rendered... So you can manipulate what will actually be displayed.
More information: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowdatabound.aspx

Yes, this definitely puts me on te right path. It looks great. Why the switch to the data reader, by the way? And thanks again.

Good to know buddy.

I'm using a data reader to help you avoid the use of a intermediate data table (less memory, better performance) when you get your data from your SP.
I'm expecting that you fill the repeater in this way, example:

using (SqlConnection cn = new SqlConnection("MyConnectionString"))
{
    SqlCommand cm = new SqlCommand("MyStoredProcedure", cn);
    cm.CommandType = CommandType.StoredProcedure;
    cn.Open();
    using (IDataReader dr = cm.ExecuteReader())
    {
          myRepeater.DataSource = dr;
          myRepeater.DataBind();
    }
}

Select allOpen in new window

If I had used a DataTable instead of a DataReader, the method CountFormsAvailables would be a little different, cast to DataRowView instead IDataRecord, making it incompatible with a DataReader.

yv989c,
Just a quick question. I was able to get the code up and running today and it's working quite well. I did discover something I missed, however. With the stored procedure returning so many records, I failed to think about this problem. Some of the records have all three forms (pre, post, pending). For those, the form displays them properly. But some records do not have all of them. Often it is just a pre or just a pending. There is never a post without a pre, but there can be a pre and a post without a pending. Right now it displays records for all three for each record, and if there is not one, it displays the rest of the record. This is because for missing records, the parent table that the procedure draws from has a 0 in it (it's a default). So records could have something like this:
preID           postID pendID
1001      1909    0
1002      1900    0
0      0          9819
The hyperlinks all point to id=0. What is the best way to handle this? Should I set all of the 0's in the table to null? Right now we have this line in there: <asp:PlaceHolder runat="server" ID="PostForm" Visible='<%# Eval("PostId") != DBNull.Value %>'>. That could be a task. I have over 50,000 records. Or perhaps change the code and put an if statment in? Or put in an Or statement to look for a 0 along with the null? I'm just wondering which is the best approach. Thanks.

Hello, my advice is, in your SP use the ISNULL function in your query to convert all the nulls values of your 3 columns to 0 (so 0 will always meant that does not must display), then in your web form change the places where is comparing again DBNull, example:

    public int CountFormsAvailables(RepeaterItem item)
    {
        IDataRecord record = item.DataItem as IDataRecord;
        return (Convert.ToInt32(record["PreId"]) > 0 ? 1 : 0) + (Convert.ToInt32(record["PostId"]) > 0 ? 1 : 0) + (Convert.ToInt32(record["PendId"]) > 0 ? 1 : 0);
    }

Select allOpen in new window

<asp:PlaceHolder runat="server" ID="PostForm" Visible='<%# Convert.ToInt32(Eval("PostId")) > 0 %>'>

Select allOpen in new window

<asp:PlaceHolder runat="server" ID="PendForm" Visible='<%# Convert.ToInt32(Eval("PendId")) > 0 %>'>

Select allOpen in new window

How to use ISNULL (T-SQL):

SELECT
   a.Date,
   ISNULL(b.Id2, 0) AS TableBId2
FROM
   TableA AS a
LEFT JOIN
   TableB AS b
      ON b.Id = a.Id

Select allOpen in new window

I hope this helps.

Holy crap, thanks again. It does help. I thought the parent table defaulted to 0 on records that were emtpy, but there are many NULLS in there. So converting NULLS to 0 in the SP works great. The other code changes worked, too. However, it does not really know what to do if you have a value for PendID but 0's for the Pre and Post. There are cases where there is only a PendID. There is no PlaceHolder for PreID like there is for Post and Pend. The display still shows, but the layout is distorted. Can I do something similar to what you suggested above with the placeholder to straighten it out?

I think is possible, I will go back later because right now I'm busy....

No problem, thanks.

Hello buddy, try this:

<%@ 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">

    const int ItemsPerPage = 5;
    int itemCount = 0;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dtt = new DataTable();
            dtt.Columns.Add("Name", typeof(string));
            dtt.Columns.Add("SubmitDate", typeof(DateTime));
            dtt.Columns.Add("PreId", typeof(int));
            dtt.Columns.Add("PostId", typeof(int));
            dtt.Columns.Add("PendId", typeof(int));
            dtt.Columns.Add("PreStatus", typeof(int));
            dtt.Columns.Add("PostStatus", typeof(int));
            dtt.Columns.Add("PendStatus", typeof(int));
            dtt.Columns.Add("Description", typeof(string));

            // Sample data
            for (int i = 1; i < 8; i++)
            {
                dtt.Rows.Add("Dan Smith" + i, new DateTime(2011, 3, 23), 1, 0, 5, 12, 8, 3, "Business Trip - Japan");
                dtt.Rows.Add("Mark Jones" + i, new DateTime(2011, 4, 21), 4676, 8876, 6321, 11, 6, 4, "Vacation - Bahamas");
                dtt.Rows.Add("Mike Schmidt" + i, new DateTime(2011, 4, 22), 4766, 8900, 6543, 11, 6, 3, "Business Trip – China");
            }

            myRepeater.DataSource = dtt.CreateDataReader();
            myRepeater.DataBind();


            HyperLinkPrevious.NavigateUrl = "RepeaterExample3.aspx?page=" + (CurrentPage - 1);
            HyperLinkPrevious.Enabled = CurrentPage > 1;

            HyperLinkNext.NavigateUrl = "RepeaterExample3.aspx?page=" + (CurrentPage + 1);
        }
    }

    public int CountFormsAvailables(RepeaterItem item)
    {
        IDataRecord record = (IDataRecord)item.DataItem;
        return (Convert.ToInt32(record["PreId"]) > 0 ? 1 : 0) + (Convert.ToInt32(record["PostId"]) > 0 ? 1 : 0) + (Convert.ToInt32(record["PendId"]) > 0 ? 1 : 0);
    }

    int currentPage = 0;
    public int CurrentPage
    {
        get
        {
            if (currentPage > 0)
                return currentPage;

            int.TryParse(Request.QueryString["page"], out currentPage);
            if (currentPage <= 0)
                currentPage = 1;
            return currentPage;
        }
    }

    public bool DrawItem()
    {
        itemCount++;
        int from = (ItemsPerPage * CurrentPage) - ItemsPerPage + 1;
        int to = from + ItemsPerPage - 1;
        return itemCount >= from && itemCount <= to;
    }

    public bool DrawHeaderOnPreForm(IDataRecord record)
    {
        return Convert.ToInt32(record["PreId"]) > 0;
    }
    public bool DrawHeaderOnPostForm(IDataRecord record)
    {
        return Convert.ToInt32(record["PostId"]) > 0 && Convert.ToInt32(record["PreId"]) == 0;
    }
    public bool DrawHeaderOnPendForm(IDataRecord record)
    {
        return Convert.ToInt32(record["PendId"]) > 0 && (Convert.ToInt32(record["PreId"]) == 0 && Convert.ToInt32(record["PostId"]) == 0);
    } 
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <table rules="all" style="width: 600px;">
        <thead style="font-weight: bold;">
            <tr>
                <td>
                    Name & Department
                </td>
                <td>
                    Submit Date
                </td>
                <td>
                    Form
                </td>
                <td>
                    View File Copy
                </td>
                <td>
                    Status
                </td>
            </tr>
        </thead>
        <tbody>
            <asp:Repeater runat="server" ID="myRepeater">
                <ItemTemplate>
                    <asp:PlaceHolder runat="server" Visible='<%# DrawItem() %>'>
                        <asp:PlaceHolder runat="server" ID="PreForm" Visible='<%# Convert.ToInt32(Eval("PreId")) > 0  %>'>
                            <tr>
                                <td rowspan="<%# CountFormsAvailables(Container) %>">
                                    <%# Eval("Name") %>
                                </td>
                                <td rowspan="<%# CountFormsAvailables(Container) %>">
                                    <%# Convert.ToDateTime( Eval("SubmitDate")).ToShortDateString() %>
                                </td>
                                <td>
                                    <asp:HyperLink NavigateUrl='<%# "PreFormView.aspx?id=" + Eval("PreId") %>' Text="Pre link"
                                        runat="server" />
                                </td>
                                <td>
                                    <asp:HyperLink NavigateUrl='<%# "PreFormView2.aspx?id=" + Eval("PreId") %>' ImageUrl="Images/ViewForm.gif"
                                        runat="server" />
                                </td>
                                <td>
                                    <asp:Image ImageUrl='<%# "Images/Status/" + Eval("PreStatus") + ".gif" %>' runat="server" />
                                </td>
                            </tr>
                        </asp:PlaceHolder>
                        <asp:PlaceHolder runat="server" ID="PostForm" Visible='<%# Convert.ToInt32(Eval("PostId")) > 0  %>'>
                            <tr>
                                <asp:PlaceHolder runat="server" Visible='<%# DrawHeaderOnPostForm((IDataRecord)Container.DataItem)  %>'>
                                    <td rowspan="<%# CountFormsAvailables(Container) %>">
                                        <%# Eval("Name") %>
                                    </td>
                                    <td rowspan="<%# CountFormsAvailables(Container) %>">
                                        <%# Convert.ToDateTime( Eval("SubmitDate")).ToShortDateString() %>
                                    </td>
                                </asp:PlaceHolder>
                                <td>
                                    <asp:HyperLink NavigateUrl='<%# "PostFormView.aspx?id=" + Eval("PostId") %>' Text="Post link"
                                        runat="server" />
                                </td>
                                <td>
                                    <asp:HyperLink NavigateUrl='<%# "PostFormView2.aspx?id=" + Eval("PostId") %>' ImageUrl="Images/ViewForm.gif"
                                        runat="server" />
                                </td>
                                <td>
                                    <asp:Image ImageUrl='<%# "Images/Status/" + Eval("PostStatus") + ".gif" %>' runat="server" />
                                </td>
                            </tr>
                        </asp:PlaceHolder>
                        <asp:PlaceHolder runat="server" ID="PendForm" Visible='<%# Convert.ToInt32(Eval("PendId")) > 0  %>'>
                            <tr>
                                <asp:PlaceHolder runat="server" Visible='<%# DrawHeaderOnPendForm((IDataRecord)Container.DataItem)  %>'>
                                    <td rowspan="<%# CountFormsAvailables(Container) %>">
                                        <%# Eval("Name") %>
                                    </td>
                                    <td rowspan="<%# CountFormsAvailables(Container) %>">
                                        <%# Convert.ToDateTime( Eval("SubmitDate")).ToShortDateString() %>
                                    </td>
                                </asp:PlaceHolder>
                                <td>
                                    <asp:HyperLink NavigateUrl='<%# "PendFormView.aspx?id=" + Eval("PendId") %>' Text="Pend link"
                                        runat="server" />
                                </td>
                                <td>
                                    <asp:HyperLink NavigateUrl='<%# "PendFormView2.aspx?id=" + Eval("PendId") %>' ImageUrl="Images/ViewForm.gif"
                                        runat="server" />
                                </td>
                                <td>
                                    <asp:Image ImageUrl='<%# "Images/Status/" + Eval("PendStatus") + ".gif" %>' runat="server" />
                                </td>
                            </tr>
                        </asp:PlaceHolder>
                    </asp:PlaceHolder>
                </ItemTemplate>
            </asp:Repeater>
        </tbody>
        <tfoot>
            <tr style="font-weight: bold;">
                <td colspan="5" style="text-align: center;">
                    <asp:HyperLink ID="HyperLinkPrevious" runat="server">< Previous Page</asp:HyperLink>
                    <span>|</span>
                    <asp:HyperLink ID="HyperLinkNext" runat="server">Next Page ></asp:HyperLink>
                </td>
            </tr>
        </tfoot>
    </table>
    </form>
</body>
</html>

Select allOpen in new window

yv989c,
Thanks again for the help. I've made the changes, but when I complie I get an error. The type or namespace name 'IDataRecord' could not be found (are you missing a using directory or an assembly reference?). I've put the code in the code behind file, but the only using statement you have is the System.Data. I have a few others, but I'm not sure what this error is speaking to? I had to retype the code since my systems are not connected, but I'n gone over that a few times to ensure there are no typos. Any idea?

Hello, be sure to import that namespace in your aspx page too, put this line on the top of your aspx file

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

Select allOpen in new window

yv989c,
It looks like this is perfect. The display page looks great. Your help has been invaluable. I'm still not clear why I had to put the Import statement on he aspx page when it was already a using statement in the code behind. No big deal. I'm in the midst of rebuilding an old ASP site (built with VB6 .dlls and ASP pages and HTML) into a new, updated .net version. It's been a pain, but I'm learning a lot about .net along the way. I already had some experience with .Net, but there are still areas I'm not well versed in. Keep a look out for more questions in you are so inclined. My timetable for this project is such that I can't always spend too much time trying to figure things out on my own, so I go looking for an answer up here. Thanks again for the help.

Glad to have been of help.

About that error, is raised because in this new example i'm using the IDataRecord interface on the aspx side for some method calls, example:

<asp:PlaceHolder runat="server" Visible='<%# DrawHeaderOnPostForm((IDataRecord)Container.DataItem)  %>'>

Select allOpen in new window

All the code on the page side (not code behind) is compiled at run time by aspnet, this process is transparent to you, because of that aspnet need to know this namespace too, you do that by declaring it on the top of the page, otherwise aspnet will don't know what IDataRecord meant.

Have a great day, and good luck with your project, i'm still have to maintain some VB6 apps and I hate that :p