RecordSet.Move or RecordSet.AbsolutePosition??

[Hung Huynh]

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

[Bob Barrows]

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]

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

[Aaron Bertrand - MVP]

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

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" <aaron@TRASHaspfaq.com> wrote in message
news:OSBAufhgDHA.2580@tk2msftngp13.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]

Thanks. I changed the CursorType and it works now.

HH

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23tPft$ggDHA.2484@TK2MSFTNGP09.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
>
>

[Aaron Bertrand - MVP]

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

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" <aaron@TRASHaspfaq.com> wrote in message
news:u$wwlJigDHA.2328@TK2MSFTNGP09.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...
>
>

[Bob Barrows]

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