Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB Script in ASp page

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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