I would like my table to have the autoinc field but doing an insert on it using ClientDataSet causes 'field must have value' error. It seems Datasnap server does not know it should generate one by itself and expects a value.
For the demo I created a simple table with only 2 fields : ID (autoinc) and DATE (varchar).
The database is SQLite.
procedure TForm3.Button1Click(Sender: TObject);
begin
ClientDataSet1.Insert;
ClientDataSet1.FieldByName('DATE').Value:= DateUtils.DateOf(PlannerCalendar1.Date);
ClientDataSet1.Post;
ClientDataSet1.ApplyUpdates(0);
if ClientDataSet1.ApplyUpdates(0) = 0 then
ClientDataSet1.Refresh;
To deal with the autoinc issue I used Dr. Bobs suggestion:
procedure TForm3.ClientDataSet1NewRecord(DataSet: TDataSet);
const {$J+}
ID: Integer = -1;
begin
DataSet.FieldByName('ID').AsInteger := ID;
DEC(ID) ;
end;
Though autoinc count is going backwards, I can live with that. But ...
Records get added in the grid but not in the database !?
So can someone please enlighten me on how to deal with this autoinc nightmare? Seen some suggestions on using the onreconcile event of the dataset but manually correcting the error is not for me. I tried inserting a zero value for the ID field hoping that datasnap server would correct the error but all it does is flash me the reconcile dialog so I can correct the issue. Help !
end;
You don't say what kind of dataset you're using to connect your DataSetProvider to the Sqlite database. I imagine it's not a FireDac dataset, because they handle autoinc columns in Sqlite tables correctly.
So assuming you're using some other type, I think you may have overlooked the significance of the Errata section of the
http://edn.embarcadero.com/article/20847
article that has been mentioned in comments, where it says:
"For DBMSes that don't map their auto increment fields to TAutoIncField, you need to remove the TField.ProviderFlags.pfInUpdate flag on the source dataset to allow DataSnap to resolve the record back to the database. You're really doing the same thing that DataSnap does automatically for TAutoIncFields by doing this. "
This applies to Sqlite. As well as that, what you need to do is to inspect the column type of your source dataset (I mean, the one feeding your DataSetProvider), at run-time if your source dataset fields aren't persistent ones.
For Sqlite, the source dataset field type should be ftLargeInt, not ftAutoInc, because the autoinc column size in the Sqlite database is 64-bit, not 32-bit (which is what ftAutoinc assumes). The easiest way to avoid this problem is to create persistent TFields on your source dataset and make sure the ID column one is type ftLargeInt in both your DBX table that accesses the Sqlite database and your ClientDataSet.
Btw #2, you don't need the 2 ApplyUpdates in your Button1Click, but apart from that, your code seems fine.
