SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help on recordset query

    If I already have a recordset how do I further query the recordset with SQL with aggregate functions? Example say I want to find the average age in an existing recordset (this recordset has many uses so it was created as such)

    tblExample (design view)
    UserID
    Name
    Age
    Address
    City
    State

    strSql="SELECT * FROM tblExample WHERE State='MI';"
    set objRs=server.CreateObject(ADODB.Recordset)
    objRs.Open strSql,objConn,1,3

    How do I now find the average age of users in objRs? I know if I wanted to intitially I could have included "AVG(Age)" in the SELECT clause. But I want to accomplish this after creating the recordset. I would like to accomplish this with a command instead of looping thru the records. And I want to reuse the inital recordset. Help.

  2. #2
    SitePoint Evangelist azizur_rahman's Avatar
    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use ADO Command!

    http://www.asp101.com/articles/wrox/asp30/26100901.asp

    and if you IIS Installed: http://localhost/iishelp/iis/htm/asp/iiwadata.htm (Improving Queries with the Command Object)
    Azizur Rahman
    Web Application Developer

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    azizur_rahman,
    I looked at the references and still am clueless. Could you give me an example. Thanks.

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2002
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.w3schools.com/sql/sql_functions.asp

    scroll down to the avg function:

  5. #5
    SitePoint Evangelist azizur_rahman's Avatar
    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know if I wanted to intitially I could have included "AVG(Age)" in the SELECT clause. But I want to accomplish this after creating the recordset. I would like to accomplish this with a command instead of looping thru the records. And I want to reuse the inital recordset. Help.
    If it was me I'd create a simple function that will return the average age.

    Code:
    Function AvgAge
    
    'set the SQL String to execure
    strSql="SELECT AVG(Age) as AverageAge FROM tblExample;"
    
    'Make new Record set Object
    set objRsAvgAge=server.CreateObject(ADODB.Recordset)
    
    'Execure SQL String to get average
    objRsAvgAge.Open strSql,objConn,1,3
    
    'return average
    AvgAge = ObjRsAvgAge.Fields.Item("AverageAge").value 
    
    ' do good programming
    ' before exit close connection
    objRsAvgAge.Close()
    
    set strSql= nothing
    set objRsAvgAge = nothing
    
    end Function
    and to make use of that in ASP
    [VBS]Average Age is <%= AvgAge() %>[/VBS]

    or?
    [VBS]Average Age is <% AvgAge() %>[/VBS]



    hope this helps
    Last edited by azizur_rahman; Sep 14, 2002 at 18:21.
    Azizur Rahman
    Web Application Developer

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would like to be able to run aggregate funtions like Average against the already instantiated recordset object ObjRs since I will be using the recordset for a variety of uses. This so I do not have to keep creating recordsets or closing and opening the current recordset. Thanks for your help.

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    To be honest, you are MUCH better off doing it the way people have suggested and running another query. Let the database perform the function and you'll find you'll get much better results than dealing with the recordset itself. Depending on the action, you can also return multiple aggregate functions from the same query, so you should be able to return an average age, a maximum age, a minimum age, etc all in the same query....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dave,
    I was looking to be efficient and in my newness to ASP was looking in the wrong way. Thanks for pointing that out to me. Although I wonder now when to use recordsets and when to use commands against the data store. There is a lot of power in the recordset object but I understand there is also a cost in performance to keep in mind. Also, there must be a performance hit for opening and closing recordsets. Any suggestions?

    azizur_rahman and Gokou thanks also for the information you provided.

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by Spock
    Dave,
    I was looking to be efficient and in my newness to ASP was looking in the wrong way. Thanks for pointing that out to me. Although I wonder now when to use recordsets and when to use commands against the data store. There is a lot of power in the recordset object but I understand there is also a cost in performance to keep in mind. Also, there must be a performance hit for opening and closing recordsets. Any suggestions?
    From my experience, if you have a choice to do something in the recordset or in the database, choose the database. This means averages, group bys, sorts, etc. This is what the database is designed to do, manipulate data. Use it wherever possible. If you have access to SQL Server, stored procs are your friend. Use them wisely as well...

    Recordsets are best used to retrieve a group of information, but even that performance can be enhanced through the use of getrows where ever possible. Recordsets should be opened as late as possible and closed as quickly as possible, which is why getrows is a very helpful concept to learn.

    If this doesn't give you enough information, give me some more specific questions and I'll try to help you out.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •