Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 Recordset prob - newbie		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

neerajdeo
Starting Member

USA
33 Posts
Posted - 09 May 2002 :  17:11:00  Show Profile  Visit neerajdeo's Homepage  Send neerajdeo a Yahoo! Message
Hello

Consider the following statement
sSQL = "Select sum(Salary), sum(Expenses) from Employee"
set result = oConn.Execute(sSQL)

Now how do I access the value of sum(Salary) and sum(Expenses)
val = result("??") ' I am confused

Another problem
Lets say that I am able to connect to a database DB and it has a table 'tb'

Now I want to get the 2nd last recordset in the table. Looping is a solution but its ugly. Is there a better way. Can someone tell me a direct sSQL statement for it. I can probably have a feild called count which can keep track of row number but its wasteful and involves complexities when I delete a row. Is there any other way. A SQL statemet will be appreciated as I have gone nuts trying to figure it out.

Thanks a lot
Neeraj

Nathan
Help Moderator

USA
7664 Posts
Posted - 09 May 2002 :  18:46:45  Show Profile  Visit Nathan's Homepage 
sSQL = "Select sum(Salary) as Sal, sum(Expenses) as Exp from Employee"
set result = oConn.Execute(sSQL)

rs("Sal")
rs("Exp")


quote:
Another problem
Lets say that I am able to connect to a database DB and it has a table 'tb'

Now I want to get the 2nd last recordset in the table. Looping is a solution but its ugly. Is there a better way. Can someone tell me a direct sSQL statement for it. I can probably have a feild called count which can keep track of row number but its wasteful and involves complexities when I delete a row. Is there any other way. A SQL statemet will be appreciated as I have gone nuts trying to figure it out.

Consider these -

rs.movelast
rs.moveprevious
count = rs.RecordCount


Nathan Bales
Snitz Exchange | Do's and Dont's

Edited by - Nathan on 09 May 2002 18:47:43
Go to Top of Page

Bullschmidt
New Member

USA
56 Posts
Posted - 09 May 2002 :  18:50:37  Show Profile  Visit Bullschmidt's Homepage
Neeraj,

Perhaps change this:
sSQL = "Select sum(Salary), sum(Expenses) from Employee"

To be this instead:
sSQL = "Select sum(Salary) As SalarySum, sum(Expenses) As ExpensesSum from Employee"

Then to access the value of sum(Salary) and sum(Expenses):
MySalarySumVariable = result("SalarySum")
MySalaryExpensesVariable = result("ExpensesSum")

And to get the 2nd of the last highest MyID in a table (and remember that a new autonumber can actually be LOWER than an existing autonumber after a database compact):
strSQL = "SELECT TOP 2 * FROM MyTable ORDER BY MyID DESC"
Then with your recordset move to the 2nd record (of course you may want to be sure that are at least 2 records in the table before trying this):
MyRecordset.MoveNext



J. Paul Schmidt - ASP Web Developer
www.Bullschmidt.com - To put live data on the Web...
Go to Top of Page

Bullschmidt
New Member

USA
56 Posts
Posted - 09 May 2002 :  18:53:18  Show Profile  Visit Bullschmidt's Homepage
Nathan looks like you beat me by 4 seconds...

J. Paul Schmidt - ASP Web Developer
www.Bullschmidt.com - To put live data on the Web...
Go to Top of Page

neerajdeo
Starting Member

USA
33 Posts
Posted - 09 May 2002 :  23:38:25  Show Profile  Visit neerajdeo's Homepage  Send neerajdeo a Yahoo! Message
Thanks a lot guys

U are awesome

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000 Version 3.4.07