Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

TClientDataSet refresh after Post

I'm using MySQL using the following arrangement:-

TSQLConnection -> TSQLTable1 -> TdatasetProvider1 -> TClientDataset1 -> TDatasource1
TSQLConnection -> TSQLTable2 -> TdatasetProvider2 -> TClientDataset2 -> TDatasource2

...to display, update, and edit records using the standard TDBGrid, TDBEdit components.  This all works fine, but if I want to maintain two different views onto the same table (as shown) and I update one of them, the data is not reflected in the other one until I restart the app.  

I am using Post followed by Applyupdates(0).
0
moorhouselondon
Asked:
moorhouselondon
  • 12
  • 10
1 Solution
 
AshokCommented:
Use following code at runtime to be able to refresh TClientDataset.

var
  DataSetProvider: TDataSetProvider;
begin
  dm.qryGraph.Open;
  DataSetProvider := TDataSetProvider.create(nil);
  DataSetProvider.DataSet := dm.qryGraph;
  dm.cdData.Close;
  dm.cdData.SetProvider(DataSetProvider);
  dm.cdData.Open;

HTH
Ashok
0
 
moorhouselondonAuthor Commented:
I have the Provider already connected to the ClientDataSet.  Are you suggesting I disconnect the Provider, close the ClientDataSet then reconnect the Provider?
0
 
AshokCommented:
For example, you are updating via TClientDataset1 and need to refresh TClientDataset2.

Do not set any properties of TClientDataset2 at design time.  Connect and disconnect at runtime.
Whenever you need to refresh, use the code I provided.

HTH
Ashok
0
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!

 
AshokCommented:
BTW, make sure you do not forget to free DataSetProvider at appropriate place.

  DataSetProvider.Free;

HTH
Ashok
         
0
 
moorhouselondonAuthor Commented:
That is not working for me, here's my code:-

dm1.cdscust.Post;
dm1.cdscust.ApplyUpdates(0);
dm1.cdscust2.Close;
dm1.cdscust2.SetProvider(dm1.provcustomeraddress2);
dm1.cdscust2.Open;
0
 
moorhouselondonAuthor Commented:
Whoops, slight mistake on copy and paste there

dm1.cdscust.Post;
dm1.cdscust.ApplyUpdates(0);
dm1.cdscust2.Close;
dm1.cdscust2.SetProvider(dm1.provcust2);
dm1.cdscust2.Open;
0
 
AshokCommented:
You also have to Close and Re-Open dm1.provcust2 first.
Then use above code.

HTH
Ashok
0
 
moorhouselondonAuthor Commented:
dm1.provcust2 is the provider - I can't just close and reopen that can I?  I have to free and create it again?  Which means that I cannot put it onto the Datamodule as a design time component?
0
 
AshokCommented:
        DataSetProvider:=TDataSetProvider.create(nil);
         dm.cdData.Close;
         DataSetProvider.DataSet := dm.qryItemAna;
         dm.cdData.SetProvider(DataSetProvider);

In above cdData is TClientDataSet and DataSetProvider is not any QUERY.
QUERY is connected to DataSetProvider.DataSet so you could close and re-open TDataSet.

HTH
Ashok
0
 
AshokCommented:
In may case dm.qryItemAna is TDataSet.

I hope you understand this.

HTH
Ashok
0
 
AshokCommented:
In my case dm.qryItemAna is TDataSet.

I hope you understand this.

HTH
Ashok
0
 
AshokCommented:
var
  DataSetProvider: TDataSetProvider;
begin
  dm.qryGraph.Open;
  DataSetProvider := TDataSetProvider.create(nil);
  DataSetProvider.DataSet := dm.qryGraph;
  dm.cdData.Close;
  dm.cdData.SetProvider(DataSetProvider);
  dm.cdData.Open;  // After openning this, you could close dm.qryGraph if you do not need it in the next statement.
  dm.qryGraph.Close;

HTH
Ashok
0
 
moorhouselondonAuthor Commented:
OK I think I'm going to build a test app where I create the Provider components on the fly, rather than putting them on to the form at design time.  I will let you know how I get on...
0
 
moorhouselondonAuthor Commented:
As promised I've created a test app to try out your ideas.  I'm having a problem creating components programmatically, which means I'm not yet at first base.  Please see this Q:-

http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_25013758.html
0
 
moorhouselondonAuthor Commented:
Sorted that one out!  I needed to define some fields in my clientdataset, silly me!
0
 
