RecordSet.Move or RecordSet.AbsolutePosition??

Discussion in 'ASP General' started by Hung Huynh, Sep 23, 2003.

  1. Hung Huynh

    Hung Huynh Guest

    Hi,

    I'm trying to use either one of these methods to position the cursor in a
    specific position inside a recordset, but neither one seems to work.

    For example, I have 10 records in my rsData recordset, and I issue this
    command

    rsData.Move 5, 1 'move to #5 from beginning

    I then retrieve rsData("field_name"), expecting that it'd return field_name
    of record #5, but it doesn't seem to work like that.

    What am I missing? Is there a restriction on what type of cursor a recordset
    must be opened in?

    I'm using W2K/IIS5/ASP.

    Thanks!

    HH
    Hung Huynh, Sep 23, 2003
    #1
    1. Advertising

  2. Hung Huynh

    Bob Barrows Guest

    Hung Huynh wrote:
    > Hi,
    >
    > I'm trying to use either one of these methods to position the cursor
    > in a specific position inside a recordset, but neither one seems to
    > work.
    >
    > For example, I have 10 records in my rsData recordset, and I issue
    > this command
    >
    > rsData.Move 5, 1 'move to #5 from beginning
    >
    > I then retrieve rsData("field_name"), expecting that it'd return
    > field_name of record #5, but it doesn't seem to work like that.


    What happens? Error message? Wrong record?
    What record do you expect to get to when you move 5 records past the first
    record (hint: 1 + 5 = 6)? I think you really want to use AbsolutePosition
    here.

    >
    > What am I missing? Is there a restriction on what type of cursor a
    > recordset must be opened in?
    >

    Yes. In order to use the second argument, you must open a bookmarkable
    cursor. Usually a static, keyset or dynamic cursor will work (forward only
    will never work), but it can depend on the Provider you are using..One way
    to make sure is to check the Supports method:
    if rsData.Supports(adBookmark) then

    You should be aware that the second argument in the Move method requires
    ether a bookmark or a BookmarkEnum value. In your case, you are using "1",
    which corresponds to vbBookmarkFirst, so you should get the desired result.
    If you try using "2" you may be surprised at the result because "2"
    corresponds to vbBookmarkLast.

    HTH,
    Bob Barrows
    Bob Barrows, Sep 23, 2003
    #2
    1. Advertising

  3. Hung Huynh

    Bob Barrows Guest

    Just wanted to add: instead of scrolling through a cursor, you may wish to
    consider using a GetRows array instead. It will perform much better, you
    will be able to use the cheapest fastest cursor to populate it, and you will
    be able to close your recordset and connection sooner, which is a good
    thing.

    Bob Barrows
    Bob Barrows, Sep 23, 2003
    #3
  4. > I'm trying to use either one of these methods to position the cursor in a
    > specific position inside a recordset, but neither one seems to work.


    Why don't you get that record (or whatever subject you're using) via a WHERE
    clause??? Why bother bringing back rows if you're not going to look at
    them?
    Aaron Bertrand - MVP, Sep 23, 2003
    #4
  5. Hung Huynh

    Hung Huynh Guest

    Well, I'm not just bringing back 1 record from query, so I can't just do a
    WHERE condition.

    My situation is this. I select 10 columns, with x number of rows and store
    in recordset. I need to translate rs("title") to a different language, sort
    by translated title, then display everything back (all 10 columns in the
    order of sorted Title)

    There's no way to pre-sort this translated Title from query by any means.
    So, I do a regular query to retrieve all rows.

    1. extract rs("title") from recordset to an array
    2. translate rs("title") and sort the array, then move this into Dictionary
    Object
    3. now, the challenge is to display the recordset in the order of the sorted
    dictionary. There's no way I can think of to match sorted dictionary with
    the recordset, other than the Key index of the Dict. So, I loop through dict
    and display like this:

    For Each K in Dict
    ' K = 12, 4, 0, 2, 5....
    rs.Move K, 1
    'move to record K of RS recordset
    'now I can call
    RS("col1"), RS("col2")....Dict(F)....RS("colx")
    Next


    This actuall does the trick. I just couldn't get RS.Move to work because I
    had a wrong CursorType. Everything is working now. Thanks Bob for your
    reply.

    If you have other ways of accomplishing what I describe above, please
    advice.

    I also thought there should be a way to update RS.("title") =
    Translated_Title, then I can call RS.Sort Title ...but I don't think I can
    update the recordset in this manner.

    HH


    "Aaron Bertrand - MVP" <> wrote in message
    news:...
    > > I'm trying to use either one of these methods to position the cursor in

    a
    > > specific position inside a recordset, but neither one seems to work.

    >
    > Why don't you get that record (or whatever subject you're using) via a

    WHERE
    > clause??? Why bother bringing back rows if you're not going to look at
    > them?
    >
    >
    Hung Huynh, Sep 23, 2003
    #5
  6. Hung Huynh

    Hung Huynh Guest

    Thanks. I changed the CursorType and it works now.

    HH

    "Bob Barrows" <> wrote in message
    news:%23tPft$...
    > Just wanted to add: instead of scrolling through a cursor, you may wish to
    > consider using a GetRows array instead. It will perform much better, you
    > will be able to use the cheapest fastest cursor to populate it, and you

    will
    > be able to close your recordset and connection sooner, which is a good
    > thing.
    >
    > Bob Barrows
    >
    >
    Hung Huynh, Sep 23, 2003
    #6
  7. > My situation is this. I select 10 columns, with x number of rows and store
    > in recordset. I need to translate rs("title") to a different language,

    sort
    > by translated title, then display everything back (all 10 columns in the
    > order of sorted Title)
    >
    > There's no way to pre-sort this translated Title from query by any means.


    Why? You can't have computed columns in SQL Server, or a view, or even a
    regular column that you update before you select (or on a periodic interval)
    so that the foreign language title is also stored in the database? Then
    your query could order by that column...
    Aaron Bertrand - MVP, Sep 23, 2003
    #7
  8. Hung Huynh

    Hung Huynh Guest

    No, we don't store foreign Titles in table, only the English version is
    stored. So, all translations have to be done after.

    I tried to convince my boss that we should just translate and store foreign
    titles once. But since the translation is fairly fast in real-time, he wants
    the flexibility of changing an English titles anytime, then foreign Titles
    will reflect right away.

    HH

    "Aaron Bertrand - MVP" <> wrote in message
    news:u$...
    > > My situation is this. I select 10 columns, with x number of rows and

    store
    > > in recordset. I need to translate rs("title") to a different language,

    > sort
    > > by translated title, then display everything back (all 10 columns in the
    > > order of sorted Title)
    > >
    > > There's no way to pre-sort this translated Title from query by any

    means.
    >
    > Why? You can't have computed columns in SQL Server, or a view, or even a
    > regular column that you update before you select (or on a periodic

    interval)
    > so that the foreign language title is also stored in the database? Then
    > your query could order by that column...
    >
    >
    Hung Huynh, Sep 24, 2003
    #8
  9. Hung Huynh

    Bob Barrows Guest

    Hung Huynh wrote:
    > So, I loop through dict and display like this:
    >
    > For Each K in Dict
    > ' K = 12, 4, 0, 2, 5....
    > rs.Move K, 1
    > 'move to record K of RS recordset
    > 'now I can call
    > RS("col1"), RS("col2")....Dict(F)....RS("colx")
    > Next
    >
    >
    > This actuall does the trick. I just couldn't get RS.Move to work
    > because I had a wrong CursorType. Everything is working now. Thanks
    > Bob for your reply.
    >
    > If you have other ways of accomplishing what I describe above, please
    > advice.


    I still think you could benefit from using a GetRows array, which will
    perform better than a recordset.


    >
    > I also thought there should be a way to update RS.("title") =
    > Translated_Title, then I can call RS.Sort Title ...but I don't think
    > I can update the recordset in this manner.
    >

    You can if you disconnect the recordset:
    rs.open ...
    set rs.activeconnection = nothing
    'close your connection, it is no longer needed

    Now you can update the recordset without affecting the data in your
    database. You will then be able to use the recordset's Sort property,
    allowing you to avoid the Dictionary object.

    HTH,
    Bob Barrows
    Bob Barrows, Sep 24, 2003
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ajit
    Replies:
    0
    Views:
    385
  2. Otuatail

    Recordset Move Next

    Otuatail, Dec 17, 2003, in forum: HTML
    Replies:
    4
    Views:
    9,626
    Augustus
    Dec 19, 2003
  3. Eduardo78
    Replies:
    0
    Views:
    241
    Eduardo78
    Nov 3, 2005
  4. mad
    Replies:
    0
    Views:
    215
  5. Andrew Tomazos
    Replies:
    2
    Views:
    594
    Nobody
    Dec 12, 2011
Loading...

Share This Page