Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC FDQuery reporting [FireDAC][DApt]-400 with AutoInc fields


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 2 - Last Post: Dec 31, 2015 2:09 AM Last Post By: Mike Collins
Mike Collins

Posts: 46
Registered: 9/23/05
FireDAC FDQuery reporting [FireDAC][DApt]-400 with AutoInc fields  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 13, 2015 4:00 PM
Hi All,

Apologies for a little cross positing - I asked a similar question in CBuilder>>FireDAC but there seems to be little traffic in that group and I have narrowed down the problem further since then.

The prelim to my problem was thus: I have a DataSnap client-server set up using TCP built with CBuilder 10 Update 1. On the DataSnap service I have a connection to a MS SQL Database using FireDAC TFDConnection and TFDQuery. Within the SQL Database I have a VIEW, vwPerson, with an AutoInc ID called PersonID. When I was trying to add in a new record on the client, I was consistently getting an exception raised stating: "Record not found or changed by another user". I followed through several tutorials, including Dan Miser "How to use AutoInc fields with DataSnap" to no avail.

However, I then tried to create a simple stand-alone client, with no DataSnap, connecting directly to the MS SQL Database (I simply copied the TFDConnection & TFDQuery out of my DataSnap server module). I added in a TDataSource and a TDBGrid and connect up, execute the SQL Query and I see the corresponding results in the DBGrid. I then try and add in a new record (the AutoInc field, PersonId, is automatically assigned a negative value) and when the grid tries to apply / post the changes I get the same exception:

Project Project1.exe raised exception class EFDException with message '[FireDAC][DApt]-400. Insert command inserted [0] instead of [1] record. Possible reasons: update table does not have PK or row identifier, record has been changed/deleted by another user'.

On the TFDQuery I have the following settings:
1) UpdateOptions->AutoIncFields = PersonID (I have tried it with and without)

For the actual persistent field, qryPersonVwPersonID I have the following settings:
1) Field type is automatically picked up as TFDAutoIncField
2) AutoGenerateValue = arAutoInc
3) AutoIncrementSeed = -1
4) AutoIncrementStep = -1
5) ClientAutoIncrement = true
6) ProviderFlags = [pfInWhere, pfInKey]
7) ReadOnly = true
8) Required = false
9) ServerAutoIncrement = true

What is frustrating is that the update is actually applied - if I refresh the query I see the new record.

From this, I have concluded that there is probably little wrong with my datasnap set up and more a case that I am doing something fundamentally wrong with the basic FireDAC query update since the stand-alone client also fails to insert new records.

I'm desperate for any help our guidance - I have been chasing this for the last 6 weeks without any success.

Any help or pointers would be gratefully received. Fingers-crossed

Mike
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC FDQuery reporting [FireDAC][DApt]-400 with AutoInc fields  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 15, 2015 10:59 PM   in response to: Mike Collins in response to: Mike Collins
Project Project1.exe raised exception class EFDException with message '[FireDAC][DApt]-400. Insert command inserted [0] instead of [1] record. Possible reasons: update table does not have PK or row identifier, record has been changed/deleted by another user'.

At first, try to make your simple (no DataSnap) application working.

"Insert command inserted [0] instead of [1] record" means, that SQL Server
ODBC driver returns the number of inserted records equal to zero. With
SQL Server it may happen if your table has on-insert trigger. If it is your
case then insert "SET NOCOUNT ON" at top of the trigger. If it is not,
then please post here your table DDL and environment report:
http://docwiki.embarcadero.com/RADStudio/Seattle/en/DBMS_Environment_Reports_%28FireDAC%29

Finally, you can turn off checking the number of updated records by
setting UpdateOptions.CountUpdatedRecords to False.

--
With best regards,
Dmitry
Mike Collins

Posts: 46
Registered: 9/23/05
Re: FireDAC FDQuery reporting [FireDAC][DApt]-400 with AutoInc fields  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 31, 2015 2:09 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Hi Dmitry,

Many thanks for taking the time to point me in the right direction. I had actually just stumbled across the MS SQL FAQ where it clearly documents the error and cause - clearly I should have read the documentation closer.

Setting UpdateOptions.CountUpdatedRecords to False worked on the demo project where the connection was been made locally (and not via DataSnap). However, when I introduced a DataSetProvider into the mix it didnt work, since updates are being carried out by the DSP and not the underlying TFDQuery. However, it was a simple enough matter to add "SET NOCOUNT ON" to the start of all triggers.

Thanks again

Mike

Dmitry Arefiev wrote:
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02