Using SQL Against Resultset


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Using SQL Against Resultset

Hybrid View

  1. #1
    Faina Guest

    Using SQL Against Resultset


    Does anybody know if I can open a resultset and have a resultset as a data
    source? For example:

    I open first resultset as
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * from psivert;"

    Then I would like to create a second resultset against rs that was just created.If
    this is possible can I see the code sample
    please?

    The reason I need to open a second recordset against first recordset is described
    below. If there are any other ideas on how to handle it please let me know.

    I have to write an application that needs to recalculate spread
    control on the screen at the same speed Excel does it(immediately), save
    changes to the local Access 97 database and
    refresh a second screen(2 screen has to be open at the same time)
    to reflect changes from the first screen. First screen is a
    detail level screen and 2nd screen is a summary screen.
    If I can do all changes against resultsets in memory instead of DB it should
    be much faster(I think). Also in order to
    do what I need to do I should be able to run different SQL functions against
    resultset in memory(sum, group by etc). Then
    at predetermined time intervals I will save these resultsetsets
    to the Access DB.

    Thanks for any suggestions

  2. #2
    Andrew Grillage Guest

    Re: Using SQL Against Resultset

    You cannot get a recordset from a recordset. The best solution is to have
    multiple recordsets for what you want to do.

    --

    Andrew Grillage
    http://www.concresco.com


    Faina <Flekakh@kraft.com> wrote in message news:3903c4ef$1@news.devx.com...
    >
    > Does anybody know if I can open a resultset and have a resultset as a data
    > source? For example:
    >
    > I open first resultset as
    > rs.CursorType = adOpenKeyset
    > rs.LockType = adLockOptimistic
    > rs.CursorLocation = adUseClient
    > rs.Open "SELECT * from psivert;"
    >
    > Then I would like to create a second resultset against rs that was just

    created.If
    > this is possible can I see the code sample
    > please?
    >
    > The reason I need to open a second recordset against first recordset is

    described
    > below. If there are any other ideas on how to handle it please let me

    know.
    >
    > I have to write an application that needs to recalculate spread
    > control on the screen at the same speed Excel does it(immediately), save
    > changes to the local Access 97 database and
    > refresh a second screen(2 screen has to be open at the same time)
    > to reflect changes from the first screen. First screen is a
    > detail level screen and 2nd screen is a summary screen.
    > If I can do all changes against resultsets in memory instead of DB it

    should
    > be much faster(I think). Also in order to
    > do what I need to do I should be able to run different SQL functions

    against
    > resultset in memory(sum, group by etc). Then
    > at predetermined time intervals I will save these resultsetsets
    > to the Access DB.
    >
    > Thanks for any suggestions




  3. #3
    Paul Dionne Guest

    Re: Using SQL Against Resultset

    This idea has come up a few times here in the newsgroups and nobody has come
    up with any sort of recommendation on how to do it.

    Work around ideas have been 1) FILTER, 2) creating a second recordset based
    on the same source as the original 3) looping through the recordset and
    writing the values you want to a second recordset (or somewhere else) 4)
    nested queries and 5) write the recordset to a temporary table and query
    that table

    Paul

    Faina wrote in message <3903c4ef$1@news.devx.com>...
    >
    >Does anybody know if I can open a resultset and have a resultset as a data
    >source? For example:





  4. #4
    Kamran Mahbub Guest

    Re: Using SQL Against Resultset


    Of course that is not possible but you can programmatically create an ADO
    recordset (or load the main recordset w/o any records if you need it to be
    empty) or CLONE the main recordset as many times as you need. Then you can
    use these copies of the rs for calculations or to save them locally. And
    it does not matter even if you change the data in these clones (for temporary
    use) as far as they are disconnected.

    Kamran


    "Faina" <Flekakh@kraft.com> wrote:
    >
    >Does anybody know if I can open a resultset and have a resultset as a data
    >source? For example:
    >
    >I open first resultset as
    > rs.CursorType = adOpenKeyset
    > rs.LockType = adLockOptimistic
    > rs.CursorLocation = adUseClient
    > rs.Open "SELECT * from psivert;"
    >
    >Then I would like to create a second resultset against rs that was just

    created.If
    >this is possible can I see the code sample
    >please?
    >
    >The reason I need to open a second recordset against first recordset is

    described
    >below. If there are any other ideas on how to handle it please let me know.
    >
    >I have to write an application that needs to recalculate spread
    >control on the screen at the same speed Excel does it(immediately), save
    >changes to the local Access 97 database and
    >refresh a second screen(2 screen has to be open at the same time)
    >to reflect changes from the first screen. First screen is a
    >detail level screen and 2nd screen is a summary screen.
    >If I can do all changes against resultsets in memory instead of DB it should
    >be much faster(I think). Also in order to
    >do what I need to do I should be able to run different SQL functions against
    >resultset in memory(sum, group by etc). Then
    >at predetermined time intervals I will save these resultsetsets
    >to the Access DB.
    >
    >Thanks for any suggestions



  5. #5
    Simon Rawlings, MCSD Guest

    Re: Using SQL Against Resultset


    One of the best ways to do this summary detail stuff is to use
    ADO shaped recordsets. These shaped structures give a hierarchical structure
    to the recordsource, and can also be linked to
    a MSHFlexgrid control.

    There is plenty of examples in MSDN on this stuff. Try
    Q196029 for a simple example populating a MSHflexgrid.

    Another article gives an overall description of what you can do gives you
    different classifications of ways of using this. Out of the three types,
    the one you probably want is (Cut from http://msdn.microsoft.com/library/te...n_newado20.htm)

    Parameterized command hierarchies. A parameter hierarchy is similar to a
    relation hierarchy. The main difference is that instead of downloading all
    of the records for both the parent and child recordsets, the parents are
    downloaded, and then the child records are downloaded as they are accessed
    by the consumer application. This has the benefit of a shorter initial query
    time, but the application must be connected to the server for new child records
    to be accessed.

    Don't be too put of by the syntax, it looks harder than it is.

    SR

    "Kamran Mahbub" <kmahbub@att.net> wrote:
    >
    >Of course that is not possible but you can programmatically create an ADO
    >recordset (or load the main recordset w/o any records if you need it to

    be
    >empty) or CLONE the main recordset as many times as you need. Then you can
    >use these copies of the rs for calculations or to save them locally. And
    >it does not matter even if you change the data in these clones (for temporary
    >use) as far as they are disconnected.
    >
    >Kamran
    >
    >
    >"Faina" <Flekakh@kraft.com> wrote:
    >>
    >>Does anybody know if I can open a resultset and have a resultset as a data
    >>source? For example:
    >>
    >>I open first resultset as
    >> rs.CursorType = adOpenKeyset
    >> rs.LockType = adLockOptimistic
    >> rs.CursorLocation = adUseClient
    >> rs.Open "SELECT * from psivert;"
    >>
    >>Then I would like to create a second resultset against rs that was just

    >created.If
    >>this is possible can I see the code sample
    >>please?
    >>
    >>The reason I need to open a second recordset against first recordset is

    >described
    >>below. If there are any other ideas on how to handle it please let me know.
    >>
    >>I have to write an application that needs to recalculate spread
    >>control on the screen at the same speed Excel does it(immediately), save
    >>changes to the local Access 97 database and
    >>refresh a second screen(2 screen has to be open at the same time)
    >>to reflect changes from the first screen. First screen is a
    >>detail level screen and 2nd screen is a summary screen.
    >>If I can do all changes against resultsets in memory instead of DB it should
    >>be much faster(I think). Also in order to
    >>do what I need to do I should be able to run different SQL functions against
    >>resultset in memory(sum, group by etc). Then
    >>at predetermined time intervals I will save these resultsetsets
    >>to the Access DB.
    >>
    >>Thanks for any suggestions

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center