I observed something last week that I did not expect, and will describe below. I am curious as to why this happens. Is it something internal to the TDataSet class, an artifact of the TDBGrid, or something else?

The order of the fields in an open ClientDataSet changed. Specifically, I created a ClientDataSet in code by calling CreateDatatSet after defining its structure using FieldDefs. The first field in this ClientDataSet's structure was a Date field named StartOfWeek. Only moments later, code that I had also written, which assumed that the StartOfWeek field was in the zeroeth position, ClientDataSet.Fields[0], failed, since the StartOfWeek field was no longer the first field in the ClientDataSet.

After some investigation, I learned that it was possible that every single field in the ClientDataSet might, at a given moment, appear in some position different from the original structure at the time that the ClientDataSet was created. I was unaware that this could happen, and a search on Google didn't turn up any mention of this effect either.

What happened wasn't magic. The fields didn't change position by themselves, nor did they change based on anything I did in my code. What caused the fields to physically appear to change position in the ClientDataSet was that the user had changed the order of the Columns in a DbGrid to which the ClientDataSet was attached (through a DataSource component, of course). I replicated this effect in Delphi 7, Delphi 2007, and Delphi 2010.

I created a very simple Delphi application that demonstrates this effect. It consists of a single form with one DBGrid, a DataSource, two ClientDataSets, and two Buttons. The OnCreate event handler of this form looks like the following

Next, drag the columns of the DBGrid to re-arrange the display order of the fields. Click the Show ClientDataSet Structure button once again. This time you will see something similar to that shown here:

What is remarkable about this example is that the Columns of the DBGrid are being moved, but there is an apparent effect on the position of the Fields in the ClientDataSet, such that the field that was in the ClientDataSet.Field[0] position at one point is not necessarily there moments later. And, unfortunately, this is not distinctly a ClientDataSet issue. I performed the same test with BDE-based TTables and ADO-based AdoTables and got the same effect.

If you never need to refer to the fields in your ClientDataSet being displayed in a DBGrid, then you don't have to worry about this effect. For the rest of you, I can think of several solutions.

The simplest, though not necessary the preferable way to avoid this problem is to prevent the user from reordering fields in a DBGrid. This can be done by removing the dgResizeColumn flag from the Options property of the DBGrid. While this approach is effective, it eliminates a potentially valuable display option, from the user's perspective. Furthermore, removing this flag not only restricts column reordering, it prevents column resizing. (To learn how to limit column reordering without removing the column resizing option, see http://delphi.about.com/od/adptips2005/a/bltip0105_2.htm.)

The second workaround is to avoid referring to a DataSet's fields based on their literal position (since this is the essence of the problem). In order words, if you need to refer to the Count field, don't use DataSet.Fields[2]. So long as you know the name of the field, you can use something like DataSet.FieldByName('Count').

There is one rather big drawback to the use of FieldByName, however. Specifically, this method identifies the field by iterating through the Fields property of the DataSet, looking for a match based on the field name. Since it does this every time you call FieldByName, this is a method that should be avoided in situations where the field needs to be referenced many times, such as in a loop that navigates a large DataSet.

If you do need to refer to the field repeatedly (and a large number of times), consider using something like the following code snippet:

There is a third solution, but this is only available when your DataSet is a ClientDataSet, like the one in my original example. In those situations, you can create a clone of the original ClientDataSet, and it will have the original structure. As a result, whichever field was create in the zeroeth position will still be in that position, regardless of what a user has done to a DBGrid that displays the ClientDataSets data.

This is demonstrated in the following code, which is associated with the OnClick event handler of the button labeled Show Cloned ClientDataSet Structure.

It is important to note that that the actual structure of the underlying data is not affected. Specifically, if, after changing the order of the columns in a DBGrid, you call the SaveToFile method of the ClientDataSet, the saved structure is the original (true internal) structure. Also, if you copy the Data property of one ClientDataSet to another, the destination ClientDataSet also shows the true structure (which is similar to the effect observed when a source ClientDataSet is cloned).

Similarly, changes to the column orders of DBGrids bound to other tested Datasets, including TTable and AdoTable, do not actually affect the structure of the underlying tables. For example, a TTable that displays data from the customer.db sample Paradox table that ships with Delphi does not actually change that table's structure (nor would you expect it to).

What we can conclude from these observations is that the internal structure of the DataSet itself remains intact. As a result, I must assume that there is a secondary representation of the DataSet's structure somewhere. And, it must be either associated with the DataSet (which would seem to be overkill, since not all uses of a DataSet need this), associated with the DBGrid (which makes more sense since the DBGrid is using this feature, but which is not supported by the observation that the TField reordering seems to persist with the DataSet itself), or is something else.

Another alternative is that the effect is associated with the TGridDataLink, which is the class that gives multirow-aware controls (like DBGrids) their data awareness. However, I am inclined to reject this explanation as well, since this class is associated with the grid, and not the DataSet, again since the effect seems persist with the DataSet classes themselves.

