Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-26
9
Medium Priority
?
658 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 143

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
Industry Leaders: 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!

 

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 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