Solved

QUERY Question

Posted on 2004-09-08
14
158 Views
Last Modified: 2010-04-05
Hi folks,

I have a TABLE called CUSTOMERS which contains two fields, CUST_ID and CUST_NOME (it is a MS Access database).

It has almost 1000 records.

How can i implement this: when the user click in a button, he just see the fields 1 to 10; after, he click in a NEXT button, and see the fields 11 to 20; and after ..... until 991 to 1000.

Do you imagine how could i implement this?

I tried something with "Select First 10 * from CUSTOMERS", but i dont know how to continue the implementation.

Could you help me with this?

Will it be different if it was in MS SQL Server?

Best regards!!!
0
Comment
Question by:joelsilva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12013174
first: in a Query you put the following SQL:

SELECT CUST_ID, CUST_NOME FROM CUSTOMER

This will load all customer records. The let's say you are using TADOQuery (just for e.g.), set filter;

for counter := 1 to 100 do
  begin
    start := counter;
    end := counter + 10;
    ADOQuery1.Filter = ' CUST_ID >= ' + start + ' AND CUST_ID <= ' + end;
    ADOQuery1.Filtered := True;
  end;
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12013265
well, ivanov, this may only work, if the ID is continiously,
without any missed ID between (caused by deletion for ex.)

another possibilty (but not very performant) would be
to cache all ID into a Tstringlist (for ex.) and build then an sql-statement
on the fly with an in-clause containing 10 IDs

just as suggestion

meikl ;-)
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12013313
This query return 91-100.

select
  top 10 *
from
  customers
where
  cust_id not in (select top 90 cust_id from customers order by nome)
order by
  nome

If you change the "top 90" in the sub-query you can get other slices of data. It will not perform very fast though.

Regards Jacco

(MSSQL has no nice solution for slices in version 2000 hopefully Yukon will have a method. It would be very nice since all webapplications need slices like this.)

