Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB Script in ASp page

Posted on 2014-03-27
8
Medium Priority
?
359 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 34

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 54

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 54

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 34

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 54

Accepted Solution

by:
Scott Fell,  EE MVE earned 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

581 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