Solved

VB Script in ASp page

Posted on 2014-03-27
8
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 53

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

 
LVL 53

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 53

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

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!

Question has a verified solution.

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

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

724 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