RecordSet.Move or RecordSet.AbsolutePosition??

H

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
 
B

Bob Barrows

Hung said:
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
 
B

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
 
A

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?
 
H

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
 
A

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

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
 
B

Bob Barrows

Hung said:
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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,439
Members
44,829
Latest member
PIXThurman

Latest Threads

Top