Delphi - FireDAC events

Question

I have the following form in Delphi:

Note: I am using FireDAC database driver, and TDBGrid component.

Note: I am using test column names and data for this question. If you want, I can send you complete database tables, but they are in Serbian.

Here is a code snippet for Load values event (combo box), that works:

procedure TForm2.Button4Click(Sender: TObject);
begin
with DataModule1.FDQuery1 do
begin
 close;
 sql.Clear;
 sql.Text:='select distinct Department from protocols';
 open;

 DataModule1.FDQuery1.First;

 while not DataModule1.FDQuery1.Eof do
 begin
 ComboBox1.Items.Create.Add(DataModule1.FDQuery1['Department']);

 DataModule1.FDQuery1.Next;
 end;


end;

end;

For the View (records), the following is working:

procedure TForm2.Button2Click(Sender: TObject);
begin
DataModule1.FDQuery1.sql.Text:= ' select protocols.ID_Document, protocols.Document, protocols.ID_Customer, protocols.Customer, protocols.Department, protocols.Date, protocols.Protocol, protocols.ID_Registrator, protocols.Registrator '
+ ' from protocols '
+ ' inner join documents on documents.ID_Document=protocols.ID_Document '
+ ' inner join registrators on registrators.ID_Registrator=protocols.ID_Registrator '
+ ' inner join customers on customers.ID_Customer=protocols.ID_Customer '
+ ' inner join users on users.ID_User=protocols.ID_User ';


DataModule1.FDQuery1.open;

Now, I can't figure a way to implement Add new entry, Update (entry-record), Delete (entry-record). Could someone show how to implement these events?

Answer 1

Your code snippets seem to show that you are not quite going about this the right way, because:

a) You are using the same FDQuery for editing the user data as you are for populating ComboBox1.

b) As all the columns in the Select in your Button2Click come from your protocols table, it is not clear why you think you need any of the Inner Joins.

c) You will find it much easier to debug and continue writing your app if you start giving your components meaningful names, rather than ComboBox1, FDQuery1, etc.

d) What you have asked in your q suggests that you may not be familiar with how Delphi datasets, including TFDQuery work, because if you were, you wouldn't be asking the question in the first place. I think you imagine that you have to implement code to perform your Inserts, Updates and Deletions. In fact, the functionality to do these operations is built into TFDQuery and other TDataSet descendants which work with SQ tables to do these operations automatically. I'll explain how to do that below.

First try this

1. Rename your components like this

   FDQuery1 -> qProtocols ComboBox1 -> cbxDepartmente Button4 -> btnOpenProtocols Button2 -> btnLoadDepartments

2. Add an extra TFDQuery to your datamodule and call it, say, qDepartmentList

3. Change your code as shown below.

Code:

procedure TForm1.btnLoadDepartmentsClick(Sender: TObject);
begin
  // Re-written do avoid using "With ..."

  if DataModule1.qDepartmentList.Active then
    DataModule1.qDepartmentList.Close;

   DataModule1.qDepartmentList.Sql.Text := 'select distinct Department from protocols';
   DataModule1.qDepartmentList.Open;
  //  DataModule1.FDQuery1.First;  <_  You DON'T need First, because the call to Open does that

   cbxDepartmentList.Items.Clear;  // Clear the combo if it's already populated
   while  not DataModule1.qDepartmentList.Eof do begin
     // ComboBox1.Items.Create.Add(DataModule1.FDQuery1['Department']);
     //  You don't need ad should not call Create in the above

     cbxDepartmentList.Items.Add(DataModule1.qDepartmentList['Department']);
     DataModule1.qDepartmentList.Next;
   end;
end;

procedure TForm1.btnOpenProtocolsClick(Sender: TObject);
begin
  DataModule1.qProtocols.Sql.Text:= ' select protocols.ID_Document, protocols.Document, protocols.ID_Customer, protocols.Customer, protocols.Department, protocols.Date, protocols.Protocol, protocols.ID_Registrator, protocols.Registrator '
  + ' from protocols '
  + ' inner join documents on documents.ID_Document=protocols.ID_Document '
  + ' inner join registrators on registrators.ID_Registrator=protocols.ID_Registrator '
  + ' inner join customers on customers.ID_Customer=protocols.ID_Customer '
  + ' inner join users on users.ID_User=protocols.ID_User ';

  DataModule1.qProtocols.Open;
end;

At this point, pause and check that you can still compile your code OK. Please not the comment I've added about "With" - with causes far more trouble than it is worth.

Then:

4. Add the following ButtonClick handlers:

   procedure TForm1.btnInsertClick(Sender: TObject); begin DataModule1.qProtocols.Insert; end;

   procedure TForm1.btnSaveChangesClick(Sender: TObject); begin DataModule1.qProtocols.Delete; end;

   procedure TForm1.btnDeleteClick(Sender: TObject); begin DataModule1.qProtocols.Delete; end;

5. Look in the online help what the Insert, Post and Delete methods of TDataSet do.

6. At this point, we are nearly done. The only thing left to do is to handle moving the contents of the edit controls to and from qProtocols. If you have used the db-aware verion of TEdit, namely TDBEDit, the DBEdits will do both operations automatically. If you have used TEdits - which is a mistake, in my opinion, you will need to copy the data from the Text properties of the TEdits, and also cbxDepartmentList into the qProtocols record's fields. The best place to do that would probably be in the qProtocols AfterInsertEvent.

If you want me to add some code to use TEdits, I could, but you will save yourself a huge amount of time coding, debugging and maintaining your code if you use TDBEdits rather than TEdits, and TDBComboBox rather than TComboBox

I'll leave it to you to study the online help to work how to implement a CancelChanges function. Also, you may find it instructive to add a TDBNavigator to your form, connected to the same TDataSource as your DBGrid, because it shows how all the functionality of your TButtons, amd more, is wrapped up by it. As you'll see, it also automatically enables and disables certain of its speedbuttons depending on whether the dataset is being browsed or edited.