Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

QUERY Question

Posted on 2004-09-08
14
Medium Priority
?
163 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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 120 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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 lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

782 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