Solved

Using Asp.net to created a custom unordered list by looping through database records

Posted on 2013-01-26
9
650 Views
Last Modified: 2013-01-26
I am retrieving data from the database to build an unordered list.  What must I do to fix the loop structure so that I can obtain my desired output  -- see "DESIRED OUTPUT:" below?

It should be 3 sets of unordered list.


Dim iCounter As Integer
Dim jCounter As Integer
Dim sb as StringBuilder  = new StringBuilder()

Dim strMemberCategory As String

    Dim objConn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SITEdbConnectString").ConnectionString)
      Dim objRS as SqlDataReader

    Dim cmdSQL As New SqlCommand("dbo.P_featured_members_advertisement_show", objConn)
      cmdSQL.CommandType = CommandType.StoredProcedure
                   
      objConn.Open()
    objRS = cmdSQL.ExecuteReader()

sb.Append("<ul>" & vbCrlf)

While objRS.Read()

If  objRS("member_category") = "S" Then
    strMemberCategory = "SS"
Else
    strMemberCategory = "BP"
end if

                 
For iCounter = 1 to 3
                     
              sb.Append("<li class=""tickerBox"">" & vbCrLf)
             
                      sb.Append("<div class=""fmembers"">" & vbCrLf)
          For jCounter = 1 to 3
                        sb.Append("<div style=""height:360px; width:208px; display:none"">" & vbCrLf)                          
                            sb.Append("<a href=""http://www.abcxyz.com/" & myaccount.GenerateURL(objRS("MemberName"), objRS("memberID"), strMemberCategory) & """><img src=""coverflow/content/images/" & objRS("member_photo") & """ alt="""" /></a>" & vbCrLf)
                            sb.Append("<h3><span>" & objRS("MemberName") & "</span>" & objRS("MemberLocation") & "</h3>" & vbCrLf)                          
                       sb.Append("</div>" & vbCrLf)
          Next jCounter
                       
                     sb.Append("</div>" & vbCrLf)
                 sb.Append("</li>" & vbCrLf)

Next iCounter
End While
objConn.close()
sb.Append("</ul>" & vbCrlf)

 lblfootermenu.Text = sb.ToString()

DESIRED OUTPUT:
====================
NO MORE THAN 3 IN EACH LIST {<li> .(3 items). </li>}, Can have as many list of 3 items as there are data in the database recordset.


<ul>
<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-770.aspx"><img src="gallery/content/images/DebraJohnson.png" alt="" /></a>
            <h3><span>Debra Johnson</span>Los Angeles, CA</h3>
        </div>
    </div>
</li>

<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-220.aspx"><img src="gallery/content/images/LizTaylor.png" alt="" /></a>
            <h3><span>Liz Taylor</span>Silver Spring, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-120.aspx"><img src="gallery/content/images/CassandraThomas.png" alt="" /></a>
            <h3><span>Cassandra Thomas</span>Baltimore, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-730.aspx"><img src="gallery/content/images/TaraClinton.png" alt="" /></a>
            <h3><span>Tara Clinton</span>Chicago, IL</h3>
        </div>
    </div>
</li>
</ul>
0
Comment
Question by:DeMyu
  • 5
  • 3
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38822026
Instead of looping inside the record reader, you should use a variable count ing the number of items you have processed already.
Once you achieved 3, you start the next  list item.
Being on mobile, writing concrete code will be difficult.
0
 

Author Comment

by:DeMyu
ID: 38822049
I will appreciate an example. Thank you.
0
 
LVL 6

Expert Comment

by:esolve
ID: 38822200
I think I got it. Just change the last line:

sb.Append("</li>" & vbCrLf) to
sb.Append("</li>")

It seems the additional vbCrLf add a null character in the string which causes an incorrect result

http://social.msdn.microsoft.com/Forums/en-US/netfxbcl/thread/d98ab0f8-e1ce-4981-8e0d-42a454916890/
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:DeMyu
ID: 38822317
Esolve, Thank you - I will give it a try when I have access to my computer.
0
 

Author Comment

by:DeMyu
ID: 38822351
Esolve, here is the output:

<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
    </div>
</li>
<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
    </div>
</li>
<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-890.aspx"><img src="gallery/content/images/JaneDoe.png" alt="" /></a>
            <h3><span>Jane Doe</span>Temple Hills, MD</h3>
        </div>
    </div>
</li>

<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
    </div>
</li>

<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
    </div>
</li>
<li class="tickerBox">
    <div class="fmembers">
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
        <div style="height:360px; width:208px; display:none">
            <a href="http://www.abcxyz.com/member-820.aspx"><img src="gallery/content/images/TiffanyWilliams.png" alt="" /></a>
            <h3><span>Tiffany Williams</span>Atlanta, GA</h3>
        </div>
    </div>
</li>


I am sure the problem is my loop structure.

Thank you,

DeMuy
0
 
LVL 6

Expert Comment

by:esolve
ID: 38822685
Whats missing only the <UL> elements?

Also ensure that all your recordset objects does have a value because the error is because of null values.

if( not objRS("member_photo") = nothing)

Just test all of these and add them to variable before appending them to the StringBuilder.

And theres nothing wrong with your loop. Also try uncommenting the two lines in the center which appends the recordset objects. If this works then you know theres an error with one of the fields being returned.
0
 

Author Comment

by:DeMyu
ID: 38822800
Esolve, the <UL> was not missing, I just didn't add it to the output I posted.

Also,  objRS("member_photo") is not null. All the fields are not null in the database.

The problem I am having is the repeation I am getting within each <li> ... </li> tags. I have a strong feeling it has something to do with my loop.

The <div>.... </div> tags within each <li> ... </li> tags should be different. For example,

<ul>
<li class="tickerBox">
    <div class="fmembers">
<div> database record 01 </div>
<div> database record 02</div>
<div> database record 03 </div>
</div>
</li>
<li class="tickerBox">
    <div class="fmembers">
<div> database record 04 </div>
<div> database record 05 </div>
<div> database record 06 </div>
</div>
</li>
<li class="tickerBox">
    <div class="fmembers">
<div> database record 07 </div>
<div> database record 08 </div>
<div> database record 09 </div>
</div?
</li>

...... etc
</ul>

Thank you.
0
 
LVL 6

Accepted Solution

by:
esolve earned 500 total points
ID: 38823042
Is this what you want to do?

sb.Append("<ul>" & vbCrLf)

        Dim iIterator As Integer
        iIterator = 1

        While objRS.Read()

            If iIterator = 1 Then
                sb.Append("<li class=""tickerBox"">" & vbCrLf)
                sb.Append("<div class=""fmembers"">" & vbCrLf)
            End If

            'DO WHAT YOU NEED TO DO FOR EACH
            'sb.Append("<div style=""height:360px; width:208px; display:none"">" & vbCrLf)
            'sb.Append("<div>")


            If iIterator = 3 Then
                sb.Append("</div>" & vbCrLf)
                sb.Append("</li>")
                iIterator = 0
            End If

            iIterator = iIterator + 1

        End While

        sb.Append("</ul>" & vbCrLf)




My apologies if I misunderstood. I thought there was something wrong with the output, because I also got an error with the way it rendered the output.
0
 

Author Closing Comment

by:DeMyu
ID: 38823099
Esolve,  I thank you much for your assistance. I had spent countless hours trying to solve this problem. Thank you again.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

786 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