Fail on ApplyUpdates?

Fail on ApplyUpdates?

Postby P Scott » Wed 13 Jun 2012 13:31

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
P Scott
 
Posts: 12
Joined: Wed 21 Dec 2011 15:14
Location: USA

Re: Fail on ApplyUpdates?

Postby ZEuS » Thu 14 Jun 2012 14:46

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;
ZEuS
Devart Team
 
Posts: 235
Joined: Thu 05 Apr 2012 07:32

Re: Fail on ApplyUpdates?

Postby P Scott » Fri 15 Jun 2012 12:56

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.
P Scott
 
Posts: 12
Joined: Wed 21 Dec 2011 15:14
Location: USA

Re: Fail on ApplyUpdates?

Postby ZEuS » Fri 15 Jun 2012 13:11

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.
ZEuS
Devart Team
 
Posts: 235
Joined: Thu 05 Apr 2012 07:32

Re: Fail on ApplyUpdates?

Postby P Scott » Tue 19 Jun 2012 02:30

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.
P Scott
 
Posts: 12
Joined: Wed 21 Dec 2011 15:14
Location: USA

Re: Fail on ApplyUpdates?

Postby ZEuS » Tue 19 Jun 2012 07:55

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.
ZEuS
Devart Team
 
Posts: 235
Joined: Thu 05 Apr 2012 07:32

Re: Fail on ApplyUpdates?

Postby P Scott » Tue 19 Jun 2012 22:07

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

Scott
P Scott
 
Posts: 12
Joined: Wed 21 Dec 2011 15:14
Location: USA


Return to dbExpress driver for SQLite