1 (edited by thezimguy 2018-08-07 17:18:07)

Topic: [SOLVED] TableGrid with Pagination and Offset

Please how do I create pagination for tableGrid using buttons as next, previous, 1,2,3..., end etc
So that on form load. First 500 records will be loaded and when I click the next button the next 500 records will load without the first 500

@thezimguy

Re: [SOLVED] TableGrid with Pagination and Offset

Hello.


Please download latest beta version, I have added new property dbOffSet for the TableGrid
https://www.dropbox.com/s/wutj7mnux7f3a … a.zip?dl=0


Script:

procedure Form1_OnShow (Sender: TObject; Action: string);
begin
    Form1.bSearch.Click;
end;

procedure Form1_bSearch_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := 0;
end;

procedure Form1_bNext_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet + 500;
    Form1.GridEmployees.dbUpdate;
end;

procedure Form1_bPrev_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet - 500;
    if Form1.GridEmployees.dbOffSet < 0 then Form1.GridEmployees.dbOffSet := 0;
    Form1.GridEmployees.dbUpdate;
end;

begin
    Form1.GridEmployees.dbLimit := 500;
end.

Project with the example:

Post's attachments

Attachment icon TableGrid pagination.zip 345.21 kb, 146 downloads since 2018-07-30 

Dmitry.

3 (edited by thezimguy 2018-07-31 10:51:34)

Re: [SOLVED] TableGrid with Pagination and Offset

Thank you so much boss
I'm really grateful
I will try it and communicate back

You are the best
Dmitry/MVD


It works like magic. You have really made it easy to load more data in the tablegrid

Guys check this new feature out. You will really love it

Don't forget to download the 4.6 beta] version Dmitry uploaded

Post's attachments

Attachment icon TableGrid pagination.png 14.61 kb, 33 downloads since 2018-07-31 

@thezimguy

4 (edited by thezimguy 2018-08-03 04:44:40)

Re: [SOLVED] TableGrid with Pagination and Offset

With the help of Dmitry
I have been able to add the pagination offset in a combo box so you can select the number of rows to display in the table Grid.
I'm really enjoying MVD.

Check this out

procedure Form1_OnShow (Sender: TObject; Action: string);
begin
    Form1.cbPages.ItemIndex:=4;////this will show the first 100 records
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    Form1.bSearch.Click;
end;

procedure Form1_bSearch_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbOffSet := 0;
end;             

procedure Form1_bNext_OnClick (Sender: TObject; var Cancel: boolean);
begin                                   
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet + StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbUpdate;
end;

procedure Form1_bPrev_OnClick (Sender: TObject; var Cancel: boolean);
begin
    Form1.GridEmployees.dbOffSet := Form1.GridEmployees.dbOffSet - StrToInt((Form1.cbPages.Text));
    Form1.GridEmployees.dbLimit := StrToInt((Form1.cbPages.Text));
    if Form1.GridEmployees.dbOffSet < 0 then Form1.GridEmployees.dbOffSet := 0;
    Form1.GridEmployees.dbUpdate;
end;

procedure Form1_cbPages_OnChange(Sender: TObject; var Cancel: boolean);
begin
    if (Form1.cbPages.ItemIndex =0) then
    begin
        Form1.cbPages.ItemIndex := 1;
    end;
end;
begin
    //Form1.GridEmployees.dbLimit := 500;
end.
Post's attachments

Attachment icon TableGrid pagination.zip 346.34 kb, 99 downloads since 2018-07-31 

@thezimguy

Re: [SOLVED] TableGrid with Pagination and Offset

This is the preview

Post's attachments

Attachment icon TableGrid pagination2.png 18.23 kb, 72 downloads since 2018-07-31 

@thezimguy

6 (edited by thezimguy 2018-12-31 10:01:32)

Re: [SOLVED] TableGrid with Pagination and Offset

Hello Dmitry,

procedure Form1_OnShow (Sender: string; Action: string);
begin
    Form1.TableGrid1.dbLimit := 10;
end;

procedure Form1_TableGrid1_OnChange(Sender: TObject; ACol, ARow: Integer);
var
    i : integer;
    rowFrom : integer = 0;
    rowTo : integer = 0;
    rowDBTotal : integer = 0;
begin
    rowFrom := Form1.TableGrid1.dbOffSet + 1;//rTo := Form1.TableGrid1.dbOffSet + Form1.TableGrid1.dbLimit;
    rowTo := Form1.TableGrid1.dbOffSet + Form1.TableGrid1.RowCount;
    //rowDBTotal := Form1.TableGrid1.dbTotalRows;//Total rows from database and not from tableGrid(not RowCount)

    Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(SQLExecute('SELECT COUNT(ID) FROM employees'));
    //so that instead of the above I will just do the following
    Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(rowDBTotal);
end;

Instead of

Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(SQLExecute('SELECT COUNT(ID) FROM employees'));

It will be

Form1.lblRows.Caption := IntToStr(rowFrom) + ' to ' + IntToStr(rowTo) + ' of ' +  IntToStr(rowDBTotal);

THE REASON FOR THIS IS I DONT WANT TO REQUERY THE DATABASE SINCE THE TABLEGRID HAS ALREADY QUERIED THE DATABASE ON UPDATE.
I mean that on tableGrid update or tableGrid load should come with the total rows from that query which should be attached to the tableGrid.
This will really help for the navigation through the table to be simplier and fast and will also reduce the number of queries to the database.
I hope to hear from you.
Regards

@thezimguy