Auto Increment Fields in a ClientDataSet

By: Justin Swett

Abstract: Using an aggregate to auto increment a local dataset with a TClientDataSet component.


How can I auto increment my field in a dataset using TClientDataSet?


If you are using a TClientDataSet to create local data set, which will be saved locally to disk, then there are a couple of ways to automatically increment a field. The first technique requires the use of an aggregate. Examine the code below:

   NewID: Integer;
//Assuming that your table has been created 
   with ClientDataSet do 
      AggregatesActive := False;
      with Aggregates.Add do
        Expression := 'Max(AField)';
        AggregateName := 'Runtime';
        Active := True;

      AggregatesActive := True;

        NewID := Aggregates[0].Value; 
        // ...or alternatively
        // NewID := Aggregates.Find('Runtime').Value;
        on E: Exception do SomeHandle; //Probably shouldn't throw exception
      // Next set field value to NewID
      FieldByName('AField').AsInteger := NewID;

In this example we first deactivate any aggregates, as well as clearing them. Clearing them is optional, if you choose not to clear then be sure to use the proper index value when indexing into Aggregates[]. When we add the new aggregate we set the Expression to Max('AField'), give it a name, and activate it. This aggregate will keep track of the maximum value in your auto incremented field. So when you insert a new record you can set the value of the auto incremented field to the current value of the aggregate, add 1.

The second technique of auto incrementing requires changing the current index of the TClientDataSet. The first thing to do is to create an Index with the value of its Fields property set to the field name for which you want to auto increment. Once the index is created you need to set it to the ClientDataSet.IndexName property. This will cause the resulting data set to order itself in descending order based on the values in the column. You can then call Last() to move the cursor to the end of this data set. Next, get the value out of the field and add 1. This will be the value of your next of the next record's auto increment field.

Server Response from: ETNASC04