Tables and Queries - Design Question - Or Bug

[johnp]

Hello,

 

I am using uniGUI with Delphi XE, DBISAM and FastReports. I have all my data table and query components in a data module that each form can refer to. The problem that I have is that I can run one query off a table set with many types of queries using various SQL text  for my query.  Works fantastic. 

 

However, I cannot seem to display any data in a second grid on an existing form where I use tabbed pages, nor can I seem to get the results of this second query to display on a grid on a new form with a new grid or into any DB labels or DB edit boxes. I can show in a unilabel how many rows have been affected or the number of rows that are returned from my query.  But I cannot see any data.

 

I am wondering what I may be missing or if there are some limitations on using queries, forms, or grids in a project. My projects has about 14 forms. On each form I would like to display data from different queries generated from different tables. If I use a table then it is OK,  but not a query.  Not sure what I may be missing when one works but not the other, or maybe this is a bug.

 

John P.

[Farshad Mohajeri]

Please give more details about your design. Are you using DBISAM in server mode or in normal mode?

 

What do you mean by 2nd query? Can you display data from 1st query?

BTW, make sure you call Open() when running queries. Calling ExecSQL() may not return a dataset.

 

Finally, can you prepare a  test case for your issue?

[johnp]

Hi Farshad,

 

1. I am using DBISAM in client-server mode.

 

2. The second query is from a different query component accessing the same table and I was trying to join two tables in a view but did try just with one table on that other query component (not text) I am referring to. On my first query component I do use

many different SQL commands and they all do seem to reset OK when  I add many different SQL commands or text on my first query as seen below.  My first Query is called QueryWaybill. My second Query component is called QueryRoutes. They are also setup in the very same way as seen below. 

 

On another note I cannot use Select * in my SQL text. I have to use Select with the column names as below, which I assumed the way it had to be. Can you comment on this.

 

 

Var
RecNumbers: Integer;
begin
DA3Datamodule.QryWaybill.SQL.clear;

DA3DataModule.QryWaybill.SQL.Add ('SELECT Route,Serv_type,ShipmentId,Custcode,Ref,PCE,WGT,Receiver,ShipAddress1,ShipAddress2,'+
'ShipCity,ShipState,ShipPostalCode,SpecialInstructions,Origin,OriginAddress,OriginCity,OriginPostal,Status, cast(Orderdate as date)'+ 'from shiporders where custcode ='+quotedstr(UniLabelCustcode.Text)+ (' and orderdate >= :FirstDate')+ (' and orderdate <= :LastDate')+(' AND Extract(Hour from orderdate)') + ' between 0 and 24');

DA3Datamodule.QryWaybill.ParamByName ( 'FirstDate' ).Asdate;
DA3Datamodule.QryWaybill.ParamByName ( 'LastDate' ).Asdate;

   DA3Datamodule.QryWaybill.Active := false;
   DA3Datamodule.QryWaybill.Prepare;
   DA3Datamodule.QryWaybill.Close;

   DA3Datamodule.QryWaybill.Params[0].AsDate := Now;
   DA3Datamodule.QryWaybill.Params[1].AsDate := Now+1;

  Try
DA3Datamodule.QryWaybill.Active := true;
DA3Datamodule.QryWaybill.Open;

   RecNumbers :=  DA3Datamodule.QryWaybill.recordcount;
  UniLabelRecords.text := inttostr(RecNumbers) + (' records found for today');
     Except
      On E:Exception do
     Begin
        ShowMessage(e.Message);

       Exit;
     End;
 end

3. I will try with just a couple of forms or break into two first. And if it is still a problem I will send over. In the meantime I was just trying to see if anyone else had any similar issues.

 

Thanks for your great support.

 

John P.

Track Information Systems - Canada

[Sherzod]

Hi John,

 

Sorry maybe I misunderstood the question, but I think in any case, you must use the following sequence with repeated queries:

...
DA3Datamodule.QryWaybill.Close;
DA3Datamodule.QryWaybill.SQL.Clear;
DA3Datamodule.QryWaybill.SQL.Add('');
DA3Datamodule.QryWaybill.Prepare;
DA3Datamodule.QryWaybill.ParamByName('param').as ...;
DA3Datamodule.QryWaybill.Open;
...

Best regards.

[rasaliad]

My Comment on point 2 is that you have unnecessary code, i will do it in this way:

Var
    iRecNumbers: Integer;
begin

    DM.qrWaybill.Close;
    DM.qrWaybill.SQL.Text := 'SELECT Route                   ' +
                             '     , Serv_type               ' +
                             '     , ShipmentId              ' +
                             '     , Custcode                ' +
                             '     , Ref                     ' +
                             '     , PCE                     ' +
                             '     , WGT                     ' +
                             '     , Receiver                ' +
                             '     , ShipAddress1            ' +
                             '     , ShipAddress2            ' +
                             '     , ShipCity                ' +
                             '     , ShipState               ' +
                             '     , ShipPostalCode          ' +
                             '     , SpecialInstructions     ' +
                             '     , Origin                  ' +
                             '     , OriginAddress           ' +
                             '     , OriginCity              ' +
                             '     , OriginPostal            ' +
                             '     , Status                  ' +
                             '     , cast(Orderdate as date) ' + 
                             '  FROM shiporders              ' +
                             ' WHERE custcode = ' + quotedstr(UniLabelCustcode.Text) + 
                             '   AND orderdate >= :FirstDate ' + 
                             '   AND orderdate <= :LastDate  ' + 
                             '   AND Extract(Hour from orderdate) BETWEEN 0 AND 24 ';
   DM.qrWaybill.Prepare;

   DM.qrWaybill.ParamByName ( 'FirstDate' ).Asdate := Date;
   DM.qrWaybill.ParamByName ( 'LastDate' ).Asdate := Date + 1;
   
   DM.qrWaybill.Open;

   iRecNumbers :=  DM.qrWaybill.recordcount;
   
   UniLabelRecords.text := Format( '%d records found for today',[iRecNumbers]);
 end; 

it will not help in your problem, but i will help in future maintenance and to other programer understand your code. BTW the datasource are all assigned correctly?.

[johnp]

Well that looks nice. Usually I do clean up  my code after I get it working but not as nice as you presented. I will be checking everything again, and will try different data sources.

 

Thanks.

 

John P.

[Abaksoft]

Hello John P,

 

Are you using classical Delphi components (DBGrid, DBEdit,...)

Instead of : UniDBGrid, UniDbEdit, Uni...?

 

In Unigui application, use only unigui components :-)

That could be the problem !

 

Regards...

[johnp]

Not sure how to explain this one. I rebuilt my data module with all the table table and query components that I use and it now works. I was using Uni.. grids, labels, edits to begin with.   My project has become quite complex. I am thinking something in a data source was not quite right as was suggested. 

 

Thanks for all the feedback.

 

John P.