Display data from multiple fields in one column

I started trying to use a gridview, but have tried a couple of different things. I'm trying to dispaly the results of a stored procedure in a table like display, with multiple field values in one column. I've tried several things and am still trying to get it right. I'm using SQL server 2005 and VS 2008 (ASP.Net and C# code behind). I attached a word document showing what I am trying to do and the sample results of the stored procedure. It also needs to be pagable, as the returned records can be numerous. Any help is appreciated.
Sample.docx
dodgerfanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Kelvin McDanielSr. Developer/ConsultantCommented:
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
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, based on your explanation I did this example for you:
<%@ 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), 4590, 8761, null, 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 = "DataListExample2.aspx?page=" + (CurrentPage - 1);
            HyperLinkPrevious.Enabled = CurrentPage > 1;

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

    public int CountFormsAvailables(RepeaterItem item)
    {
        IDataRecord record = item.DataItem as IDataRecord;
        return (record["PreId"] != DBNull.Value ? 1 : 0) + (record["PostId"] != DBNull.Value ? 1 : 0) + (record["PendId"] != DBNull.Value ? 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;
    }
    
</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() %>'>
                        <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 runat="server" ID="PostForm" Visible='<%# Eval("PostId") != DBNull.Value %>'>
                            <tr>
                                <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='<%# Eval("PendId") != DBNull.Value %>'>
                            <tr>
                                <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>

Open in new window


The constant ItemsPerPage controls how many items are displayed per page.

Here the aspx file:
DataListExample2.aspx

I hope this puts you on the right path.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
dodgerfanAuthor Commented:
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Carlos VillegasFull Stack .NET DeveloperCommented:
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();
    }
}

Open 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.
0
 
dodgerfanAuthor Commented:
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.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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);
    }

Open in new window


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

Open in new window


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

Open 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

Open in new window


I hope this helps.
0
 
dodgerfanAuthor Commented:
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?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
I think is possible, I will go back later because right now I'm busy....
0
 
dodgerfanAuthor Commented:
No problem, thanks.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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>

Open in new window

0
 
dodgerfanAuthor Commented:
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?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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" %>

Open in new window

0
 
dodgerfanAuthor Commented:
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.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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)  %>'>

Open 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.