get newly generated primary key

[Joe L]

Hi all,

I use VB6/ADO 2.5 to write middle tier dlls for front tiers. One function is to create new record in Oracle and return the newly created primary key to the front end. The primary keys is automatically generated by Oracle triggers when cn.Execute or rs.UpdateBatch runs.

My question is: How can I get the newly generated key without re-open the recordset again? Right now I have to re-open the recordset in order to get the primary key, but I am afraid that last record might not be the one I have just created.

Any help would be very appreciated. My code is like this:


cn.Open
rs.ActiveConnection = cn

' retrive fields including primary key field order by primary key field
rs.Open strSQL

rs.AddNew

' some code to assign values to rs, except the primary key

rs.UpdateBatch

' open again to get the newly generated primary key
rs.Open strSQL
rs.MoveLast

cn.CommitTrans

[Robert Moy]

Hello:

I hope understand what you mean. Do you mean after you create record with a primary key, you want to retreive it? If that is the case, try this


private Sub Command1_Click()
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields(0) = Val(Text1.Text)
Adodc1.Recordset.Fields(1) = Text2.Text
Adodc1.Recordset.UpdateBatch
End Sub





'Will retrieve your entries without you reopen the recordset if you are using textboxes

private Sub Command2_Click()
Adodc1.Recordset.UpdateBatch
Adodc1.Recordset.MoveLast
Text3.Text = Str(Adodc1.Recordset.Fields(0))
Text4.Text = Adodc1.Recordset.Fields(1)
MSHFlexGrid1.Refresh
End Sub





'Will retrieve your entries without you reopen the recordset if you are using flexgrid
private Sub Command3_Click()
Adodc1.Refresh
MSHFlexGrid1.Refresh
End Sub





Please send back on reply

[Joe L]

Thanks Robert,

However, my application is on the middle tier and is not allowed to use any front end controls. I have to use ADODB.Recordset to process database modifications. And here is the situation:

1. Open connection and recordset
2. Call .AddNew. Now I am at the last record which should be the new record.
3. Assign some data to the new record, except the primary key for that one is generated by Oracle trigger.
4. Call rs.UpdateBatch. Now I am at the first record
5. Call rs.MoveLast and I am supposed at the new record I have just created, but the primary key field is invalid.

I will get error like "Operation is not allowed in this context." if I call rs.Requery, which I thought that would refresh the newly created record at the first place.

I guess the ADO cannot get the new values generated by back end triggers until I re-open the recordset, which is risky for during this split of second, somebody else might creates another new record. I am afraid the Data control at the front end will have the same problem for I assume these data controls are built on top of ADO.

[michi]

Hi,
I found that you tried to retrieve the primary key BEFORE calling cn.CommitTrans, that means it hasn't been actually committed.
I just created a simply function and tested it. It works fine to me. The database I use is MS Sql server 7.0, but I did not use trigger to get create the primary key (ID), I use Identity. I think the ideas are similar. Don't you think so?

=====
Private Sub UpdateData()
On Error GoTo UpdateData_Error
Dim strSQL As String 'The SQL Command to execute
Dim rsRecord As ADODB.Recordset 'Recordset for holding loaded record.
'
' Create object
'
Set rsRecord = New ADODB.Recordset

'
' The test table only has two fields, "ID" and "Name". "ID" is the PK, and also
' Identity.
'
strSQL = "select * from test"

'
' Attempt to load in the database record. gadoWFIMSConn is the global connection
' of my project.
'
Call rsRecord.Open(strSQL, gadoWFIMSConn, adOpenDynamic, adLockBatchOptimistic)

rsRecord.AddNew
rsRecord!Name = "a"
rsRecord.UpdateBatch
'
'Now, let's show the primary key which was created just now.
'
rsRecord.MoveLast
MsgBox rsRecord!id

rsRecord.Close
Set rsRecord = Nothing

Exit Sub

UpdateData_Error:

If (rsRecord.State = adStateOpen) Then
rsRecord.Close
End If
Set rsRecord = Nothing

End Sub

==========

Hope this helps.


Regards,

Michi

[Joe L]

Thanks Michi.

You are right that there seems OK for SQL server, but it doesn't work if db is Oracle. If I run your sample code on Oracle table that its PK is self-generated by trigger, I will have PK = 0 when display the MsgBox.

My dll works as middle tier components that has to support both SQL Server and Oracle, so I have to write 2 sets of codes for different DBs before I can find out the way for Oracle.

I did not call cn.CommitTran right after UpdateBatch is sometimes I may be requested to add several new records. So I want to provide chance to client to rollback all the newly created records if necessary.

[RobZeilinga]

hi there , possibly another way for sql ( I don't know if this works in Oracle)

in SQL Srv when you create a new row in a table that has the Identity functionality activated, you can add a new row and get the new identity value through one SQL call:

sqlstr = "Insert into yourtable ( fields ) values ( your values ) Select @@IDENTITY as id"

execute the sql returning the answer into a record set
rs.open sqlstr,connection,other parms

rs(0) = new rows identity value

hope this helps
RobZ

[Joe L]

This is great Rob, but how about Oracle? Anybody?

[RobZeilinga]

Hi Again,

A friend of mine suggested that you try searching at

(oh no another forum!!) www.orafans.com

p.s. I think the term you are looking for is ROWID

Hope this helps
RobZ

[RobZeilinga]

Hi Joe,

I got this off www.orafans.com - perhaps this is what U R looking For?


>: Is there a way to get the rowid of a previously inserted record without >requerying for the rowid? kinda similar to a %rowcount function. Any ideas?
>: Thank you,
>
>: Ben
>
>The RETURNING clause does the job, and works with INSERT, UPDATE and DELETE.
>
>In your insert statement, you'll need something like ...
>
>INSERT INTO MY_TABLE (MY_COL)
>VALUES ('ABC')
>RETURNING ROWID INTO MY_ROWID;

Hope This Helps
RobZ

[Joe L]

Seems we are getting closer. But do I have to make the table to have a field storing MY_ROWID? My table already has PK field.

[whois]

RobZ is right:

This is some code copied from Microsoft KB article Q195224 PRB: ADO Not Returning @@IDENTITY Value After AddNew

The only thing missing is the found in the lines of code that build the SQL query below.

RobZ's post is way easier to understand- you just need to wrap it with the SET NOCOUNT ON and OFF lines. Don't forget the ';'.

Basically, if the option for NOCOUNT is OFF, meaning that the count of rows affected is returned, then you will get a run time error and no id. If NOCOUNT option is on, then no count will be returned, and you will have the last IDENTITY value added. You can also access the value using the <varname> = ADORs![RS] notation.


set ADORs = new ADODB.Recordset
With ADORs
set .ActiveConnection = ADOCon
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
'Uncomment this line and it works without the Unique index.
.Open "set NOCOUNT on;INSERT idTest(Col1, Col2) " & _
"VALUES('" & strCol1 & "', '" & dtCol2 & "');" & _
"SELECT @@IDENTITY as ID;set NOCOUNT OFF"

End With





whois