• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • Last Modified:

Display query results (tile) down the page

I have a web site done in ASP.NET with VB.NET.  We have a good number of product details in our database.  When the Product_list page for a certain category of products is loaded (page_load), I need to display a thumbnail image along with product name, short description, and price for each of the products in the given category.  All I'm getting now is the first record returned.  Maybe I'm just burnt out on this project, but I need help with the code.  I think I need to do a "For each . . . next" in the page_load. The code for the page_load event is in the snippet.  What I have to  display it on the page is below.

<form id="form1" runat="server">
                <div>
                <!-- <table width="612" border="0" cellpadding="0" cellspacing="0"> -->
                <asp:Image ID="imgSml" runat="server" /><br />
                <asp:Label ID="lblProduct" runat="server" /><br />
                <asp:Label ID="lblShort_descr" runat="server" /><br />
                <asp:Label ID="lblPrice" runat="server" /><!-- &nbsp;<asp:Label ID="lblSalePrice" runat="server" /> -->
                </div>
                </form>
Dim strConn1 As String = (ConfigurationManager.ConnectionStrings("omahavaccineConnectionString").ToString)
            Dim GetProductBasics As String = "SELECT product.sml_img as sml_image, product.sku as sku, product_main.product_name as product, product.product_name as short_descr, product.price as price, product.sale_price AS sale_price, product.special_start_date AS start_date, product.special_end_date AS end_date, Categorization.categoryID AS id FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id INNER JOIN Categorization ON product.sku = Categorization.sku WHERE (product.sku NOT LIKE '%-main') AND (Categorization.categoryID = " & catID & ") ORDER BY product.name"
            Dim MyConn1 As New SqlConnection(strConn1)
            Dim CmdSDR As New SqlCommand(GetProductBasics, MyConn1)
            Dim myReader1 As SqlDataReader
            MyConn1.Open()
            myReader1 = CmdSDR.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
            Do While myReader1.Read
                If Not IsDBNull(myReader1.Item("sml_image")) Then
                    'Dim SmlImg As String = myReader1.GetString(myReader1.GetOrdinal("sml_img"))
                    imgSml.ImageUrl = "new_images/small/" & myReader1.GetString(myReader1.GetOrdinal("sml_image")) & ".jpg"
                End If
                If Not IsDBNull(myReader1.Item("product")) Then
                    lblProduct.Text = myReader1.GetString(myReader1.GetOrdinal("product"))
                End If
                If Not IsDBNull(myReader1.Item("short_descr")) Then
                    lblShort_descr.Text = myReader1.GetString(myReader1.GetOrdinal("short_descr"))
                End If
                Dim Price As Decimal = "0.00"
                If Not IsDBNull(myReader1.Item("price")) Then
                    Price = myReader1.GetString(myReader1.GetOrdinal("price"))
                    lblPrice.Text = "$" & Price
                End If
                Dim SalePrice As Double = "0.00"
                If Not IsDBNull(myReader1.Item("sale_price")) Then
                    SalePrice = myReader1.GetDouble(myReader1.GetOrdinal("sale_price"))
                End If
                Dim StartDate As String = ""
                If Not IsDBNull(myReader1.Item("start_date")) Then
                    StartDate = myReader1.GetString(myReader1.GetOrdinal("start_date"))
                End If
                Dim EndDate As String = ""
                If Not IsDBNull(myReader1.Item("end_date")) Then
                    EndDate = myReader1.GetString(myReader1.GetOrdinal("end_date"))
                End If
                Dim myTime As String = System.DateTime.Now.ToString("yyyyMMddHHmmss")
                Dim Cost As Decimal = "0.00"
                If myTime > StartDate And myTime < EndDate Then
                    lblPrice.Text = "<font color=black>reg. price $" & Price & "</font>&nbsp; &nbsp;"
                    lblSalePrice.Text = "SALE PRICE $" & SalePrice
                    Session("ItemCost") = SalePrice
                Else
                    Session("ItemCost") = Price
                End If
            Loop
           
            'Close the Reader
            myReader1.Close()
            MyConn1.Close()
            MyConn1.Close()

Open in new window

0
OVC-it-guy
Asked:
OVC-it-guy
  • 13
  • 12
1 Solution
 
arhameCommented:
I think you're sort of re-inventing the wheel.  Using a sqldatasource, attached to a datalist would give you the exact same results.  You can put HTML in the datalist to get it to produce the format you're wanting.
0
 
OVC-it-guyAuthor Commented:
Re-inventing the wheel?  Naaa, just switching from asp:DataGrid and drew a blank.  Let me give this a try.
0
 
arhameCommented:
I just meant doing something manually when they have controls that do the exact same thing.  I think, from looking at your code it's doing what you tell it to - it's just changing the labels each time a new record comes.  You have nothing that tells it to create a new label for each product it pulls back.  So if you have 4 products, it's going to go through each product and then override the text you had previously set on it.  This is why a Datalist would make your life quite a bit easier.  However if you're dead set on doing it from code behind, for each product it returns, you'll need to add a new image, and new labels for each one.  To add a new control from code behind to a panel or whatever do something like this the code attached.  That way you have a label and image for each result.

