Displaying data in two columns

peterh · January 25th, 2008, 03:00 PM

Hey, I'm trying to display data from 2 database columns in 4 columns without duplicating or repeating data in VB.NET using a SQL stored procedure.
Any help is greatly appreciated.

i.e.

Column 1 Column 2 Column 3 Column 4
===== ===== ===== =====

Image 1 Record 1 Image 2 Record 2
Image 3 Record 3 Image 4 Record 4
Image 5 Record 5 Image 6 Record 6

Peter Hansen
Software Developer
AtHomeNet, Inc.
[email protected]

planoie · January 25th, 2008, 03:43 PM

What criteria can you offer that will dictate what column should contain what data? I.e. how do we know that the value from the single image column should be in column 1 or column 3? From the sample it appears to be a simple even/odd type behavior.

I don't think that SQL is the correct place for this. You are trying to use the data domain to affect a result intended for the presentation domain. There is likely a much more appropriate solution.

There are ASP.NET server controls that permit a fairly good amount of control over the emitted HTML. Alternatively you could use an even simpler control (HtmlTable) to construct the output exact as you describe. From experience I would suggest that it would be far easier to get the desired the result with some presentation coding instead of trying to manipulate the data in SQL. Plus you get the benefit of having the display logic in the appropriate place.

-Peter

peterh · January 25th, 2008, 03:49 PM

Perhaps I worded my problem wrong.
I am simply using a stored procedure to access the data, and then displaying it on a page.
I'm not attempting to do anything in SQL other than get my data.
I apologize for the confusion.

Peter Hansen
Software Developer
AtHomeNet, Inc.
[email protected]

peterh · January 25th, 2008, 03:54 PM

This is essentially what I'm trying to do.

http://i37.photobucket.com/albums/e62/petemosh/1-2.jpg

Where the images and text are both pulled from the database.

Peter Hansen
Software Developer
AtHomeNet, Inc.
[email protected]

planoie · January 25th, 2008, 04:00 PM

Let me ask a different way.

In your result data the value in column 1 and 3 come from the same source column. Likewise, output columns 2 and 4 come from a different single source column. And you are were asking how to do this with a stored procedure, correct?

-Peter

peterh · January 25th, 2008, 04:04 PM

No, I fear that I shouldn't have even mentioned the stored procedure!
I am wondering how to create+fill an html table with the data, without any duplicates or repeats, I am having no trouble accessing the data, just displaying it properly.

For example, I don't want the first row in columns 1 and 3(and 2 and 4 respectively) to display the same record/image.

I'm sorry if I've been confusing you by not wording my question properly.

Peter Hansen
Software Developer
AtHomeNet, Inc.
[email protected]

planoie · January 25th, 2008, 05:10 PM

Creating an HTML table is fairly straight forward.

Start with the HtmlTable class, then add HtmlTableRow instances to the table's Rows collection. Similarly, create and add instances of the HtmlTableCell class to the row's cells collection. Add the HtmlTable class to a controls collection somewhere and you'll get the table in the page. You can place an instance of the table directly in the page using the markup (<asp:table ...>) so you can just access it directly by its ID instead of creating it and adding it to a controls collection.

Then you simply need to loop over your data source (data reader, data table, etc) and do the right logic to add two pairs of cells into each row. It really comes down to checking your interval and creating a new row instance before you add the cells. This is easy to do with a modulus operation. It's also a good idea to fill out the possible missing cells when you have a record count that doesn't completely fill the table.

In psedocode:

interval = 2 //in your case
rowCount = 0
for each row returned
   if rowCount modulus interval == 0 //0,2,4,etc
      myRow = new row
      myTable.Rows.Add(myRow)
   /if
   myCell = new cell
   myImage = new image
   //set image props from data
   myCell.Controls.Add(myImage)
   myRow.Cells.Add(myCell)
   myCell = new cell
   myCell = cell text from data
   myRow.Cells.Add(myCell)
   rowCount++
next row
while rowCount modulus interval != 0
   myRow.Cells.Add(new cell)
next

-Peter

Bob Bedell · January 25th, 2008, 05:50 PM

