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:

Here you go:
You really need to stop using recordsets too. They are slow. you should put the recordset into an array.

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:thanks again

It should be ELSEIF <-- one word

Also remember that these comparisons are CASE SENSITIVE. So you might need to put:
if ucase(rs.Fields("recency")) = "NEW PLAN" ...

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

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

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.

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

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

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?

*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.