[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

ASP Data Formatting

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

%>
<br>
<table border="0" width="100%">
  <tr>
	<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>
  </tr>
<%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>
    <%rs.MoveNext%>
    </tr>
<%loop
rs.close
%>

Open in new window


What I would like to see is something like this:

PIC
row1 information
row2 information
row5 information

CEU
row3 information
row4 information

Thank you.
0
nirsait
Asked:
nirsait
  • 3
  • 2
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
just change your sql to:

strSQL="SELECT * FROM CEU_Tracking WHERE iMISID = 612682 order by CEU_PIC desc"
0
 
nirsaitAuthor Commented:
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:

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


CEU
row3  information  8
row4  information  9
   Total for CEU  =  17
0
 
alorentzCommented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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>"
   else
       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  
   rs.MoveNext
    
loop

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.
0
 
alorentzCommented:
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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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.
0

Featured Post

Technology Partners: 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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now