VB ADO: Getting Identity Field after Addnew

Frost I will be doing some heavy Stored Proc stuff later this week.
And I have figured out how to return data, although only string data. Requery
is really a backward way, does UDB have an equivalent to the @@IDENTITY field
in SQL. I'll check at work tomorrow if I have time, tomorrows a SQL Server
day. (I support UDB and SQL, mostly on NT)

KlK, MCSE
klk@knudsons.com

"MarkN" <mnuttall@nospam.com> wrote:
>
>Are you using the DB2 provider? I'm not sure if I will even work then.
Alot
>of the ADO databoundish techniques don't seem to work with DB2.
>See my discussion with Kevin on ADO and DB2 - http://news.dev-archive.com/cgi-bin/dnewsweb.exe?cmd=article&group=database.db2.general&item=249&utag=
>
>
>"Frost" <stevef@pedco.com> wrote:
>>
>>Oh Kay...
>>
>>I have a table on DB2 with 2 fields ID and Name. ID field in an Identity
>>field. When I add a new record to the Table (via ADO) I need to be able
>>to get the ID of the record I just added. The code below is and example
>>of how I can do it in SQL Server. UDB returns 0 for the lID? Can anyone
>>tell me how I can get the ID. If I do a .Requery I a can get it by moving
>>to the last record. But that is only if it is sorted by ID.
>>
>>Dim rsRec as Recordset
>>Dim lID as long
>>Dim sSQL as String
>>Dim db2 as Connection
>>
>>sSQl = "Select * From NameTable Order by Name"
>>rsRec.Open sSQL,db2,adOpenDynamic,adLockOptimistic
>>
>>rsRec.AddNew
>> rsRec![Name] = "Steve"
>>rsRec.Update
>>
>>lID = rsRec![ID]
>>
>>
>>
>

It does - IDENTITY_VAL_LOCAL(). Here is a link. http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7irdb2ir19.htm

I'm not sure how well getting this (or @@IDENTITY) works. It returns the
most recently assigned value. You could get some elses. I remember this
being an issue with SQL Server. If this is the case, the only safe way is
to retrieve the record by the application/candidate/logical key(what really
makes the record unique) after insert.

"kevin knudson" <klk@knudsons.com> wrote:
>
>Frost I will be doing some heavy Stored Proc stuff later this week.
>And I have figured out how to return data, although only string data. Requery
>is really a backward way, does UDB have an equivalent to the @@IDENTITY
field
>in SQL. I'll check at work tomorrow if I have time, tomorrows a SQL Server
>day. (I support UDB and SQL, mostly on NT)
>
>KlK, MCSE
>klk@knudsons.com
>
>"MarkN" <mnuttall@nospam.com> wrote:
>>
>>Are you using the DB2 provider? I'm not sure if I will even work then.
> Alot
>>of the ADO databoundish techniques don't seem to work with DB2.
>>See my discussion with Kevin on ADO and DB2 - http://news.dev-archive.com/cgi-bin/dnewsweb.exe?cmd=article&group=database.db2.general&item=249&utag=
>>
>>
>>"Frost" <stevef@pedco.com> wrote:
>>>
>>>Oh Kay...
>>>
>>>I have a table on DB2 with 2 fields ID and Name. ID field in an Identity
>>>field. When I add a new record to the Table (via ADO) I need to be able
>>>to get the ID of the record I just added. The code below is and example
>>>of how I can do it in SQL Server. UDB returns 0 for the lID? Can anyone
>>>tell me how I can get the ID. If I do a .Requery I a can get it by moving
>>>to the last record. But that is only if it is sorted by ID.
>>>
>>>Dim rsRec as Recordset
>>>Dim lID as long
>>>Dim sSQL as String
>>>Dim db2 as Connection
>>>
>>>sSQl = "Select * From NameTable Order by Name"
>>>rsRec.Open sSQL,db2,adOpenDynamic,adLockOptimistic
>>>
>>>rsRec.AddNew
>>> rsRec![Name] = "Steve"
>>>rsRec.Update
>>>
>>>lID = rsRec![ID]
>>>
>>>
>>>
>>
>

MarkN wrote:

> It does - IDENTITY_VAL_LOCAL(). Here is a link. http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7irdb2ir19.htm
>
> I'm not sure how well getting this (or @@IDENTITY) works. It returns the
> most recently assigned value. You could get some elses. I remember this
> being an issue with SQL Server. If this is the case, the only safe way is
> to retrieve the record by the application/candidate/logical key(what really
> makes the record unique) after insert.

Initially, the IDENTITY_VAL_LOCAL only worked within the same transaction, ie before you commit, in which case you should get your own value.
As of v7 fixpack 4, you can use the function after committing the transaction. I don't know whether you would get someone else's value -- I would have thought the
"LOCAL" part of IDENTITY_VAL_LOCAL implies it's local to your connection. Anyone from IBM comment?

--greg

>
>
> "kevin knudson" <klk@knudsons.com> wrote:
> >
> >Frost I will be doing some heavy Stored Proc stuff later this week.
> >And I have figured out how to return data, although only string data. Requery
> >is really a backward way, does UDB have an equivalent to the @@IDENTITY
> field
> >in SQL. I'll check at work tomorrow if I have time, tomorrows a SQL Server
> >day. (I support UDB and SQL, mostly on NT)
> >
> >KlK, MCSE
> >klk@knudsons.com
> >
> >"MarkN" <mnuttall@nospam.com> wrote:
> >>
> >>Are you using the DB2 provider? I'm not sure if I will even work then.
> > Alot
> >>of the ADO databoundish techniques don't seem to work with DB2.
> >>See my discussion with Kevin on ADO and DB2 - http://news.dev-archive.com/cgi-bin/dnewsweb.exe?cmd=article&group=database.db2.general&item=249&utag=
> >>
> >>
> >>"Frost" <stevef@pedco.com> wrote:
> >>>
> >>>Oh Kay...
> >>>
> >>>I have a table on DB2 with 2 fields ID and Name. ID field in an Identity
> >>>field. When I add a new record to the Table (via ADO) I need to be able
> >>>to get the ID of the record I just added. The code below is and example
> >>>of how I can do it in SQL Server. UDB returns 0 for the lID? Can anyone
> >>>tell me how I can get the ID. If I do a .Requery I a can get it by moving
> >>>to the last record. But that is only if it is sorted by ID.
> >>>
> >>>Dim rsRec as Recordset
> >>>Dim lID as long
> >>>Dim sSQL as String
> >>>Dim db2 as Connection
> >>>
> >>>sSQl = "Select * From NameTable Order by Name"
> >>>rsRec.Open sSQL,db2,adOpenDynamic,adLockOptimistic
> >>>
> >>>rsRec.AddNew
> >>> rsRec![Name] = "Steve"
> >>>rsRec.Update
> >>>
> >>>lID = rsRec![ID]
> >>>
> >>>
> >>>
> >>
> >