moorhouselondonAuthor Commented:
Here's my code

On my form I have two dbgrids, two dbnavigators, all attached to the respective datasources.  When I fire up the app and login to my database, I see the Id and Company columns in the dbgrid, but no records populated, even though there are in the underlying table.

If I use the navigator to create a new record, then attempt to post it and applyupdates, or if I refresh, I get an error "Missing Data Provider or data packet", where am I going wrong?
unit Unit2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, WideStrings, FMTBcd, SqlExpr, DB, Grids, DBGrids, Provider, DBClient,
  StdCtrls, ExtCtrls, DBCtrls;

type
  Tdm1 = class(TForm)
    ClientDataSet1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    SQLConnection: TSQLConnection;
    SQLTable1: TSQLTable;
    Button1: TButton;
    Edit1: TEdit;
    Edit2: TEdit;
    DBNavigator1: TDBNavigator;
    DBNavigator2: TDBNavigator;
    Button2: TButton;
    Button3: TButton;
    procedure Button1Click(Sender: TObject);
    procedure createeverything;
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  dm1: Tdm1;

implementation

{$R *.dfm}


var
  username,password:string;
  sqlcustomer1,sqlcustomer2:tsqltable;
  provcustomer1,provcustomer2:tdatasetprovider;
  cdscustomer1,cdscustomer2:tclientdataset;
  dscustomer1,dscustomer2:tdatasource;


procedure Tdm1.Button2Click(Sender: TObject);
begin
cdscustomer1.Post;
cdscustomer1.ApplyUpdates(0);
end;

procedure Tdm1.Button3Click(Sender: TObject);
begin
cdscustomer2.Post;
cdscustomer2.ApplyUpdates(0);
end;

procedure tdm1.createeverything;
begin
sqlcustomer1:=tsqltable.Create(nil);
sqlcustomer2:=tsqltable.Create(nil);

sqlcustomer1.SQLConnection:=sqlconnection;
sqlcustomer2.SQLConnection:=sqlconnection;

sqlcustomer1.TableName:='customer';
sqlcustomer2.TableName:='customer';

sqlcustomer1.Active:=true;
sqlcustomer2.Active:=true;

provcustomer1:=tdatasetprovider.Create(nil);
provcustomer2:=tdatasetprovider.Create(nil);

provcustomer1.Constraints:=true;
provcustomer1.Exported:=true;
provcustomer1.ResolveToDataSet:=false;
provcustomer1.UpdateMode:=upwhereall;

provcustomer2.Constraints:=true;
provcustomer2.Exported:=true;
provcustomer2.ResolveToDataSet:=false;
provcustomer2.UpdateMode:=upwhereall;

provcustomer1.Name:='customer1';
provcustomer2.Name:='customer2';

provcustomer1.DataSet:=sqlcustomer1;
provcustomer2.DataSet:=sqlcustomer2;

cdscustomer1:=tclientdataset.Create(nil);
cdscustomer2:=tclientdataset.Create(nil);

cdscustomer1.ProviderName:=provcustomer1.Name;
cdscustomer2.ProviderName:=provcustomer2.Name;

