Retrieving Identity value of newly added row

Retrieving Identity value of newly added row

Post by Brian Leac » Wed, 13 Dec 2000 09:51:08



I'm inserting a new row in a table using an Insert SQL query.
One of the fields is an Identity (Autonumber) field, which
is the unique value for the row.

I execute the SQL statement and the row is inserted
properly using a JDBC driver.

My problem:  How do I grab the value of the identity
field in that newly added row?  The ResultSet doesn't
have the information because it was an INSERT query.

What I'm doing must be possible, right?

---
Brian Leach

 
 
 

Retrieving Identity value of newly added row

Post by Joseph Weinstei » Thu, 14 Dec 2000 03:27:38



> I'm inserting a new row in a table using an Insert SQL query.
> One of the fields is an Identity (Autonumber) field, which
> is the unique value for the row.

> I execute the SQL statement and the row is inserted
> properly using a JDBC driver.

> My problem:  How do I grab the value of the identity
> field in that newly added row?  The ResultSet doesn't
> have the information because it was an INSERT query.

> What I'm doing must be possible, right?

> ---
> Brian Leach


Yes. Just run the SQL you'd run if you were in isql to see it.
For MS SQLServer or Sybase, I believe it's


So you'd run that query through any statement made from the same
JDBC connection as you made the update. (It could be the same
statement).

Joe Weinstein at BEA, the home of WebLogic
--

PS: Folks: BEA WebLogic is in S.F. with both entry and advanced positions for
people who want to work with Java, XML, SOAP and E-Commerce infrastructure

--------------------------------------------------------------------------------
                    The Weblogic Application Server from BEA                    
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java    
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
                 http://www.bea.com/press/awards_weblogic.html

 
 
 

1. Retrieve the ID value of the newly added record

Hello all

I have a strange problem. I've worked with a database
(Microsoft SQL Server 2000) on a remote server through
my Visual Basic programs. I've used ADO recordset and
connection objects to handle the database connection
and query results or tables.
I've added new records with rs.AddNew method using
Keyset, Optimistic cursors, and easily got the ID of
this newly added record simply reading the rs!ID after the
rs.Update...

Or, like this

rs.Open "Users", cn, adOpenKeyset, adLockOptimistic,
adCmdTable

rs.AddNew
rs!Name="John"
rs!Surname="Andrews"
rs.Update
userID=rs!ID

It worked fine.. Till today. Today I created a merge
subscription from that database and pushed it to our other
server.

Now my programs cannot retrieve this ID's!
Now, when I go through the same procedure,

rs.Open "Users", cn, adOpenKeyset, adLockOptimistic,
adCmdTable

rs.AddNew
rs!Name="John"
rs!Surname="Andrews"
rs.Update
userID=rs!ID

userID is allways 0, that is, I cannot see the rs!ID value!

Anybody ?

Irene

2. Any scripts or stored procs to display space usage

3. Getting the identity value of a newly inserted row

4. db library with multiple keys ?

5. How to get Identity column values for newly inserted row

6. Basic TRIGGER problem

7. Retrival of newly created row auto generated identity column values

8. Execute of a stored procedure if server starts

9. Getting IDENTITY value of newly inserted row

10. Retrieving identity field value when adding new record

11. How to retrieve the IDENTITY (Counter) value of INSERTed rows

12. Get identity value from row added with AddNew ?