SunQuest
 
           ASP Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Programming > ASP Development
Reload this Page

Database - Recordset - Calculating recordset results

Discuss Calculating recordset results in the ASP Development forum on ASP Free.
Calculating recordset results ASP Development forum discussing ASP related topics including coding practices, ASP tips, and more. Active Server Pages (ASP) enables you to empower your HTML pages dynamically with robust scripting options.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!
  #1  
Old May 12th, 2008, 08:07 PM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Brisbane Australia
Posts: 319 dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 17 h 46 m 2 sec
Reputation Power: 11
Smile Database - Recordset - Calculating recordset results
Hi all

I'm using select count(*) in MySQL statements to return total records.

I have 2 of these recordsets. I'm using response.write to show the results on the asp page.

I'd like to calculate a percentage using the 2 results eg(sqlResult1/sqlResult2 *100)

errors are occuring. Is there a tried and true method of doing this.

I appreciate any advice
__________________
Damo
Reply With Quote
  #2  
Old May 12th, 2008, 08:14 PM
keep_it_simple's Avatar
keep_it_simple keep_it_simple is offline
KIS
ASP Free Beginner (1000 - 1499 posts)
  
Join Date: Jul 2007
Location: USA
Posts: 1,031 keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 2 Weeks 2 Days 8 h 12 m 51 sec
Reputation Power: 340
Send a message via Yahoo to keep_it_simple
Quote:
Originally Posted by dtayl20
Hi all

I'm using select count(*) in MySQL statements to return total records.

I have 2 of these recordsets. I'm using response.write to show the results on the asp page.

I'd like to calculate a percentage using the 2 results eg(sqlResult1/sqlResult2 *100)

errors are occuring. Is there a tried and true method of doing this.

I appreciate any advice



is it a logical error...or a syntax error..../


post relevant code
__________________
Please give respect to those that helped solve an issue by clicking on the reputation icon
Reply With Quote
  #3  
Old May 12th, 2008, 08:49 PM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Brisbane Australia
Posts: 319 dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 17 h 46 m 2 sec
Reputation Power: 11
Smile
It's a type mismatch error. I've tried assigning variable types... but that doesn't seem to work...

below are the recordsets...

<% set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select count(*) From LogIn Where FirstLogin='1' and State = 'Queensland'",conn
response.write(rs.Fields(0))
LoginQueensland=rs.Fields(0)
rs.close%>

<% set rsQP=Server.CreateObject("ADODB.Recordset")
rsQP.Open "Select Count(*) From LogIn where State='Queensland'", conn
rsQP=rsQP.Fields(0) %>


<% repsonse.write (LoginQueensland/rsQP * 100) %>

Thanks ...
Reply With Quote
  #4  
Old May 12th, 2008, 09:05 PM
soft903's Avatar
soft903 soft903 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2007
Posts: 191 soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 7 h 54 m 49 sec
Reputation Power: 62
Hi,

rsQP is a recordset
You should declare a variable like you did for LoginQueensland
Let say declare LoginTotalQueensland
Try this
ASP Code:
Original - ASP Code 
  1. <%
  2. rsQP.Open "Select Count(*) From LogIn where State='Queensland'", conn
  3. LoginTotalQueensland=rsQP.Fields(0)
  4. %>
  5. <%
  6. repsonse.write (LoginQueensland / LoginTotalQueensland * 100)
  7. %>
Hope it helps
Reply With Quote
  #5  
Old May 12th, 2008, 09:20 PM
keep_it_simple's Avatar
keep_it_simple keep_it_simple is offline
KIS
ASP Free Beginner (1000 - 1499 posts)
  
Join Date: Jul 2007
Location: USA
Posts: 1,031 keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 2 Weeks 2 Days 8 h 12 m 51 sec
Reputation Power: 340
Send a message via Yahoo to keep_it_simple
Code:
<% 
 set rs=Server.CreateObject("ADODB.recordset")
 rs.Open "Select count(*) AS Q1 From LogIn Where FirstLogin='1' and State = 'Queensland'",conn

 iQ1 = rs("Q1")

 rs.close

 set rsQP=Server.CreateObject("ADODB.Recordset") 
 rsQP.Open "Select Count(*) AS Q2 From LogIn where State='Queensland'", conn
  
 iQ2 = rsQP("Q2")

 If iQ1 > 0 AND iQ2 > 0 Then
  ' String
  Response.Write "(" & iQ1 & "/" & iQ2 & "* 100)"

  ' Result
  Response.Write CDbl(iQ1/iQ2 * 100)
 End If

 %>
Comments on this post
soft903 agrees: check &gt;0 is important. (maybe mistype: there is a rsQP in response.write)
Last edited by keep_it_simple : May 12th, 2008 at 10:05 PM. Reason: mispelled vars and changed result to cdbl
Reply With Quote
  #6  