(In SQL Server you could write a StoredProcedure and you could pass start en end record number to it. The stored procedure would then just do a "select * from customers order by nome" skip start records and return end-start records. I don't know if this is possible in access, but I think it would perform a bit better then the solution above since it uses no subquery)
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12014671
select top 10 * from customers where CUST_ID > :CUST_ID order by CUST_ID.

A parameterized query. Use 0 when you first call this query. Then change the parameter CUST_to the highest CUST_ID in your current recordset to get the "next" page.

Thus, if your CUST_ID's are 1..14, 16, 18, 20..100 then the first time the parameter is 0, returning 1 to 10. The second time CUST_ID=10, returning 11 to 23 (3 records missing!) and the third time you use 23 as CUST_ID. and 33, 43, 53, 63, 73, 83. With 93 you'll only get 8 records and when you use 100, you won't get any more records.
0
 

Author Comment

by:joelsilva
ID: 12014687
Hi folks,

I dont have Delphi right here, but i will test tonight... I deeply thank you everybody.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12015270
Workshop_Alex: You can only order by CUST_ID that way.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12015334
Try this:

        T :=''
          + ' SELECT * FROM CHANGEDATE WHERE ID BETWEEN :PRM0 AND :PRM1'
          + '';
        AdoQuery1.Active := False;
        AdoQuery1.SQL.Text := T;
        ADOQuery1.Parameters.ParamByName(PRM0).Value := 1;
        ADOQuery1.Parameters.ParamByName(PRM1).Value := 10;
        AdoQuery1.Active := True;
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12016206
Well, then leave away the ODER BY at the end of the query. Sort it any way you like. As long as you select the next batch based on the highest ID in the current batch.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12017863
Workshop Alex: Then you get every batch sorted but not the "whole" query. Ideally if the current batch has alle names starting with C/D/E if you go a few batches further you can get K/L/M names.
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 12018963
Ofcourse you should use variables as BgnID and EndID:

        T :=''
          + ' SELECT * FROM CHANGEDATE WHERE ID BETWEEN :PRM0 AND :PRM1'
          + '';
        AdoQuery1.Active := False;
        AdoQuery1.SQL.Text := T;
        ADOQuery1.Parameters.ParamByName(PRM0).Value := BgnID;
        ADOQuery1.Parameters.ParamByName(PRM1).Value := EndID;
        AdoQuery1.Active := True;
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12018990
I have accomplished this by having my stored procedure that returns that data, defined as:

Create procedure test
(@LastId int = 0)
as
set rowcount 10 --this is the magic here!!! (simulates "paging")
select * from box where BoxPK>@LastId
order by BoxPK

then in the Delphi, hook up your ADOConnection, a ADOProcedure, DataSource, DataGrid, etc

your Open query code looks like:

  ADOStoredProc1.Open;

your "Next page" button looks like:
procedure TForm1.Button2Click(Sender: TObject);
var
  LastID:Integer;
begin
  ADOStoredProc1.Last;
  LastID:=ADOStoredProc1.fieldByName('BoxPK').AsInteger;
  ADOStoredProc1.Close;
  ADOStoredProc1.Parameters.ParamValues['@LastId']:=LastID;
  ADOStoredProc1.Open
end;

let me know if you have any questions
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12019026
sorry, if this is an ACCESS table, you just need to change the stored procedure to a local query

your query would be something like:

select top 10 * from table where TableID>:TableID order by TableID

then you can use the same method I described, only the first time you open the table, you need to send a TableID of 0

this way it doesn't matter if they delete records in the table, you're always getting 10 (or whatever) records in the next recordset
0
 

Author Comment

by:joelsilva
ID: 12019047
Hey guys, many ideas here, in this Post!

Thank you very much for your attention... As I said, i dont have Delphi right now, but tonight i will test each suggestion...

Best Regards!
0
 
LVL 13

Accepted Solution

by:
BlackTigerX earned 30 total points
ID: 12019130
Here's the full code with a database and fields definition as your example:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids, DBClient;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Button1: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button2: TButton;
    ADOQuery1: TADOQuery;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.Parameters.ParamValues['custid']:=-1;
  ADOQuery1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  LastID:Integer;
begin
  ADOQuery1.Last;
  LastID:=ADOQuery1.fieldByName('cust_id').AsInteger;
  ADOQuery1.Close;
  ADOQuery1.Parameters.ParamValues['custid']:=LastID;
  ADOQuery1.Open
end;

end.

and the form:

object Form1: TForm1
  Left = 192
  Top = 123
  Width = 358
  Height = 332
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  DesignSize = (
    350
    305)
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 64
    Top = 24
    Width = 75
    Height = 25
    Caption = 'Open'
    TabOrder = 0
    OnClick = Button1Click
  end
  object DBGrid1: TDBGrid
    Left = 16
    Top = 104
    Width = 321
    Height = 185
    Anchors = [akLeft, akTop, akRight, akBottom]
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Button2: TButton
    Left = 200
    Top = 24
    Width = 75
    Height = 25
    Caption = 'Next page'
    TabOrder = 2
    OnClick = Button2Click
  end
  object ADOConnection1: TADOConnection
    Connected = True
    ConnectionString =
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\test.mdb;Persist' +
      ' Security Info=False'
    LoginPrompt = False
    Mode = cmShareDenyNone
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    Left = 16
    Top = 16
  end
  object DataSource1: TDataSource
    DataSet = ADOQuery1
    Left = 72
    Top = 64
  end
  object ADOQuery1: TADOQuery
    Connection = ADOConnection1
    LockType = ltReadOnly
    Parameters = <
      item
        Name = 'custid'
        Attributes = [paNullable]
        DataType = ftInteger
        NumericScale = 255
        Precision = 255
        Size = 510
        Value = Null
      end>
    Prepared = True
    SQL.Strings = (
      'select top 10 * from customers'
      'where Cust_ID >:custid')
    Left = 128
    Top = 64
  end
end
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question