That kind of flow layout, in which you have a multicolumn table where each cell is a seperate record, is also often accomplished using a DataList. Both the image field and the text field for each record would appear in the same table cell. Here's an MSDN example demonstrating what I mean. Just paste it into a Web Form, and select the following values from the drop down lists at the bottom: Repeat Direction = Horizontal, Repeat Layout = Table, Repeat Columns = 2. Should give you the layout you're looking for.

Code:

<%@ Page Language="C#"  %>
<%@ Import Namespace="System.Data" %>

<html>
<head id="Head1" runat="server">
    <title>DataList Example</title>
    <script runat="server">

    ICollection CreateDataSource()
    {

        // Create sample data for the DataList control.
        DataTable dt = new DataTable();
        DataRow dr;

        // Define the columns of the table.
        dt.Columns.Add(new DataColumn("IntegerValue", typeof(Int32)));
        dt.Columns.Add(new DataColumn("StringValue", typeof(String)));
        dt.Columns.Add(new DataColumn("CurrencyValue", typeof(double)));
        dt.Columns.Add(new DataColumn("ImageValue", typeof(String)));

        // Populate the table with sample values.
        for (int i = 0; i < 9; i++)
        {
            dr = dt.NewRow();

            dr[0] = i;
            dr[1] = "Description for item " + i.ToString();
            dr[2] = 1.23 * (i + 1);
            dr[3] = "Image" + i.ToString() + ".jpg";

            dt.Rows.Add(dr);
        }

        DataView dv = new DataView(dt);
        return dv;
    }


    void Page_Load(Object sender, EventArgs e)
    {

        // Load sample data only once, when the page is first loaded.
        if (!IsPostBack)
        {
            ItemsList.DataSource = CreateDataSource();
            ItemsList.DataBind();
        }

    }

    public void Button_Click(Object sender, EventArgs e)
    {

        // Set the repeat direction based on the selected value of the
        // DirectionList DropDownList control.
        ItemsList.RepeatDirection =
            (RepeatDirection)DirectionList.SelectedIndex;

        // Set the repeat layout based on the selected value of the
        // LayoutList DropDownList control.
        ItemsList.RepeatLayout = (RepeatLayout)LayoutList.SelectedIndex;

        // Set the number of columns to display based on the selected
        // value of the ColumnsList DropDownList control.
        ItemsList.RepeatColumns = ColumnsList.SelectedIndex;

        // Show or hide the gridlines based on the value of the
        // ShowBorderCheckBox property. Note that gridlines are displayed
        // only if the RepeatLayout property is set to Table.
        if ((ShowBorderCheckBox.Checked)
            && (ItemsList.RepeatLayout == RepeatLayout.Table))
        {
            ItemsList.BorderWidth = Unit.Pixel(1);
            ItemsList.GridLines = GridLines.Both;
        }
        else
        {
            ItemsList.BorderWidth = Unit.Pixel(0);
            ItemsList.GridLines = GridLines.None;
        }
    }
    </script>
