Moving Columns in a DBGrid seems to move the attached DataSet fields

Question

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

procedure TForm1.FormCreate(Sender: TObject);
begin
  with ClientDataSet1.FieldDefs do
  begin
    Clear;
    Add('StartOfWeek', ftDate);
    Add('Label', ftString, 30);
    Add('Count', ftInteger);
    Add('Active', ftBoolean);
  end;
  ClientDataSet1.CreateDataSet;
end;

Button1, which is labeled Show ClientDataSet Structure, contains the following OnClick event handler.

procedure TForm1.Button1Click(Sender: TObject);
var
  sl: TStringList;
  i: Integer;
begin
  sl := TStringList.Create;
  try
    sl.Add('The Structure of ' + ClientDataSet1.Name);
    sl.Add('- - - - - - - - - - - - - - - - - ');
    for i := 0 to ClientDataSet1.FieldCount - 1 do
      sl.Add(ClientDataSet1.Fields[i].FieldName);
    ShowMessage(sl.Text);
  finally
    sl.Free;
  end;
end;

To demonstrate the moving field effect, run this application and click the button labeled Show ClientDataSet Structure. You should see something like that shown here:

The Structure of ClientDataSet1
- - - - - - - - - - - - - - - - - 
StartOfWeek
Label
Count
Active

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:

The Structure of ClientDataSet1
- - - - - - - - - - - - - - - - - 
Label
StartOfWeek
Active
Count

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:

var
  CountField: TIntegerField;
  Sum: Integer;
begin
  Sum := 0;
  CountField := TIntegerField(ClientDataSet1.FieldByName('Count'));
  ClientDataSet1.DisableControls;  //assuming we're attached to a DBGrid
  try
    ClientDataSet1.First;
    while not ClientDataSet1.EOF do
    begin
      Sum := Sum + CountField.AsInteger;
      ClientDataSet1.Next;
    end;
  finally
    ClientDataSet1.EnableControls;
  end;

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.

procedure TForm1.Button2Click(Sender: TObject);
var
  sl: TStringList;
  i: Integer;
  CloneClientDataSet: TClientDataSet;
begin
  CloneClientDataSet := TClientDataSet.Create(nil);
  try
    CloneClientDataSet.CloneCursor(ClientDataSet1, True);
    sl := TStringList.Create;
    try
      sl.Add('The Structure of ' + CloneClientDataSet.Name);
      sl.Add('- - - - - - - - - - - - - - - - - ');
      for i := 0 to CloneClientDataSet.FieldCount - 1 do
        sl.Add(CloneClientDataSet.Fields[i].FieldName);
      ShowMessage(sl.Text);
    finally
      sl.Free;
    end;
  finally
    CloneClientDataSet.Free;
  end;
end;

If you run this project and click the button labeled Show Cloned ClientDataSet Structure, you will always get the true structure of the ClientDataSet, as shown here

The Structure of ClientDataSet1
- - - - - - - - - - - - - - - - - 
StartOfWeek
Label
Count
Active

Addendum:

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.

Answer 1

Cary I think I've found a solution for this problem. Instead of using VCL wrapper Fields we need to use an internal Fields property of the Recordset COM object.

Here is how it should be referenced:

qry.Recordset.Fields.Item[0].Value

Those fields are NOT affected by the behaviour you have described earlier. So we can still refer to the fields by their index.

Test this out and tell me what was the result. It worked for me.

Edit:

Of course it will work only for ADO components, not for the TClientDataSet...

Edit2:

Cary I do not know if this is answer for your question, however I've been pushing folks on the embarcadero forums and Wayne Niddery gave me quite detailed answer about all this Fields movement.

To make a long story short: If you define your columns in TDBGrid explicitly, field indexes are not moving! Have a bit more sense now, hasn't it?

Read full thread here: https://forums.embarcadero.com/post!reply.jspa?messageID=197287

Answer 2

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.

procedure TForm1.Button1Click(Sender: TObject);
var
  sl: TStringList;
  i: Integer;
begin
  sl := TStringList.Create;
  try
    sl.Add('The Structure of ' + ClientDataSet1.Name +
      ' using FieldByNumber');
    sl.Add('- - - - - - - - - - - - - - - - - ');
    for i := 0 to ClientDataSet1.FieldCount - 1 do
      sl.Add(ClientDataSet1.Fields.FieldByNumber(i + 1).FieldName);
    ShowMessage(sl.Text);
  finally
    sl.Free;
  end;
end;

For the sample project, this code produces the following output, regardless of the orientation of the Columns in the associated DBGrid:

The Structure of ClientDataSet1 using FieldByNumber
- - - - - - - - - - - - - - - - - 
StartOfWeek
Label
Count
Active

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:

TBlobField(MyDataSet.Fields.FieldByNumber(6)).SaveToFile('c:\mypic.jpg');

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.

Answer 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.

procedure TColumn.SetIndex(Value: Integer);
[...]
        if (Col <> nil) then
        begin
          Fld := Col.Field;
          if Assigned(Fld) then
            Field.Index := Fld.Index;
        end;
[...]