DBLookupComboBox in FireMonkey, or a crutch for a red monkey
Good day to all!
Not so long ago, faced with the need to work with a database from the FMX-application.
Those who have already “tested” Delphi XE should be aware of the absence of such favorite VCLs as FMX:
')
And if the problem with DBGrid is solved quite intuitively, with visual binding, then with TDBLoockupComboBox everything is not so simple.
In any case, Google could not tell me anything sensible.
I solved the problem; I want to share the decision with the community, maybe someone will come in handy.
So, let's start with the fact that we outline a demo project.
DB for simplicity we take SQLite.
Create a main table, secondary, and a table of relations between them.
Well and we throw a bit of records for the test.
tbl_main, tbl_status, main_st.
Next, prepare a project in Delphi.
The main form, the form of status change, the module for working with the database (DataModule).
On the form of the data module we throw the necessary components for working with data.
I prefer FireDAC, so I tipped TFDPhysSQLiteDriverLink, TFDGUIxWaitCursor, TFDConnection and three TFDQuery.
Put the TGrid on the main form, stretch it over the whole form.
We put TComboBox on the status editing form, and it will be used by us to select the status.
By double clicking on the main form grid, we will open the status editing form.
We will also add a “Status” lookup field to the dataset of the grid, which will refer to the qStMain dataset:
And it will insert into the grid the name of the status associated with this record.
Now let's move on to the dataset that will fill the combo box:
Initial training is complete.
Let us turn to the most interesting - the form of status change.
It will look like this:
Form preparation.
The very first thing you need to do is bind data to the combo box.
To do this, select the combo box in the designer and double-click on the LiveBindings line in the object inspector.
In the drop-down menu, select "Bind Visually":
After that, in the bottom part, the bindings window will appear:
For lukapa to the database, we need an intermediate field.
In this case, the Tag property of the combo box is well suited.
In this field we will store the current id value of the selected dataset string.
Therefore, we poke at three points and select Tag for display:
Do not forget to register our datamodule in the uses form.
After that appear in possible bindah.
In binding, we stretch the connections from the dataset fields to the combo box.
name drag in Item.Text, id drag in Item.LookupData.
This results in the automatic creation of a TBindSourceDB, TBindingsList and TLinkFillControlToField on the form.
Already at the moment, if we do not forget to make an open dataset, when opening a form, in the combo box there will be a list of values from the dataset.
It would seem - everything is fine, but there are two points.
Let's solve the first problem.
Let's create an Init () method in the form, in which we pass the status id in the selected string.
Accordingly, having id, we can make locate in dataset and positioned on the record.
This action does not lead to any changes in the combo box, so you need to select the desired value in it.
Wait, but in the grid we have no id status!
You can certainly add it to the request, but we are not looking for easy ways.
Let's return to the main form and gridu.
On the OnDblClick event of the grid, we will hang the display of the status change form:
Now by double-clicking on the line of the grid, the form for changing status will open, with a combo box positioned on the desired status.
Problem two remained. Whichever status we choose in a combo box, datasets attached to it will not move the cursor.
Solution two.
I googled a lot on this topic, but never found a solution.
I had to think of it myself.
We put on the form of the choice of the status TPrototypeBindSource.
In the "Structure" window, expand the list of this object and click RMB -> "AddItem" on the line "FieldDefs":
Choose its type "ftInteger" - this is id, and call it accordingly:
In LiveBindings Designer, we extend the links from ComboBox1.SelectedValue to PrototypeBindSource1.id, then from PrototypeBindSource1.id to ComboBox1.Tag.
Oddly enough, we cannot get an id from ComboBox1.SelectedValue.
But after the procedure, from ComboBox1.Tag - we can!
Who does not believe - can put on the label form and write in it the value of the combo box Tag, at the time of onChange.
Or try to get the id in any other way.
So, in the end, we have an id entry in the dataset, and this can be limited to reading the Tag field value after the modal form is closed.
But this is not our method again.
It is necessary to move the cursor dataset attached to the combo box to the selected record.
If you look at the “Structure” window, you can see the “LinkPropertyToFieldTag” element that appears:
Click on it and create an onAssignedValue event handler:
Now, when you select a line in the combo box, it also moves the cursor!
And we can rightfully complement the OnDblClick method of the main form:
We start, check, rejoice!
I hope this information will help someone get around the limitations imposed by fmx.
PS:
Not so long ago, faced with the need to work with a database from the FMX-application.
Those who have already “tested” Delphi XE should be aware of the absence of such favorite VCLs as FMX:
- TDBGrid
- TDBLoockupComboBox
')
And if the problem with DBGrid is solved quite intuitively, with visual binding, then with TDBLoockupComboBox everything is not so simple.
In any case, Google could not tell me anything sensible.
I solved the problem; I want to share the decision with the community, maybe someone will come in handy.
So, let's start with the fact that we outline a demo project.
DB for simplicity we take SQLite.
Create a main table, secondary, and a table of relations between them.
Well and we throw a bit of records for the test.
tbl_main, tbl_status, main_st.
Structure
-- Table: tbl_main CREATE TABLE tbl_main ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name VARCHAR( 255 ) ); INSERT INTO [tbl_main] ([id], [name]) VALUES (1, '1'); INSERT INTO [tbl_main] ([id], [name]) VALUES (2, '2'); INSERT INTO [tbl_main] ([id], [name]) VALUES (3, '3'); INSERT INTO [tbl_main] ([id], [name]) VALUES (4, '4'); -- Table: tbl_status CREATE TABLE tbl_status ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, name VARCHAR( 255 ) ); INSERT INTO [tbl_status] ([id], [name]) VALUES (1, ''); INSERT INTO [tbl_status] ([id], [name]) VALUES (2, ''); INSERT INTO [tbl_status] ([id], [name]) VALUES (3, ''); INSERT INTO [tbl_status] ([id], [name]) VALUES (4, ''); -- Table: main_st CREATE TABLE main_st ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, id_main INTEGER NOT NULL REFERENCES tbl_main ( id ) ON DELETE CASCADE ON UPDATE CASCADE, id_status INTEGER NOT NULL REFERENCES tbl_status ( id ) ON DELETE CASCADE ON UPDATE CASCADE ); INSERT INTO [main_st] ([id], [id_main], [id_status]) VALUES (1, 1, 2); INSERT INTO [main_st] ([id], [id_main], [id_status]) VALUES (2, 2, 1); INSERT INTO [main_st] ([id], [id_main], [id_status]) VALUES (3, 3, 2); INSERT INTO [main_st] ([id], [id_main], [id_status]) VALUES (4, 4, 3); Next, prepare a project in Delphi.
The main form, the form of status change, the module for working with the database (DataModule).
On the form of the data module we throw the necessary components for working with data.
I prefer FireDAC, so I tipped TFDPhysSQLiteDriverLink, TFDGUIxWaitCursor, TFDConnection and three TFDQuery.
Put the TGrid on the main form, stretch it over the whole form.
We put TComboBox on the status editing form, and it will be used by us to select the status.
By double clicking on the main form grid, we will open the status editing form.
Request for grid
Grid will be filled with dataset FDQuery1.
select id, name from tbl_main We will also add a “Status” lookup field to the dataset of the grid, which will refer to the qStMain dataset:
Request for lukapa in grid
select ms.id, ms.id_main, ms.id_status, s.name from main_st ms join tbl_status s on s.id = ms.id_status And it will insert into the grid the name of the status associated with this record.
Now let's move on to the dataset that will fill the combo box:
Request for TComboBox
The combo box will be populated using dataset qStatuses.
select id, name from tbl_status Initial training is complete.
Let us turn to the most interesting - the form of status change.
It will look like this:
Form preparation.
The very first thing you need to do is bind data to the combo box.
To do this, select the combo box in the designer and double-click on the LiveBindings line in the object inspector.
In the drop-down menu, select "Bind Visually":
After that, in the bottom part, the bindings window will appear:
For lukapa to the database, we need an intermediate field.
In this case, the Tag property of the combo box is well suited.
In this field we will store the current id value of the selected dataset string.
Therefore, we poke at three points and select Tag for display:
Do not forget to register our datamodule in the uses form.
After that appear in possible bindah.
In binding, we stretch the connections from the dataset fields to the combo box.
name drag in Item.Text, id drag in Item.LookupData.
This results in the automatic creation of a TBindSourceDB, TBindingsList and TLinkFillControlToField on the form.
Already at the moment, if we do not forget to make an open dataset, when opening a form, in the combo box there will be a list of values from the dataset.
It would seem - everything is fine, but there are two points.
- When opening, it would be nice to position the dataset and combo box on the record we clicked on in the grid.
- When you select a value in a combo box, the cursor does not move across the dataset, but you must!
Let's solve the first problem.
Let's create an Init () method in the form, in which we pass the status id in the selected string.
Accordingly, having id, we can make locate in dataset and positioned on the record.
This action does not lead to any changes in the combo box, so you need to select the desired value in it.
procedure TfrmStChange.init(id: string); begin with DM do begin if qStatuses.Active then qStatuses.Close; qStatuses.Open(); qStatuses.Locate('id', id, []); ComboBox1.Tag := id.ToInteger; ComboBox1.ItemIndex := ComboBox1.Items.IndexOf (qStatuses.FieldByName('NAME').AsString); end; end; Wait, but in the grid we have no id status!
You can certainly add it to the request, but we are not looking for easy ways.
Let's return to the main form and gridu.
On the OnDblClick event of the grid, we will hang the display of the status change form:
procedure TfrmMain.Grid1DblClick(Sender: TObject); var frm: TfrmStChange; begin frm := TfrmStChange.Create(self); DM.qStMain.Locate('id_main', DM.FDQuery1.FieldByName('id').AsString, []); frm.init(DM.qStMain.FieldByName('id_status').AsString); frm.ShowModal; frm.Free; end; Now by double-clicking on the line of the grid, the form for changing status will open, with a combo box positioned on the desired status.
Problem two remained. Whichever status we choose in a combo box, datasets attached to it will not move the cursor.
Solution two.
I googled a lot on this topic, but never found a solution.
I had to think of it myself.
We put on the form of the choice of the status TPrototypeBindSource.
In the "Structure" window, expand the list of this object and click RMB -> "AddItem" on the line "FieldDefs":
Choose its type "ftInteger" - this is id, and call it accordingly:
In LiveBindings Designer, we extend the links from ComboBox1.SelectedValue to PrototypeBindSource1.id, then from PrototypeBindSource1.id to ComboBox1.Tag.
Oddly enough, we cannot get an id from ComboBox1.SelectedValue.
But after the procedure, from ComboBox1.Tag - we can!
Who does not believe - can put on the label form and write in it the value of the combo box Tag, at the time of onChange.
Or try to get the id in any other way.
So, in the end, we have an id entry in the dataset, and this can be limited to reading the Tag field value after the modal form is closed.
But this is not our method again.
It is necessary to move the cursor dataset attached to the combo box to the selected record.
If you look at the “Structure” window, you can see the “LinkPropertyToFieldTag” element that appears:
Click on it and create an onAssignedValue event handler:
procedure TfrmStChange.LinkPropertyToFieldTagAssignedValue(Sender: TObject; AssignValueRec: TBindingAssignValueRec; const Value: TValue); begin with DM do begin qStatuses.Locate('id', ComboBox1.Tag.ToString, []); end; end; Now, when you select a line in the combo box, it also moves the cursor!
And we can rightfully complement the OnDblClick method of the main form:
procedure TfrmMain.Grid1DblClick(Sender: TObject); var frm: TfrmStChange; begin frm := TfrmStChange.Create(self); DM.qStMain.Locate('id_main', DM.FDQuery1.FieldByName('id').AsString, []); frm.init(DM.qStMain.FieldByName('id_status').AsString); frm.ShowModal; if (frm.ModalResult = mrOk) then begin DM.qStMain.Edit; DM.qStMainid_status.AsInteger := DM.qStatusesid.AsInteger; DM.qStMain.Post; refresh;// , close\open end; frm.Free; end; We start, check, rejoice!
I hope this information will help someone get around the limitations imposed by fmx.
PS:
- I will gladly accept any constructive criticism.
- If someone knows the method better - tell, sprinkle ashes on your head and admit you are wrong.
- The demo project is written only for the demo, any eye prints of object names should be considered random and irrelevant.
- Well, do not judge strictly, if that ...
Source: https://habr.com/ru/post/233645/
All Articles