title: Moving Columns in a DBGrid seems to move the attached DataSet fields
body: <p>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?</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Button1, which is labeled Show ClientDataSet Structure, contains the following OnClick event handler.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>The Structure of ClientDataSet1
  - - - - - - - - - - - - - - - - - 
  StartOfWeek
  Label
  Count
  Active
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>The Structure of ClientDataSet1
  - - - - - - - - - - - - - - - - - 
  Label
  StartOfWeek
  Active
  Count
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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 <a href="http://delphi.about.com/od/adptips2005/a/bltip0105_2.htm" rel="noreferrer">http://delphi.about.com/od/adptips2005/a/bltip0105_2.htm</a>.)</p>
  
  <p>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').</p>
  
  <p>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.</p>
  
  <p>If you do need to refer to the field repeatedly (and a large number of times), consider using something like the following code snippet:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>This is demonstrated in the following code, which is associated with the OnClick event handler of the button labeled Show Cloned ClientDataSet Structure.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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</p>
  
  <pre><code>The Structure of ClientDataSet1
  - - - - - - - - - - - - - - - - - 
  StartOfWeek
  Label
  Count
  Active
  </code></pre>
  
  <p>Addendum:</p>
  
  <p>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).</p>
  
  <p>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).</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>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.</p>