?
Solved

Display data from multiple fields in one column

Posted on 2011-10-07
14
Medium Priority
?
394 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:dodgerfan
  • 7
  • 6
14 Comments
 
LVL 11

Expert Comment

by:Kelvin McDaniel
ID: 36935862
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
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 36936207
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
 

Author Closing Comment

by:dodgerfan
ID: 36936792
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36936836
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
 

Author Comment

by:dodgerfan
ID: 36946518
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36948358
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
 

Author Comment

by:dodgerfan
ID: 36949924
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36950025
I think is possible, I will go back later because right now I'm busy....
0
 

Author Comment

by:dodgerfan
ID: 36950106
No problem, thanks.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36955800
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
 

Author Comment

by:dodgerfan
ID: 36956733
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36956763
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
 

Author Comment

by:dodgerfan
ID: 36957193
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
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 36957312
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question