my resultset object is having some records...I want to display 10 records per page.i want to put "next" and "previous" buttons in my page....
4 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   vijay_varanasi
Posted On:   Thursday, July 3, 2003 05:29 AM

my resultset object is having some records...I want to display 10 records per page.i want to put "next" and "previous" buttons in my page....

I have some problem with resultset object while doing the above operation...

Can anyone please provide me the code for the above operation....

Fancy SQL ...

Posted By:   Roger_Hand  
Posted On:   Friday, July 11, 2003 02:30 AM

See also my replies (especially the 2nd one) at retrieving n number of records from database ...

Re: my resultset object is having some records...I want to display 10 records per page.i want to put "next" and "previous" buttons in my page....

Posted By:   Rubber_Chicken  
Posted On:   Thursday, July 10, 2003 02:25 PM

There are ways to control the resultset size via the SELECT statement as well, the exact way to do it depends on your database:


-----------------------------


Oracle:


select your results and explicitly add the rownum to the result, then select against that and use a between clause on the rownum, like this:


SELECT TALIAS.* FROM (SELECT ROWNUM AS RWN, FROM (SELECT FROM table_name WHERE <....> ORDER BY <...>) TALIAS) WHERE TALIAS.RWN BETWEEN 1 AND 15


plug in your column names and table name, where conditions, etc. TALIAS is an statement-scope alias for the inner SELECTion result.


just plug in different values for the BETWEEN numbers and you can control the size of the result set and your overall window to it....


-----------------------------


MySQL:


much easier, use the limit keyword at the end of your query:


All rows:


SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 0, -1


first 10 rows:


SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 10


next 20 rows:


SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 11, 20


LIMIT begin-index, number-to-return (-1 is all remaining rows, so LIMIT 0, - 1 is the entire result set, while LIMIT 101, -1 would be all remaining results after the 100th)


---------------------------


HSQLDB:


also much easier, use the limit keyword at the beginning of your query, with no comma, and 0 in the second position means all remaining results:


all rows:


SELECT LIMIT 0 0 columnA, columnB FROM table_name WHERE columnA=condition


first 10 rows:


SELECT LIMIT 0 10 * FROM table_name WHERE columnA=condition


next 20 rows:


SELECT LIMIT 11 20 * FROM table_name WHERE ColumnA=condition


---------------------------


hope this helps -- I just ran into the same problem myself yesterday

Re: my resultset object is having some records...I want to display 10 records per page.i want to put "next" and "previous" buttons in my page....

Posted By:   Stephen_McConnell  
Posted On:   Thursday, July 3, 2003 09:07 AM

The only way you can do this is to create a bean that contains all the rows in a vector. Read all the rows into this bean, ans create an object that has the information for each row and put it into the Vector.


You will need a variable that keeps track of where you are in the vector.


Now you place this bean as a session variable and as you do a page forward or page backward, you respond to the "previous" or "next" button action by moving the cursor in the bean that is in Session backwards or forwards... Then display the 10 records you need.


Not a great way to do this, but depending upon what Database you are using, that's the only way.


Actually, you could have your rows indexed by a row-count and then in your query get those rows greater than an index value you have in either 1) a hidden input field 2) a session object.


Think about how you want do maintain session between calls.


Stephen McConnell

Re: my resultset object is having some records...I want to display 10 records per page.i want to put "next" and "previous" buttons in my page....

Posted By:   Steven_Martin  
Posted On:   Thursday, July 3, 2003 08:00 AM

Not sure what database you are using. This is the classic rownum problem. No one solution has been found for this. If your database supports telling you how many rows and row retrival windows then you are set. Otherwise, post your approach and happy to help.
About | Sitemap | Contact