Retrieving Data

[Nathan]

I need to be able to retrieve data from a database 1 to 50 records at a time. I want to retrieve for example 50 records from a database. Later I want to retrieve the next 50 records from the database that match the sql string for the first 50 etc...

does anyone have any ideas? If this isn't clear let me know and I'll try to clarify what I want here....

[simonm]

I think I understand what you require. There are several ways to do that.

1) You could create a temporary table that identifies all records that meet the specified criteria.
Each time you retrieve records from your database, delete the identifiers from the temporary table.

or, if all the records might not be present when the temporary table is created;

2) Keep a temporary table of the records you have so far retrieved so you don't retrieve the same record twice.

[Nathan]

thanx... I was hoping to do it without a temporary table but I might just have to do it that way...

[simonm]

Adding a field to the table you're retrieving from to flag once they have been retrieved (and therefore excluded from future retrievals).

You need some way to record which records have been retrieved and which haven't.

[Nathan]

actually I'm working on another idea of creating an array of recordsets that would be clones of each page. Then when page one was read it would be stored in the array place 1 then when page 2 was read it would be stored in the array place 2 etc... this would make it a lot easier to retrieve the previous pages... what do you think about this idea?

[simonm]

Sounds like it would work but personally, I'm not that keen on arrays.

It may make it easier to retrieve previously read records but not un-read records.

If you really don't want to use a temporary table you could keep a string of identifiers (each seperated by a comma) that you could maintain by adding to as you read records. You could then use it to retrieve unread records by something like: 'SELECT * FROM [SOMEWHERE] WHERE ID NOT IN (ID1, ID2, ID3...)'

[Nathan]

do you have any idea what the speed would be like for using an 'IN' in the SQL for a SQL Server database? would it get really slow if for example it were to have 500 or more elements in the 'IN'?

[RIVES]

Have you ever thought of using cursors?

[Nathan]

could you explain more please?

[simonm]

I'm not entirely sure how efficient it is but I know using the 'IN' keyword with a comma-seperated list is faster than using it with a nested query (That has to be re-calculated each time).

As for 'RIVES' suggestion on using cursors, that could also be a good way of doing it. Open a recordset of all the records you will ever want to retrieve with an additional, constructed, field to flag whether a record has been viewed or not.

[Nathan]

I only want to retrieve a certain amount from the database. Ie. if the person never leaves page 1 (50 records) then all I'll ever retrieve is 50 records but if they go to page 2 then I want to retrieve only the next 50 records from the database. I don't want to retrieve everything at any point... Only the current 50 records that I'm working with...