VB Script in ASp page

I have an asp page where I pull data from excel. in one of my fields I am showing either a green, yellow or red dot in gif format depending on the value in "Status", which is either a 1, 2 or a 3. What I really want to do is average up all the values in "Status" and if its 1 then green and if its 2 then yellow and if its 3 then red.

the if statement I am using now is below:

If rs.Fields("Status") = 1 Then
&#9;Response.Write "<img src=../../images/dotgreen.gif>"
Elseif rs.Fields("Status") = 2 Then
&#9;Response.Write "<img src=../../images/dotyellow.gif>"
Elseif rs.Fields("Status") = 3 Then
&#9;Response.Write "<img src=../../images/dotred.gif>"
End If

Open in new window

Who is Participating?
Scott Fell, EE MVEConnect With a Mentor Developer & EE ModeratorCommented:
In most cases, calculating in the db slows things down.  If you have a few records and that is easier, it may be a good choice.  However, if yo have 500 or 1000 records, there is a lot of looping going on.  So now the choice is 2 calls to the db with 2 recordsets as I outlined, or even quicker ( I said a couple of choices) would be to throw your results into an array.  Perhaps using getrows.

Once you have your data in the array, you can loop through your array and grab the total, count and sum.  A bit more coding on the page, but will result in faster processing.   Because the data is now in the array, you can access it multiple times without going back to the db.

'Assume you have an array from getrows
dim theTotal, theCount, theAvg
theCount=UBound(myArray, 2) ' total records
For r = LBound(myArray, 2) To UBound(myArray, 2)
    	dblAmount = myArray(3, r)  ' assume the field you want is in the 4th column of your array
        ' The step above is not needed, but can make it easier to track what column you are on.	
' now we know the total records and the sum amount of some field.

' now you can look through the array again without going back to the db 

For r = LBound(myArray, 2) To UBound(myArray, 2)
    ID= myArray(0, r) ' assume the id is the first column
    field2= myArray(1, r) 
    field3= myArray(2, r)
    field6= myArray(5, r)  
   select case cstr(int(cdbl(theTotal)/cdbl(field6))) ' you may want to round instead of just getting the integer
      case "1" 
            Response.Write "<img src=../../images/dotgreen.gif>"
      case "2" 
            Response.Write "<img src=../../images/dotyellow.gif>"
     case "3"
           Response.Write "<img src=../../images/dotred.gif>"
  end select 

Open in new window

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you calculate the average in your sql statement? that would be the easiest route to go. post your sql if you need help
jlcannonAuthor Commented:
In my sql I am just grabbing everything that has to do with a certain asset

sql = "SELECT * from tblData where Asset LIKE 'FB%' ;" and that pulls up all the assets that start with FB. then I have on that looks for asset LIKE "ST%" and then one looking for "GT%"
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You have a couple of options.  You could make a new recordset that sums and counts.

SELECT SUM(column_name) as theTotal, Count(column_name) as theCount FROM table_name;

Now from that recordset you can use

You can calculate in your sql, but it is better to do it in your server side code.
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
As a side note, if you can start looking for files with 2 dots and a slash, "../../images/dotred.gif" that means you have parent paths turned on which can leave you open to hacks.


Basically, somebody could potentially get at files you don't want anybody to touch. https://www.acunetix.com/websitesecurity/directory-traversal/
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
just use the AVG command in your sql:

sql = "SELECT *, statusAVG = AVG( status ) from tblData where Asset LIKE 'FB%' ;"


no need to get the count and sum of the fields when there's already a built in function
jlcannonAuthor Commented:
exactly what i needed, thank you!

My apologies; I posted in this question in error.

Senior Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.