</head>
<body>

   <form id="form1" runat="server">

      <h3>DataList Example</h3>

      <asp:DataList id="ItemsList"
           BorderColor="black"
           CellPadding="5"
           CellSpacing="5"
           RepeatDirection="Vertical"
           RepeatLayout="Table"
           RepeatColumns="0"
           BorderWidth="0"
           runat="server">

         <HeaderStyle BackColor="#aaaadd">
         </HeaderStyle>

         <AlternatingItemStyle BackColor="Gainsboro">
         </AlternatingItemStyle>

         <HeaderTemplate>

            List of items

         </HeaderTemplate>

         <ItemTemplate>

            Description: <br />
            <%# DataBinder.Eval(Container.DataItem, "StringValue") %>

            <br />

            Price: <%# DataBinder.Eval(Container.DataItem, "CurrencyValue", "{0:c}") %>

            <br />

            <asp:Image id="ProductImage"
                 AlternatingText='<%# DataBinder.Eval(Container.DataItem, "StringValue") %>'
                 ImageUrl='<%# DataBinder.Eval(Container.DataItem, "ImageValue") %>'
                 runat="server"/>

         </ItemTemplate>

      </asp:DataList>



      <table cellpadding="5">

         <tr>

            <th>

               Repeat direction:

            </th>

            <th>

               Repeat layout:

            </th>

            <th>

               Repeat columns:

            </th>

            <th>

               <asp:CheckBox id="ShowBorderCheckBox"
                    Text="Show border"
                    Checked="False" 
                    runat="server" />

            </th>

         </tr>

         <tr>

            <td>

               <asp:DropDownList id="DirectionList" 
                    runat="server">

                  <asp:ListItem>Horizontal</asp:ListItem>
                  <asp:ListItem Selected="True">Vertical</asp:ListItem>

               </asp:DropDownList>

            </td>

            <td>

               <asp:DropDownList id="LayoutList" 
                    runat="server">

                  <asp:ListItem Selected="True">Table</asp:ListItem>
                  <asp:ListItem>Flow</asp:ListItem>

               </asp:DropDownList>

            </td>

            <td>

               <asp:DropDownList id="ColumnsList" 
                    runat="server">

                  <asp:ListItem Selected="True">0</asp:ListItem>
                  <asp:ListItem>1</asp:ListItem>
                  <asp:ListItem>2</asp:ListItem>
                  <asp:ListItem>3</asp:ListItem>
                  <asp:ListItem>4</asp:ListItem>
                  <asp:ListItem>5</asp:ListItem>

               </asp:DropDownList>

            </td>

            <td>

               &nbsp;

            </td>


         </tr>

      </table>     

      <asp:LinkButton id="RefreshButton" 
           Text="Refresh DataList" 
           OnClick="Button_Click" 
           runat="server"/>

   </form>

</body>
</html>

Best,

Bob

Bob Bedell · January 25th, 2008, 10:58 PM

If you have version 3.5 of the framework installed, you could also get what you want with the new ListView control. For lack of something better to call it, lets call the layout of the photobucket.jpg you linked to 'tiled layout with horizontal flow' (the table cells are bound left to right, then down). Here's a .NET 3.5 ListView control that renders 'tiled layout with horizontal flow' using 2 columns:

Code:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>

<html>
<head runat="server">
<title>ListView Tiled Layout with Horizontal Flow</title>
<script runat="server">
    ICollection CreateDataSource()
    {   
        DataTable dt = new DataTable();
        DataRow dr;

        dt.Columns.Add(new DataColumn("DescriptionValue", typeof(String)));
        dt.Columns.Add(new DataColumn("ImageValue", typeof(String)));

        for (int i = 0; i < 9; i++)
        {
            dr = dt.NewRow();

            dr[0] = "Description for item " + i.ToString();
            dr[1] = "Image" + i.ToString() + ".jpg";

            dt.Rows.Add(dr);
        }

        DataView dv = new DataView(dt);
        return dv;
    }


    void Page_Load(Object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            MyListView.DataSource = CreateDataSource();
            MyListView.DataBind();
        }
    }
</script>
</head>
<body>
    <form id="form1" runat="server">
     <asp:ListView ID="MyListView" GroupItemCount="2" runat="server">
        <LayoutTemplate>
          <table cellpadding="2" width="400px" runat="server" id="tblDataItems">
            <tr runat="server" id="groupPlaceholder" />
          </table>
        </LayoutTemplate>
        <GroupTemplate>
          <tr runat="server" id="DataItemRow">
            <td runat="server" id="itemPlaceholder" />
          </tr>
        </GroupTemplate>
        <ItemTemplate>
          <td id="Td1" align="center" style="width:200px" runat="server">
            <asp:Image ID="AnImage" runat="server"
               ImageUrl='<%# "~/images/thumbnails/" + Eval("ImageValue") %>' />&nbsp&nbsp
            <asp:Label ID="DescriptionLabel" runat="server" Text='<%# Eval("DescriptionValue")%>' />
          </td>
        </ItemTemplate>

      </asp:ListView>
    </form>
</body>
</html>

HTH,

Bob

Bob Bedell · January 26th, 2008, 12:53 AM

ok, maybe I shouldn't use the term 'flow' since it has a different meaning in web page layout - how 'bout just 'horizontal direction'.