with Cdscustomer1.FieldDefs do
begin
  Clear;
  with AddFieldDef do
  begin
    Name := 'ID';
    DataType := ftInteger;
  end; //with AddFieldDef do
  with AddFieldDef do
  begin
    Name := 'company';
    DataType := ftString;
    Size := 30;
  end; //with AddFieldDef do
{  with AddFieldDef do
  begin
    Name := 'Date of Birth';
    DataType := ftDate;
  end; //with AddFieldDef do
  with AddFieldDef do
  begin
    Name := 'Active';
    DataType := ftBoolean;
  end; //with AddFieldDef do}
end; //with ClientDataSet1.FieldDefs

with Cdscustomer2.FieldDefs do
begin
  Clear;
  with AddFieldDef do
  begin
    Name := 'ID';
    DataType := ftInteger;
  end; //with AddFieldDef do
  with AddFieldDef do
  begin
    Name := 'company';
    DataType := ftString;
    Size := 30;
  end; //with AddFieldDef do
{  with AddFieldDef do
  begin
    Name := 'Date of Birth';
    DataType := ftDate;
  end; //with AddFieldDef do
  with AddFieldDef do
  begin
    Name := 'Active';
    DataType := ftBoolean;
  end; //with AddFieldDef do}
end; //with ClientDataSet1.FieldDefs


cdscustomer1.CreateDataSet;
cdscustomer2.CreateDataSet;

dscustomer1:=tdatasource.Create(nil);
dscustomer2:=tdatasource.Create(nil);

cdscustomer1.Active:=true;
cdscustomer2.Active:=true;

dscustomer1.DataSet:=cdscustomer1;
dscustomer2.DataSet:=cdscustomer2;

dbgrid1.DataSource:=dscustomer1;
dbgrid2.DataSource:=dscustomer2;
dbnavigator1.DataSource:=dscustomer1;
dbnavigator2.DataSource:=dscustomer2;

end;

procedure Tdm1.Button1Click(Sender: TObject);
begin
username:=edit1.Text;
password:=edit2.Text;
dm1.SQLConnection := TSQLConnection.Create(nil);
dm1.SQLConnection.DriverName := 'MySQL';
dm1.SQLConnection.LibraryName := 'dbexpmys30.dll';
dm1.SQLConnection.VendorLib := 'libmysql.dll';
dm1.SQLConnection.GetDriverFunc := 'getSQLDriverMYSQL';
dm1.SQLConnection.Params.Clear;
dm1.SQLConnection.Params.Add('DriverName=MySQL');
dm1.SQLConnection.Params.Add('HostName=192.168.0.2');
dm1.SQLConnection.Params.Add('Database=mss');
dm1.SQLConnection.Params.Add('User_Name='+username);
dm1.SQLConnection.Params.Add('Password='+password);
dm1.SQLConnection.Params.Add('BlobSize=1000');
dm1.SQLConnection.Params.Add('LocaleCode=0000');
dm1.SQLConnection.Params.Add('Compressed=False');
dm1.SQLConnection.Params.Add('Encrypted=False');
dm1.SQLConnection.Params.Add('connecttimeout=120');
dm1.SQLConnection.Params.Add('autoreconnect=1');
dm1.SQLConnection.Params.Values['FetchAll'] := 'True';
dm1.sqlconnection.loginprompt:=false;

dm1.SQLConnection.Open;
createeverything;

end;


end.

Open in new window

0
 
moorhouselondonAuthor Commented:
Aha, by putting this code in:-

cdscustomer1.Close;
cdscustomer1.SetProvider(provcustomer1);
cdscustomer1.Open;
cdscustomer2.Close;
cdscustomer2.SetProvider(provcustomer2);
cdscustomer2.Open;

I have records, and the datagrids populate.  Now presumably I have to put that code in to the post click code...
0
 
moorhouselondonAuthor Commented:
In my Customer1 Post button routine I put this code:-

cdscustomer1.Post;
cdscustomer1.ApplyUpdates(0);
cdscustomer2.Close;
cdscustomer2.SetProvider(provcustomer2);
cdscustomer2.Open;
cdscustomer2.Close;
cdscustomer2.SetProvider(provcustomer2);
cdscustomer2.Open;

This works exactly how I want it to (Points will go to to Ashok for that).  If I Close/Open once there is a lag.  If I do it twice then it's fine.  But why???  If someone can give me a rational explanation of what's going on then I will assign some points for that explanation.
0
 
AshokCommented:
cdscustomer2.SetProvider(provcustomer2);

Above line feeds data from ResultSet of provcustomer2.  I think in the first try data in the provcustomer2 is not refreshed.
When you try second time, provcustomer2 is getting new data.

That is why I was saying, close and re-open provcustomer2 then feed the data to cdscustomer2 (TClientDataSet).

cdscustomer1.Post;
cdscustomer1.ApplyUpdates(0);
provcustomer2.Close;
provcustomer2.Open;
cdscustomer2.Close;
cdscustomer2.SetProvider(provcustomer2);
cdscustomer2.Open;

I think above shoud work.  I do not have your project source code so I cannot exactly provide best solution without TESTING.

HTH
Ashok
0
 
moorhouselondonAuthor Commented:
Thanks Ashok.  This works a treat.
0
 
AshokCommented:
moorhouselondon,

I am glad it worked!
I have been programming in Delphi for last 15 years.  I feel like I am stuck with Delphi.  Trying to get into C# / Web applications.

Ashok
0
 
moorhouselondonAuthor Commented:
Well I'm glad that you are so knowledgeable about Delphi because there's no link on the web that I can find that gives the solution you did.
0

Featured Post

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!

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now