Hope that makes sense.


Do While myReader1.Read
  If Not IsDBNull(myReader1.Item("product")) Then
   Dim lbl As New Label()
   lbl.Text = myReader1.GetString(myReader1.GetOrdinal("product"))
    Panel1.Controls.Add(lbl)
  End If

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
OVC-it-guyAuthor Commented:
Ok, your second suggestion came in while I was getting a handles on the first.  It seemed easier (I'm not dead set, but management sure is), since I already had the code behind going on.  I got all the images, product names, short descriptions and prices to show up and tile, but they are one right after the other (like a really long string).  I just need to insert some HTML code into my label.text's so it will format right.
0
 
arhameCommented:
Ah, okay - you'll need to switch the order in which you assign text, and when you add it to the panel, and then also add a </br> to the text.  So using the previous example:
If Not IsDBNull(myReader1.Item("product")) Then
   Dim lbl As New Label()   
   Panel1.Controls.Add(lbl)
   lbl.Text = myReader1.GetString(myReader1.GetOrdinal("product")) & "<br />"
End If

Open in new window

0
 
OVC-it-guyAuthor Commented:
Yep, got that already.  The products are tiling down the page, but just one in the center of the page.  See file (image).  The page will fit maybe three products in a row across (however many will fit).  I need three in the first row then three in the next row and so on, until I run out of products for that category.
tiling-products.jpg
0
 
arhameCommented:
Hmmmmmmm.  Not sure how you'd accomplish that from the way you're doing it.  You'd have to almost put each product in it's own panel (that's created dynamically as well) and count how many panels are added, at three panels created, insert a <br />

You really could do this whole setup in about 10 minutes using a Datalist.

If you were using a datalist you could just set the columns attribute to 3, and it would repeat three items across.  I would highly suggest cutting your losses on this one and learning the Datalist.  It's a very usefull tool and will make doing this same thing on different pages so much easier for you in the future.

Example:

I just think spending an extra 30min to redo this with this control is much better for you long term.
<asp:DataList ID="DataList1" RepeatColumns="3" runat="server" DataKeyField="ActiveID" DataSourceID="SqlDataSource1">
        <ItemTemplate>
            <asp:Image ID="Image1" runat="server" ImageUrl='new_images/small/<%# Eval("sml_image") %>' /><br />
            <asp:Label ID="lblshort_descr" runat="server" Text='<%# Eval("short_descr") %>'></asp:Label><br />
'etc etc etc for each label
            Active:
            <asp:Label ID="ActiveLabel" runat="server" Text='<%# Eval("Active") %>'></asp:Label><br />
            <br />
        </ItemTemplate>
    </asp:DataList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Yours%>"
        SelectCommand="SELECT product.sml_img as sml_image, product.sku as sku, product_main.product_name as product, product.product_name as short_descr, product.price as price, product.sale_price AS sale_price, product.special_start_date AS start_date, product.special_end_date AS end_date, Categorization.categoryID AS id FROM product INNER JOIN product_main ON product.associated_master_product_id = product_main.id INNER JOIN Categorization ON product.sku = Categorization.sku WHERE (product.sku NOT LIKE '%-main') AND (Categorization.categoryID = " & catID & ") ORDER BY product.name">
    </asp:SqlDataSource>

Open in new window

0
 
arhameCommented:
Oops, take out the lines:

Active:
<Asp:Label - etc etc

It was part of my own code I use one in that I forgot to delete while adjusting it to work with your code.
0
 
OVC-it-guyAuthor Commented:
Well, I did start down the DataList path . . . I still have what code I did when I interrupted that in favor of this.  Yes, I keep wanting to put the products into a table, but each product ( <td> ) will just string the row out into a very wide page.  I can't think of any other way (darn it, what does Amazon use?!?).  So, I'm going to shift gears and go with the DataList.  By the way, I also want to make each image a hyperlink.  Do I just wrap your <asp:Image ID="Image1" runat="server" ImageUrl='new_images/small/<%# Eval("sml_image") %>' /> with <a href=" '></a>?
0
 
arhameCommented:
Yeah, inside the datalist you can just put HTML in directly (very nice) so wrapping it with a <a href> tag would work, yes.

I know going a different direct when you got it to display is going to be a hard way to go, but if you ever plan on doing a page like this again I think when you're done you'll see how much easier it would be to set it up this way vs having to do it the way you were before is.  SImple things (like changing how many rows across it will span before starting a new row) take 3 seconds, rather than having to change how the whole thing works.  Things like that will save you time in the long run big time.
0
 
OVC-it-guyAuthor Commented:
I'm in agreement.  I'll get starting on it (again) a little later.
0
 
arhameCommented:
Okay, I'm off work so if you run into any problems just post here and I'll be back in the morning to answer them for you.
0
 
OVC-it-guyAuthor Commented:
Most of this is going well.  However, my images aren't showing becuase I need some literal text in the ImageURL= property, followed by some more.  My ImageUrl='new_images/small/<%# Eval("sml_image") %>.jpg' /> doesn't work.  I don't get any error when the page loads, but I don't get the images either.  WHat is the correct syntax?  Same deal with lblPrice - <asp:Label ID="lblPrice" runat="server" Text='$<%# Eval("price") %>'" /> only give me the dollar sign.  Syntax again?
0
 
arhameCommented:
Image:
ImageUrl='<%# Eval("PhotoName", "new_images/small/{0}") %>'
Price Label:
Text='<%# Eval("price", "${0}") %>

Basically in the Eval itself we can setup a template for it to put the database results in, the database results go where the {0} is if that wasn't obvious.
0
 
OVC-it-guyAuthor Commented:
Close.  I already resolved this formatting thing with:

<ItemTemplate>
                        <a href="product_details.aspx?sku=<%# Eval("sku") %>"><asp:Image ID="imgSml" runat="server" ImageUrl='<%# "new_images/small/" + Eval("image") + ".jpg" %>' /></a><br />
                        <asp:Label ID="lblProduct" runat="server" Text='<%# Eval("product") %>' /><br />
                        <asp:Label ID="lblShort_descr" runat="server" Text='<%# Eval("short_descr") %>' /><br />
                        <asp:Label ID="lblPrice" runat="server" Text='<%# "$" + Eval("price") %>' /><br /><br />
                    </ItemTemplate>
0
 
arhameCommented:
Ahhh, gotcha.  Either works :)  Whatever makes more sense to you I guess.  The syntax I used it what Visual Studio pops out if you use its built in tools to tell it where the path is - so that's where I learned that.

But hey if yours works and you like that formatting better, power to ya :)  Everything else going smoothly with the Datalist?
0
 