Which brings me back to the original question. Is this effect something internal to the TDataSet class, an artifact of the TDBGrid, or something else?

Permit me also to stress something here that I added to one of the below comments. More than anything, my post is designed to make developers aware that when they are using DBGrids whose column orders can be changed that the order of their TFields may also be changing. This artifact can introduce intermittent and serious bugs which can be very difficult to identify and fix. And, no, I don't think this is a Delphi bug. I suspect that everything is working as it was designed to work. It's just that many of us were unaware that this behavior was occurring. Now we know.

Very informative, but is there a question in here somewhere?
–
Mason WheelerDec 31 '09 at 6:21

Thanks @Cary, I have had no idea about that and I am using DataSet.Field[x] construct very often. I think you should report it on Embarcadero website as a bug.
–
WodzuDec 31 '09 at 9:33

There is a question, which appears in the second sentence: "Is it something internal to the TDataSet class, an artifact of the TDBGrid, or something else?" I spend some time (an hour or so) searching both the TCustomGrid and TDataSet source, but didn't see where this is happening. More importantly, and this is why my post is so long, I wanted to at least make Delphi developers aware of this interesting behavior. For anyone who is using a DBGrid, or some other similar grid that produces these changes in the TFields order, it could be a source of an intermittent, and difficult to find bug.
–
Cary JensenDec 31 '09 at 16:24

I don't think that this is a bug. It may be unexpected, simply because it is a little known artifact, but it doesn't look like a bug.
–
Cary JensenDec 31 '09 at 17:07

Yeah you've right. I've consulted and it seems that it is done by design since Delphi 1.
–
WodzuDec 31 '09 at 20:16

3 Answers
3

Apparently the behaviour is by design. In fact it is not related to the dbgrid. It is merely a side effect of a column setting a field index. For instance this statement,

ClientDataSet1.Fields[0].Index := 1;

will cause the output of the "Show ClientDataSet Structure" button to change accordingly, either there is a grid or not. The documentation for TField.Index states;

"Change the order of a field's position in the dataset by changing the value of Index. Changing the Index value affects the order in which fields are displayed in data grids, but not the position of the fields in physical database tables."

One should conclude the reverse should also be true and changing the order of fields in a grid should cause field indexes to be changed.

The code causing this is in TColumn.SetIndex. TCustomDBGrid.ColumnMoved sets a new index for the moved column and TColumn.SetIndex sets the new index for that column's field.

Wodzu posted a solution to the reordered Field problem that was specific to ADO DataSet, but he led me to a solution that is similar, and available for all DataSets (whether it is implemented properly in all DataSets is another issue). Note that neither this answer, nor Wodzu's, is actually an answer to the original question. Instead, it is a solution to the problem noted, whereas the question relates to where this artifact originates.

The solution that Wodzu's solution lead me to was FieldByNumber, and it is a method of the Fields property. There are two interesting aspects to the use of FieldByNumber. First, you must qualify its reference with the Fields property of your DataSet. Second, unlike the Fields array, which takes a zero-based indexer, FieldByNumber is a method that takes a one-based parameter to indicate the position of the TField you want to reference.

The following is an updated version of the Button1 event handler that I posted in my original question. This version uses FieldByNumber.

To repeat, notice that the reference to the underlying TField required FieldByNumber to be qualified with a reference to Fields. Furthermore, the parameter for this method must lie within the 1 to DataSet.FieldCount range. As a result, to refer to the first field in the DataSet, you use the following code:

ClientDataSet1.Fields.FieldByNumber(1)

Like the Fields array, FieldByNumber returns a TField reference. As a result, if you want to refer to a method that is specific to a particular TField class, you have to cast the returned value to the appropriate class. For example, to save the contents of a TBlobField to a file, you may have to do something like the following code:

Note that I am not suggesting that you should reference TFields in a DataSet using integer literals. Personally, the use of a TField variable that gets initialized through a one time call to FieldByName is more readable, and is immune to changes in the physical order of a table's structure (though not immune to changes in the names of your fields!).

However, if you have DataSets associated with DBGrids whose Columns can be reordered, and you reference the fields of these DataSets using integer literals as indexers of the Fields array, you may want to consider converting your code to use the DataSet.Fields.FieldByName method.

Your solution is a good one, in that you can still reliably refer to specific TFields by their known physical location in the structure of the underlying DataSet. The limitation is, as you've pointed out, this works for ADO DataSet only. However, you sent me back on second search for a corresponding member of the TFields class. This class has a FieldByNumber method, which is invariant with respect to the order of Columns in an associated grid. FieldByNumber is like your Item property. I've added an answer to the question that explains FieldByNumber in more detail than is availale here.
–
Cary JensenDec 31 '09 at 17:17

Reading between the lines, we can conclude that the DBGrid is the source of this effect, interacting in some way with the TFields of the DataSet. I'm going to accept your answer on the basis of this conclusion. But I think we can still dig deeper. I want to know more about the precise mechanism that is responsible, as it may be the source of other little known side effects. Thanks, Dimitrij
–
Cary JensenJan 1 '10 at 0:32