SQL identity fields do not update after posting an ADO dataset (Delphi 2006 + SQL Server 2003)

maybe FCTable.Refresh after the post. be carefull by the way if the autoincrement field is the index you are using. it might be best to use a diff index

also see:
http://bdn.borland.com/article/16123

Thanks Mokule, the arAutoInc looked very promising, especially after I read up on it, but sadly when I set the option it makes no difference :-((

Doing a fctable.refresh just returns and error saying I don't have a key.

can you use an sql command to insert then use table.refresh?

i take it it is single user since the refresh bit will cause other problems as well? (other users changing data)

CPTable.Active:=false;
CPTable.Active:=true;

Regards

Ok, the requery method suggested by kretzschmar works but reading the Borland documentation on it apparently its just the same as closing and opening the database except it maintains cursor location.

As I indicated in my original question "If I close the open the table the values are re-read and are correct but obviously that's highly inefficient when I'm updating hundreds of records" (not sure why calinutz re-suggested it) so if that the only way to do it or is there a more efficient way?

Thanks.

again,use a sql component to add, then refresh the table ;)

Limbeck, thanks for the suggestion but as I've already said, if I call FCTable.refresh I just get a key error and if I update FCTable with an SQL and don't refresh then later on the app complains data has been updated by another method and errors.

hmm yes, but why not do the insert in an query component, and after you executed this statement refresh the table. do you still get an error then?

Yes, I've tried it in a TADOQuery as that's the only way I would know how to do a direct SQL command.

Perhaps this is all because I'm using ADO rather than dbExpress?  If dbExpress is a better solution (more efficient) then I'm happy to consider moving to that instead of ADO as long as I can get record selection in grids to work properly as when I tried it before and set the RowCurrentColor of the grid to blue, ALL rows display blue regardless of what I do to the cursor!

Perhaps I should have stuck to good old BDE ... lol :-))

>I'm updating hundreds of records
in case on such amount (sounds like a batch)
what for an interest is then to get back the autogenerated value?

a requery is fast enough for a dialog-app

meikl ;-)

Meikl, the interest in getting back the autogenerated value is for auditing.  The FCTable records are finance claims generated from census records in the CPTable so for traceability once the FCTable record is generated I write back the number to the CPTable.

It is done on a "Batch" basis with each batch containing 50 to 100 records however this is a national web based system and we could have up to fifty people at once doing this batch process and potentially thousands of other people updating other records so I need to be very efficient when I'm hitting the SQL server.

well, ok, currenlty i have never used d2006,
so i don't know what is changed there.
my last release i have coded with was d7

i see two possibilities (if possible)
- use a trigger on your targettable to perform the auditing
- use a stored procedure which returns the id so that you can supply it for your insert
(don't know if this is possible with SQLServer, my focus is more on oracle)

meikl ;-)

I'm not an SQL server expert.  I know you can have both triggers and stored procedures on SQL server but I would not know how to make them properly or how to use them to link back to Delphi.

I have made a stored procedure once in my life, so do a complex calculation for returning to another column not to Delphi.  I've never used triggers and know nothing about them.

It looks like there is no easy solution for this problem :-(

maybe you should consider a workaround like using a tquery (with the wehre clause) instead of a ttable so your datasets wont be as large and the requery wont affect performance all that much.

i use Firebird myself mostly, and instead of an autoincrement field i usually use a generator on the server that returns an unique id, i read that in my program and use it to save the record

>instead of an autoincrement field i usually use a generator
because Firebird has no autoincrement field  . . .

;)

well and it avoids those nasty key problems since my id is usually my prim. key.

I don't have an alternative; I must use SQL server as it's our company standard.

It looks like there is no simple solution to this problem so I have decided to change the way my app works and instead of using an autoincrement field I'll just store a number elsewhere and increment manually in the app each time I write a record.  It's not as elegant as getting the server to do it but it will solve the problem.

well use a generator on the server if you can, that way you are sure that the id's you use are unique

Hi,

Read %%IDENTITY variable from your MS SQL server just after the insert/append using a SP or query (select) in the same session. It gives you the last autoincrement value generated by the server (in the terms of your session, of course).

Regards, Geo

It should be @@IDENTITY, sorry for the mistake.

As a test I filled out my tables to the size we expect in a year's time ... and now I know what they mean about Serverside cursors being inefficient!  I gave up waiting after my app spent 5 minutes flogging the SQL server just starting up with a few Serverside cursors!  I changed to clientside cursors and although it took a while to start (10 to 15 seconds) it was then useable.

I've used the requery method on these large tables and although it's not fast it's just about acceptable, so I'm giving the points to kretzschmar.

It's not the simple solution I was looking for but it looks like the best I'm going to get and I need to move on with the app now.  Thanks for all your suggestiong guys.

Chris

Hi,

You haven't even tried to read the value of @@IDENTITY variable, right? Just try and you'll see how useful it is.

Regards, Geo