PowerBuilder: Get identity value after insert into Sql Server table

Question

I'm using a PowerBuilder 12 Classic DataWindow to insert a new record into a SQL Server 2008 database table that has an identity column. After the insert, I'd like to get the value of the identity column for use in other tables.

In my pbodb120.ini under [MS_SQLSERVER_SYNTAX] I've changed the GetIndentity line to this:

GetIdentity='Select IDENT_CURRENT (''&TableName'')'

My code looks like this (docid is the identity column):

dw_document_add.update(true, true)

li_docid = dw_document_add.getitemnumber(dw_document_add.getrow(), "docid")

The trouble is it only seems to return the identity about half the time. I think it may be a timing issue. (The records are getting inserted into the database.)

Is there any way I can be sure to get the identity every time?

UPDATE

I've added Identity='@@IDENTITY' to my connection string and it seems to be working now. But I'm not sure if that needs to be in the pbodbxxx.ini file instead.

Answer 1

The best advice is to not try to reinvent the wheel; the DataWindow already does this. In the DataWindow painter, go into Rows / Update Properties, and at the bottom you'll see Identity Column. Select your identity column (it needs to be in your SQL statement already), and PB will retrieve the identity value after INSERTs.

Good luck,

Terry.

Answer 2

And I wouldn't trust the GetRow() call either.

You should already have the row number in a local variable for the dw row you inserted, otherwise you wouldn't have had a proper argument for all the SetItem() calls that certainly preceeded the Update() call.