Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > RecordSet.Move or RecordSet.AbsolutePosition??

Reply
Thread Tools

RecordSet.Move or RecordSet.AbsolutePosition??

 
 
Hung Huynh
Guest
Posts: n/a
 
      09-23-2003
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



 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      09-23-2003
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


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      09-23-2003
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


 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      09-23-2003
> 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?


 
Reply With Quote
 
Hung Huynh
Guest
Posts: n/a
 
      09-23-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> > 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?
>
>



 
Reply With Quote
 
Hung Huynh
Guest
Posts: n/a
 
      09-23-2003
Thanks. I changed the CursorType and it works now.

HH

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:%23tPft$(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
Aaron Bertrand - MVP
Guest
Posts: n/a
 
      09-23-2003
> 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...


 
Reply With Quote
 
Hung Huynh
Guest
Posts: n/a
 
      09-24-2003
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" <(E-Mail Removed)> wrote in message
news:u$(E-Mail Removed)...
> > 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...
>
>



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      09-24-2003
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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off




Advertisments