Fail on ApplyUpdates?

[P Scott]

I managed to create a grid in Delphi XE2 that I can edit, delete rows, etc. just fine. But when finished, the underlying database is never updated. I am using TSimpleDataSet and just a table from the database that I edit (no query that I define). In fact the only code in the whole project is:
--
procedure TForm1.BitBtn1Click(Sender: TObject); //apply updates button
var
i: integer;
begin
SimpleDataSet1.Edit; //line probably not necessary
SimpleDataSet1.Post;
i := SimpleDataSet1.ApplyUpdates(-1); //-1 allows unlimited error count
if i <> 0 then
raise Exception.Create('Error count='+IntToStr(i)+' on ApplyUpdates occured');
end;
--
If I have 3 edits to the grid, I get an error count of 3. Never do any changes get recorded in the database. Is this a delphi coding problem or something with the dbexpress driver? Do I need to apply query statements rather than editing the table in a grid? Eventually I probably will be using TClientDataSet as opposed to TSimpleDataSet - would that work better?

TIA,
Scott Stalheim

[ZEuS]

Hello.

Please try to reproduce the problem without using any visual components to find out if the problem is due to dbExpress or TSimpleDataSet.
Unlink the grid and edits from the SimpleDataSet1 and modify your code like the following:

Code: Select all

procedure TForm1.BitBtn1Click(Sender: TObject); //apply updates button
var
 i: integer;
begin
 SimpleDataSet1.Edit;
 SimpleDataSet1.FieldByName('SOME_FIELD').AsInteger := 1;
 SimpleDataSet1.Post;
 i := SimpleDataSet1.ApplyUpdates(-1);
 if i <> 0 then
   raise Exception.Create('Error count='+IntToStr(i)+' on ApplyUpdates occured');
end; 

[P Scott]

Still same problem. Even with no visual component.
When i do use a grid or dbedit component, the data appears, but always error on ApplyUpdates. The error count reported is one error for each record where a change was made.
Scott S.

[ZEuS]

Please try to create a small sample that demonstrates the problem, and send it to eugeniyz*devart*com, including a script to create a table.

[P Scott]

Found the problem!

The very small example I was preparing for you worked, and with much trial and error I found that a field name can NOT have a question mark (?) in it! I had a boolean field names "Newsletter?" for True/False on who gets a newsletter. Turns out that ANY field with a "?"in the field name makes ApplyUpdates fail. (A question mark is allowed in some SQL utitlities, so don't know if this restriction comes from the driver, from SQLite, or from Delphi.)

This does raise a question on how to find errors more easily! The Delphi routine for OnReconcileError gave me an error like: <near ":13":syntax error>.
I suppose this was line 13 of the SQL for applying updates, but darned if i know where to view the SQL for applying updates.!

Thanks for the help.

Scott S.

[ZEuS]

The problem is due to peculiarities of working with parametrized queries in dbExpress.
dbExpress does not use named parameters and represents all query parameters as "?", without name.
In your case, when trying to apply updates, dbExpress treats the "Newsletters?" column name as [the Newsletters column][a parameter] and raises the "syntax error" error.
To avoid such errors, you should not use "?" in column names when working with dbExpress.
To view the SQL for applying updates you can use the TSQLMonitor component. Just put it on the form, set the SQLConnection property to the connection you are using, set the Active property to True, and then you can monitor SQL statements in the TSQLMonitor.OnTrace event handler.

[P Scott]

Thanks for the help, and the tip on future debugging!

Scott