Add a blank row, then populate using a dialog in Delphi/DataSnap/dbExpress

Question

I'm planning to create a dialog that would create a blank record in a Firebird database, then populate the fields with values taken from text edit boxes in that dialog.

I am using the following: dbExpress DataSnap Delphi XE2

Maybe it should go like this:

DataSource.DataSet.Append;
(call the dialog with the db text boxes pointing to DataSource)
if ModalResult = mrOK then
    DataSource.DataSet.Post;

Any ideas? I'd like to know which components to use (ClientDataSet, or SQLQuery or SQLDataSet). I've no idea on how to go about this.

EDIT: Code formatting

Answer 1

You might want to allow for a cancel as well...

DataSource.DataSet.Append;
(call the dialog with the db text boxes pointing to DataSource)
if ModalResult = mrOK then
    DataSource.DataSet.Post
else 
    Datasource.Dataset.cancel;

I use TADOQuery components with MS-SQL and it works reliably.

Answer 2

In other words, you want to create a dialog with non-data aware controls. In order to do this, you need three TSQLQueries: one to retrieve data when entering the dialog (in case of editing the data), one to insert and one to update.

Here is some (edited) code from such a dialog which I wrote the other day. The parameter 'n' is the id of the tuple to be edited; its value will be -1 if I am inserting a new record.

Function TEditCashbox.Execute (n: longint): boolean;
var
 q: TSqlQuery;

begin
 if n = -1 then
  begin
   edDate.Text:= datetostr (date);
   edAmount.text:= '0';
  end
 else with qGetCashbox do
  begin
   params[0].asinteger:= n;
   open;
   edDate.text:= fieldbyname ('curdate').asstring;
   edAmount.text:= fieldbyname ('amount').asstring;
   edDetails.text:= fieldbyname ('details').asstring;
   close
  end;

 if showmodal = mrOK then
  begin
   if n = -1 then
    begin
     q:= qInsertCashbox;
     q.ParamByName ('p0').asinteger:= dm.GenerateID ('cashbox')
    end
   else
    begin
     q:= qUpdateCashbox;
     q.ParamByName ('p0').asinteger:= n
    end;

  with q do
   begin
    parambyname ('p1').asdate:= strtodate (edDate.text);
    parambyname ('p2').asinteger:= strtoint (edAmount.Text);
    parambyname ('p3').asstring:= edDetails.text;
    execsql
   end;
  end
end.

qGetCashbox is a query defined as select curdate, amount, details from cashbox where id = :p1

qInsertCashbox is insert into cashbox (id, curdate, amount, details) values (:p0, :p1, :p2, :p3)

qUpdateCashbox is update cashbox set curdate = :p1, amount = :p2, details = :p3 where id = :p0

Of course, you could also use data aware components, which require the 'trinity' - TSQLDataSet, TDataSetProvider and TClientDataSet. Using data aware components is easier, but sometimes there are cases in which this approach is not suitable. If you do use data aware components, then my template code is as follows

sdsEditDeposit.params[0].AsInteger:= n;   // this is the TSQLDataSet
with qEditDeposit do    // this is the clientdataset
 begin
  open;
  if n = -1 then        // new tuple
   begin
    insert;
    fieldbyname ('amount').asinteger:= 0;
    fieldbyname ('curdate').asdatetime:= date;
   end;

  edit;
  if showmodal = mrOK then
   begin
    if n = -1 then 
     begin
      n:= dm.GenerateID;
      fieldbyname ('id').asinteger:= n;
     end;
    result:= n;
    post;
    applyupdates (0)
   end
  else
   begin
    cancel;
    result:= 0
   end;
end;    

Answer 3

You need a TSQLConnection configured for your database and (to make it easy) a TSQLTable. Link the TSQLTable to the TSQLConnection and select the desired table in the TableName property. Next drop a TDataSetProvider onto the form and connect it with the TSQLTable. Now take a TClientDataSet, set its Provider to the TDataSetProvider and connect the DataSource to the TClientDataSet.

Remember: to actually write the data into the database you have to call TClientDataSet.ApplyUpdates.

More info here