Solved

QUERY Question

Posted on 2004-09-08
14
151 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
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 17

Expert Comment

by:Wim ten Brink
Comment Utility
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
Comment Utility
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
Comment Utility
Workshop_Alex: You can only order by CUST_ID that way.
0
 
LVL 12

Expert Comment

by:esoftbg
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 17

Expert Comment

by:Wim ten Brink
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now