Solved

VB Script in ASp page

Posted on 2014-03-27
8
337 Views
Last Modified: 2014-04-01
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

0
Comment
Question by:jlcannon
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 39960056
can you calculate the average in your sql statement? that would be the easiest route to go. post your sql if you need help
0
 

Author Comment

by:jlcannon
ID: 39960082
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%"
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39960097
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
 
theAvg=cdbl(rs("theTotal"))/cdbl(rs("theCount"))

You can calculate in your sql, but it is better to do it in your server side code.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39960099
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.

http://www.iis.net/learn/application-frameworks/running-classic-asp-applications-on-iis-7-and-iis-8/classic-asp-parent-paths-are-disabled-by-default

Basically, somebody could potentially get at files you don't want anybody to touch. https://www.acunetix.com/websitesecurity/directory-traversal/
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39960108
just use the AVG command in your sql:

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

http://technet.microsoft.com/en-us/library/ms177677.aspx

no need to get the count and sum of the fields when there's already a built in function
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 39960206
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.

http:Q_28366983.html#a39866067
'Assume you have an array from getrows
dim theTotal, theCount, theAvg
theTotal=0
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.	
   theTotal=cdbl(theTotal)+cdbl(dblAmount)
  
next
' 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 
    
next

Open in new window

0
 

Author Closing Comment

by:jlcannon
ID: 39967800
exactly what i needed, thank you!
0
 
LVL 5

Expert Comment

by:Netminder
ID: 39970060
jlcannon,

My apologies; I posted in this question in error.

Netminder
Senior Admin
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

810 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