Old May 12th, 2008, 09:24 PM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Brisbane Australia
Posts: 319 dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 17 h 46 m 2 sec
Reputation Power: 11
I'll give it a go ....
Reply With Quote
  #7  
Old May 12th, 2008, 10:06 PM
keep_it_simple's Avatar
keep_it_simple keep_it_simple is offline
KIS
ASP Free Beginner (1000 - 1499 posts)
  
Join Date: Jul 2007
Location: USA
Posts: 1,031 keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level)keep_it_simple User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 2 Weeks 2 Days 8 h 12 m 51 sec
Reputation Power: 340
Send a message via Yahoo to keep_it_simple
Quote:
soft903 agrees: check >0 is important. (maybe mistype: there is a rsQP in response.write)


indeed...good eye..ty
Reply With Quote
  #8  
Old May 13th, 2008, 05:58 AM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Brisbane Australia
Posts: 319 dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 17 h 46 m 2 sec
Reputation Power: 11
Hi all

Nope... type mismatch errors are occuring. The method works if only one variable is used in the final response.write.... but when dividing (or multiplying for that matter) errors occur. Perhaps its because the select count(*) result isn't really a number??? I can't think why else mismatch errors would occur??
Reply With Quote
  #9  
Old May 13th, 2008, 07:08 AM
Shadow Wizard's Avatar
Shadow Wizard Shadow Wizard is offline
Moderator From Beyond
Click here for more information.
  
Join Date: Sep 2004
Location: Israel
Posts: 26,621 Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)Shadow Wizard User rank is General 7th Grade (Above 100000 Reputation Level)  Folding Points: 325693 Folding Title: Super Ultimate Folder - Level 1Folding Points: 325693 Folding Title: Super Ultimate Folder - Level 1Folding Points: 325693 Folding Title: Super Ultimate Folder - Level 1Folding Points: 325693 Folding Title: Super Ultimate Folder - Level 1Folding Points: 325693 Folding Title: Super Ultimate Folder - Level 1Folding Points: 325693 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 3 Months 1 Week 4 Days 13 h 47 m 29 sec
Reputation Power: 1441
not many people know, but VBScript has data types.
when you bring something from database, it might have data type
that VBScript can't handle by default.

for such cases, you can use special functions that will convert
data to the desired type.

one of those is CLng, which stand for Cast Long.
correct syntax would be:
Code:
<%
Set rs=Server.CreateObject("ADODB.recordset")
rs.Open "Select count(*) From LogIn Where FirstLogin='1' and State = 'Queensland'",conn
FirstLoginQueensland = CLng(rs(0))
rs.close

Set rsQP=Server.CreateObject("ADODB.Recordset")
rsQP.Open "Select Count(*) From LogIn where State='Queensland'", conn
LoginQueensland = CLng(rsQP(0))
rsQP.Close

response.write("first: " & FirstLoginQueensland & "<br />")
response.write("total: " & LoginQueensland & "<br />")
response.write("percentage: " & ((FirstLoginQueensland/LoginQueensland) * 100) & "<br />")
%>
Reply With Quote
  #10  
Old May 13th, 2008, 05:59 PM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Brisbane Australia
Posts: 319 dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 17 h 46 m 2 sec
Reputation Power: 11
Nice work shadow wizard. Thanks to the others as well.

Just to finish off... how do I display the calculation to 2 decimal places??

Thanks again
Reply With Quote
  #11  
Old May 13th, 2008, 06:04 PM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Brisbane Australia
Posts: 319 dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 17 h 46 m 2 sec
Reputation Power: 11
shadow wizard

i've tried looking up vbscript data types... without any luck. do you have a handy URL reference somewhere that i could refer to if i have future data type issues??

Thanks again
Reply With Quote
  #12  
Old May 13th, 2008, 08:04 PM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Brisbane Australia
Posts: 319 dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level)dtayl20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 1 Day 17 h 46 m 2 sec
Reputation Power: 11
I found a data type reference that may be useful
http://www.microsoft.com/technet/sc...s.mspx?mfr=true

I'm still having trouble with rounding to 2 decimal places...
Reply With Quote
  #13  
Old May 13th, 2008, 08:14 PM
soft903's Avatar
soft903 soft903 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2007
Posts: 191 soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level)soft903 User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 2 Weeks 1 Day 7 h 54 m 49 sec
Reputation Power: 62
Hi,

See would FormatNumber help you
Reply With Quote
  #14  
Old May 13th, 2008, 10:47 PM
dtayl20 dtayl20 is offline
Contributing User
ASP Free Newbie (0 - 499 posts)