Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Vancouver
    Posts
    17

    Unanswered: can i do multiple colored rowed in a table?

    hi folks, hoping someone can help me out with this one:
    I have a report.asp page where i display data from an Access query. I know how to set my table so that each row will alternate 2 colors back and forth, but can i set the background color, based on a value in a field.

    When someone enters data in my form, one of the fields is called "recency" Here they must select whether the data is "new", "ongoing", or "expired". These are text fields in my db.

    Soooo, when i show all my records in my report, I want to be able to base the bgcolor for a particular recordset, based on the value that record has in it's "recency" field. i.e if that recordset has "new" in the Recency field, then display the row background in green, if it is "ongoing", then the row background should be yellow, etc etc

    Any ideas?
    Here is a couple lines of my code where i display the data:
    Code:
      <tr VALIGN="TOP">
        <td><%Response.Write RS("planno")%></td>
        <td><%Response.Write RS("type")%></td>
        <td><%Response.Write RS("company")%></font></td>
      </tr>
    still in the baby steps of coding

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post here you go

    Here you go:
    '--------------------------------------------------------
    <%
    '------------------------------
    ' Set Color based on value
    '------------------------------
    dim tdBGcolor
    if Regency = "new" then
    &nbsp &nbsp&nbsp tdBGcolor = "green"
    else
    &nbsp &nbsp&nbsp tdBGcolor = "yellow"
    end if
    '------------------------------
    ' Display HTML code
    '------------------------------
    %>
    <tr VALIGN="TOP">
    &nbsp&nbsp <td bgcolor="<%=tdBGcolor%>">
    &nbsp &nbsp&nbsp&nbsp <%Response.Write RS("planno")%>
    &nbsp&nbsp </td>
    &nbsp&nbsp </td bgcolor="<%=tdBGcolor%>">
    &nbsp &nbsp&nbsp&nbsp <%Response.Write RS("type")%>
    &nbsp&nbsp </td>
    &nbsp&nbsp <td bgcolor="<%=tdBGcolor%>">
    &nbsp &nbsp&nbsp&nbsp <%Response.Write RS("company")%>
    &nbsp&nbsp </td>
    </tr>
    '--------------------------------------------------------
    You really need to stop using recordsets too. They are slow. you should put the recordset into an array.

  3. #3
    Join Date
    Feb 2004
    Location
    Vancouver
    Posts
    17

    Re: here you go

    thanks! I had to make one adjustement to your code:
    if recency="NEW" then to if rs.Fields("recency") = "NEW" then

    One more change i need ot make though. I need to get it to work for 3 colors and i tried altering the code, but it doesnt seem to work. Whats wrong here:
    Code:
    <%
    ' Set Color based on value
    dim tdBGcolor
    if rs.Fields("recency") = "NEW PLAN" then
         tdBGcolor = "lightgreen"
    else 
    if rs.Fields("recency") = "EXPIRED" then
         tdBGcolor = "blue"
    else 
         tdBGcolor = "yellow"
    end if
    %>
    thanks again
    still in the baby steps of coding

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108
    It should be ELSEIF <-- one word

    ' Set Color based on value
    dim tdBGcolor
    if rs.Fields("recency") = "NEW PLAN" then
    &nbsp;&nbsp;&nbsp;tdBGcolor = "lightgreen"
    elseif rs.Fields("recency") = "EXPIRED" then
    &nbsp;&nbsp;&nbsp;tdBGcolor = "blue"
    else
    &nbsp;&nbsp;&nbsp;tdBGcolor = "yellow"
    end if
    %>
    Also remember that these comparisons are CASE SENSITIVE. So you might need to put:
    if ucase(rs.Fields("recency")) = "NEW PLAN" ...

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    You really need to stop using recordsets too. They are slow. you should put the recordset into an array.
    This is a very bold statement Thele. Surely it would depend on the overhead of turning the recordset into an array and how much work you are doing with the recordset.

    Unless you want to share with us some groovy method of doing the conversion...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108
    Typically, for larger sets of data, you should use an array. I will put up some sample code tommorrow.

    And I misspoke. What I meant was he should use the getrows() method to put the RecordSet data into an array, THEN he should close the recordset.


    `Le

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I'd be really interesting in seeing some stats and code along this line.

    The recordset I used generally aren't that large but it could be a fun experiment...

    I might play with it at home myself...

    Wouldn't you get an increase in your memory foot print when you did this as you'd have a populated array and a populated recordset at the same time (albeit for a very brief period). Wouldn't this affect performance as well as the over head involved in the processing to change it from a recordset to an array?

    Please don't get me wrong, I'm not saying your claim is incorrect I just wonder how big the increase in speed is and in what circumstances the conversion to an array to valid/appropiate.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108
    From what I recall, you should see a significant speed increase, at least 3-5 times faster, if not more.

    To test, grab a few thousand records from somewhere.

    Loop it with your standard rs.next and time it so you know how long it takes to go through all the records.

    Then try it the other way:
    myArray = rs.getrows
    rs.close
    for i = 0 to ubound(myArray,2)
    'yadda yadda yadda
    next

    Compare how long each way takes. I am curious to see if your numbers. I will run a test myself tomorrow afternoon and post my results.

    When you loop the records, try something realworld, like putting the records into a drop down box.

    ~Le

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    When you run your tests can you also try and grab the memory usage of the components in use (dllhost from memory, might be something else though, I haven't played much) just to see what happens....

    I believe you are right, the processing of the array should be faster, I am just curious to know how much faster across various different datasets.

    What I'm really interested in as well as the looping time is the converting to array overhead/time depending on recordset size....

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post

    Hmm.. My tests did not have significant time increases (about 2 seconds faster using an array on 30k records).

    Here were my numbers:
    30,000 records, put into a drop-down box

    --Using Do While / RS.movenext:
    17 Seconds to insert each record into the dropdownbox

    --Using an Array (with rs.getrows)
    15 Seconds to do a "MyArray = rs.GetRows"
    Zero seconds to put all records from the array into the dropdownbox

    However, someone did write something that makes sense:
    [quote]
    When it comes to increasing the efficiency of your script, you need to reduce both the number of objects instantiated, the duration of their scope, and the number of trips to each object. When working with a recordset that contains several rows of data, you can increase the efficiency of your script by using the Recordset objects GetRows method. By stuffing the Recordset data into an array, your able to release your database objects quicker reducing overheads on the server, plus its quicker to loop through an array than a Recordset. The disadvantage of using the GetRows method, is that you give up all of the features that the Recordset object has to offer.
    [quote]

    Hmm. Your thoughts?

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    *mutter something about stupid EDS proxy settings killing his browsing abilities* >:|

    Anyhow back to the subject... yeah, the comment that you posted makes perfect sense, never open anything or create anything until you need it and close it or destory it as soon as you are finished with it.... definately a good practise...

    I saw a neat use of a function like getrows once where basically the whole table was created on the fly by using a </td><td> field delimiter and a </td></tr><tr><td> record delimiter.... (or something similar)... now with that in mind you could skip a pile of extra work....

    [edit]I think the method was GetString[/edit]

    I primarily use XML these days and just perform transformations so it's not really a big deal for me.... but it is interesting to see what is going on.
    Last edited by rokslide; 04-14-04 at 21:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •