Professional Web Applications Themes

RecordSet.Move or RecordSet.AbsolutePosition?? - ASP

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...

  1. #1

    Default RecordSet.Move or RecordSet.AbsolutePosition??

    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 Guest

  2. #2

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    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 Guest

  3. #3

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    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 Guest

  4. #4

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    > 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 Guest

  5. #5

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    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" <aaronTRASHaspfaq.com> wrote in message
    news:OSBAufhgDHA.2580tk2msftngp13.phx.gbl...
    > > 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 Guest

  6. #6

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    Thanks. I changed the CursorType and it works now.

    HH

    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:%23tPft$ggDHA.2484TK2MSFTNGP09.phx.gbl...
    > 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 Guest

  7. #7

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    > 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 Guest

  8. #8

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    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" <aaronTRASHaspfaq.com> wrote in message
    news:u$wwlJigDHA.2328TK2MSFTNGP09.phx.gbl...
    > > 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 Guest

  9. #9

    Default Re: RecordSet.Move or RecordSet.AbsolutePosition??

    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 Guest

Similar Threads

  1. Recordset is M.I.A.
    By jboyus in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 28th, 08:10 PM
  2. Need help with a recordset
    By Shane930 in forum Coldfusion Database Access
    Replies: 9
    Last Post: June 6th, 02:42 PM
  3. RECORDSET
    By NAIME ESTEVES in forum ASP.NET
    Replies: 10
    Last Post: February 21st, 03:52 PM
  4. Is this possible with a recordset?
    By harrillj in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 14th, 07:15 PM
  5. End Of Recordset!!
    By Adam Knight in forum ASP Database
    Replies: 1
    Last Post: September 14th, 11:13 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139