ASP Data Formatting

Posted on 2012-08-14
Last Modified: 2012-08-15
I am displaying table data that has a field that I would like to format (or sort) based on that field. The field either has a 'PIC' or 'CEU' ("CEU_PIC"). I want to display the information separated with all the data that has a 'PIC' and then all the data that has a 'CEU'.

Here is my current code:

Dim conn, rs, strSQL

set conn=Server.CreateObject("ADODB.Connection")

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adUseClient = 3
Const adCmdText = 1
Const adLockOptimistic = 3

set rs = Server.CreateObject("ADODB.recordset")

strSQL="SELECT * FROM CEU_Tracking WHERE iMISID = 612682"
rs.Open strSQL, aConnectionString, adOpenForwardOnly, adLockOptimistic

<table border="0" width="100%">
	<th>Track Type</th><th>Date</th><th>Name of Event</th><th>Name of Educational Session</th><th>CEU/PICs Earned</th><th>CEU/PIC Type</th><th>TED Event</th><th>Core Competencies Covered/PIC Category</th><th>Date Updated</th>
<%do until rs.EOF%>
    <tr valign="top">
       <td><%= rs("CEU_PIC")%></td><td><%= rs("DateEvent")%></td><td><%= rs("NameEvent")%></td><td><%= rs("NameSession")%></td><td><%= rs("UnitsEarned")%></td><td><%= rs("CEUPICType")%></td><td><%= rs("TEDEvent")%></td><td><%= rs("CEUPICCore")%></td><td><%= rs("DateUpdated")%></td>

Open in new window

What I would like to see is something like this:

row1 information
row2 information
row5 information

row3 information
row4 information

Thank you.
Question by:nirsait
    LVL 32

    Expert Comment

    by:Big Monty
    just change your sql to:

    strSQL="SELECT * FROM CEU_Tracking WHERE iMISID = 612682 order by CEU_PIC desc"

    Author Comment

    That 'works'.

    However, I guess I want something a bit different. While this does separate the data between 'PIC' & 'CEU', I guess what I want is to be able to have everything that is a 'PIC' displayed and then 'CEU' displayed with space between them as I will need to do some additional formatting/calculations.

    What I would like to eventually see is something like this:

    row1  information  2
    row2  information  3
    row5  information  5
       Total for PIC  =  10

    row3  information  8
    row4  information  9
       Total for CEU  =  17
    LVL 31

    Expert Comment

    LVL 32

    Accepted Solution

    you can change your loop to first build the HTML for each section, then output it to the screen:

    do until rs.EOF
       ceuPic = rs("CEU_PIC")
       if ceuPic = "PIC" then 
          picHTML = picHTML & "<tr valign='top'><td>" & ceuPic " & "</td><td>" & rs("DateEvent") & "</td><td>" & rs("NameEvent") & "</td><td>" & rs("NameSession")  & "</td><td>" & rs("UnitsEarned") & "</td><td>" & rs("CEUPICType") & "</td><td>" & rs("TEDEvent") & "</td><td>" & rs("CEUPICCore") & "</td><td>" & rs("DateUpdated") & "</td></tr>"
           ceuHTML = ceuHTML & "<tr valign='top'><td>" & ceuPic " & "</td><td>" & rs("DateEvent") & "</td><td>" & rs("NameEvent") & "</td><td>" & rs("NameSession")  & "</td><td>" & rs("UnitsEarned") & "</td><td>" & rs("CEUPICType") & "</td><td>" & rs("TEDEvent") & "</td><td>" & rs("CEUPICCore") & "</td><td>" & rs("DateUpdated") & "</td></tr>"
       end if  
    Response.Write ceuHTML & "<tr><td colspan='9'>&nbsp;</td></tr>" & picHTML

    Open in new window

    this'll add a blank line in between the PIC and CEU.
    LVL 31

    Expert Comment

    oh you are kidding selecting that answer :)

    What happens when you need to add new groups, etc.  Update your code every time?  Your supposed to be building scalable code, that can handle the data changes dynamically.  That is the solution I provided.

    Anyway, best of luck.
    LVL 32

    Expert Comment

    by:Big Monty
    my guess for the selected answer was because you gave a vague link with no assistance on how to incorporate it into the OP's question. True my solution isnt very scalable, but it's a lot easier to understand and implement, as well as more efficiently written (I saw 2 loops in your solution compared to 1). Without knowing all of the exact background details, its almost impossible to guesswhat exactly is needed - a quick and dirty solution or a complex scalable one. we have no idea of more groups will be added, and its up to the OP to define that.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now