OVC-it-guyAuthor Commented:
I know this is probably in the parameters for the datalist, but I'm out of time on this.  How exactly do I put a thin chalkline in med. gray between the products (and a little cellpadding) and have each of them vertically aligned to the top?  What I have for the datalist so far is:

<asp:DataList ID="DataList1" runat="server" RepeatColumns="4" RepeatDirection="Horizontal" DataSourceID="SqlDataSource1">
0
 
OVC-it-guyAuthor Commented:
I think it should be in the:

<SeparatorStyle Font-Bold="False" Font-Italic="False" Font-Overline="False" Font-Strikeout="False"
                Font-Underline="False" ForeColor="Gray" HorizontalAlign="Center" VerticalAlign="Top" />
0
 
arhameCommented:
First set the property on the Datalist:  Gridlines="both"

Close - it'd be under hte <ItemStyle> tag not SeperatorStyle
<ItemStyle VerticalAlign="Top" BorderColor="LightGray" />

And on the Datalist itself you can set the Cellpadding="3" property.
0
 
OVC-it-guyAuthor Commented:
Yes, <ItemStyle ..> instead of <SeperatorStyle ...>.  That would explain why I'm not seeing it.  But when I added the <ItemStyle ...> I still don't see it.  Where does it go - after </ItemTemplate> but before </DataList> ?
0
 
arhameCommented:
Yes.  Make sure you have the GridLines="Both" under the <asp:Datalist   tag.
0
 
OVC-it-guyAuthor Commented:
Oops, Gridlines.  That got it.
0
 
OVC-it-guyAuthor Commented:
I appreciate you hanging with me.  It's all looking good (almost there).  I have other fields that I query from the product table in the database - sale price, special start date and special end date, as well as setting a variable for system time in the same format as the latter two fields.  I need to compare the system date to the other two dates.  If it is between the Special Start Date and the Special End Date, then I need to display the sale price in bold red.  But how do I do that with a DataList?

Ref:
sale_price:
                <asp:Label ID="sale_priceLabel" runat="server" Text='<%# Eval("sale_price") %>'>
                </asp:Label><br />
                special_start_date:
                <asp:Label ID="special_start_dateLabel" runat="server" Text='<%# Eval("special_start_date") %>'>
                </asp:Label><br />
                special_end_date:
                <asp:Label ID="special_end_dateLabel" runat="server" Text='<%# Eval("special_end_date") %>'>
                </asp:Label>
0
 
arhameCommented:
You can access the controls individually in the Item_Databound event.  You will need to DirectCast each Label to access it's content but you can check the values of it just like you were before.  Example:

Now this isn't a working function obviously for your situation, but from what I remember of your previous code you had the vb.net stuff to check if it's between the dates already.  But that's how you access the controls in a datalist.  I'll be working on a project of my own so it might take me a few to respond for a bit.
        If e.Item.ItemType = ListItemType.Item Then
            Dim lbl1 As Label = DirectCast(e.Item.FindControl("special_start_dateLabel"), Label)
            If lbl1.Text = "11/13/08" Then
                Dim lbl2 As Label = DirectCast(e.Item.FindControl("Sale_PriceLabel"), Label)
                lbl2.Font.Bold = True
            End If
        End If

Open in new window

0
 
OVC-it-guyAuthor Commented:
No, you're fine.  This is the kick in the . . . point in the direction I needed.  Management has me working something else, so it may be a while for me to come back to this, as well.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now