TDbAdvGrid: Common mistake when adding records using the grid
There are always subtle differences when you compare the standard VCL components to their much more feature-rich counterparts from profressional component sets.
When you start using TDBAdvGrid in your database projects you will definitely run into the “issue” that will I describe here. The biggest annoyance when encountering this is that you basically will start looking for a fix to the problem in the wrong place as you never expect the issue to be related to the setup of the visual component.
My form setup is pretty simple: VCL application. I have two visual grid components hooked up to the same TDatasource, which is connected to a TFDQuery.
TDBAdvGrid from TMSSoftware on top, TDBGrid on the bottom.
The data being edited is also quite simple: An item number is associated with a pack name and a theme name. The values for the combo box are being pulled from yet other queries (lookup fields), but this is not to be the focus of this post.
Compiling this for the first time will immediately yield different results. You will be able to enter data in the TDBGrid. Furthermore, you will be able to add more records to the already existing list, i.e. append and edit operations are both enabled by default. However, this is a very easy fix. TDBGrid behaves according to the TDatasource which is set to AutoEdit by default. Thus, the user may edit and append.
TDBAdvgrid introduces another level that can be configured. In order to be able to edit the dataset, you need to set “goEditing” of the “Options” property to “true”:
If you restart the app now, you will be able to edit the already existing values in the TDBAdvgrid. Still, you will not be able to add any values. Yes, you may bring a TDBNavigator or similar component into the mix. But entering values is supposed to be quick and efficient for the user. He needs to be able to keep his focus on the grid. Thus, we find in the “Navigation” property the setting “AppendOnArrowDown”. Perfect, exactly what we need.
Done. … or are we?
If you try to append a value at this point, the grid will offer a new row to enter values:
Entering “71220” is possible, but in no way you will be able to select anything else. As soon as you leave the cell, the row “disappears”. It also does not show up in the second grid as an incomplete record. It simply disappears.
If you are like me, you question your setup of the query, datasource and connection immediately. Is the query set up correctly to post the values correctly? Due to the fact that I had lookup fields involved, I started checking those as well. Of course, I realized that the underlying DBMS does not accept tuples that lack the association to pack and theme. This is all correct, but why does the component not allow me to make the selection first?
To make things worse, I did not have the luxury of a TDBGrid on the same page when I implemented this. That was the n-th step for me to see what was going on. As soon as I realized that things turned bad as soon as the cell in the TDBAdvGrid was left, I remembered.
Quote from the developer guide:
TDBAdvGrid can perform editing in two modes. This is selected by the property DBAdvGrid.EditPostMode. When this is set to epCell, this means that the value that has been edited is posted immediately to the database when a cell leaves inplace editing. When DBAdvGrid.EditPostMode is set to epRow, this means that a Post will only happen when all cells in a row have been edited and the user moves to another row. Typically, for tables with various required fields, the epRow setting is the preferred setting.
The non-default behavior is exactly what I need in my case. I need the row to be posted when all the fields have been entered.
So, I changed the property EditPostMode to ‘epRow’. And … all worked as I wanted it to work in the first place.
As a general rule when working with visual components that are linked to database queries or tables, it is always good to be aware of the effects the visual component and the associated user interaction with that component might have on the underlying dataset.
This is a very good example where flexibility and comfort require some additional thought how to configure the post operation. I am really struggling not to link to the obvious Spiderman quote here….
(For anyone wondering what the sample data refers to: it refers to available LEGO Dimension character and item packs…)