INSERT INTO then get autonumber

danny-t
01-11-2004, 07:49 PM
I'm trying to insert a new record into a table with an autonumber field (this much is fine) I then want to add that newly created autonumber as a foreign key into another table.

I.e.

insert into table A
insert table A's new autonumber into table B

any ideas on the easiest way to achieve this
Ta!

Optikal
01-11-2004, 07:51 PM
after doing the INSERT into the AutoNumber table, do "SELECT @@IDENTITY FROM MyTable" and that should return the newly created ID number for you.

LuckyGeekBoy
01-13-2004, 11:56 PM
after doing the INSERT into the AutoNumber table, do "SELECT @@IDENTITY FROM MyTable" and that should return the newly created ID number for you.

I need to perform this same operation, but using an Access DB via ADO. Unfortunately, Access does not support the @@Idendity fcn. I feel silly asking this, as this requirement must be an everyday neccessity, but alas I cannot figure out how to elegantly accomplish this task.

Thank You in advance!

LuckyGeekBoy
01-14-2004, 12:40 AM
after doing the INSERT into the AutoNumber table, do "SELECT @@IDENTITY FROM MyTable" and that should return the newly created ID number for you.

I need to perform this same operation, but using an Access DB via ADO. Unfortunately, Access does not support the @@Idendity fcn. I feel silly asking this, as this requirement must be an everyday neccessity, but alas I cannot figure out how to elegantly accomplish this task.

Thank You in advance!

-------------------------------------------------------------------
I stand corrected; Jet OLE DB version 4.0+ does in fact support the @@Indentity fcn!

In the interest of helping others who may be dealing with this problem, below is my particular (VB.NET) solution to the problem. I've outdented the required changes to my original code needed to return the indexed field:

' Assign the connection property.
Dim MyConnection As ADODB.Connection = Me.DBConn
Try

Dim command As New ADODB.Command
command.ActiveConnection = MyConnection
command.CommandType = ADODB.CommandTypeEnum.adCmdText
command.CommandText = Me.Query

' Begin the transaction
MyConnection.BeginTrans()
' Execute command agains connection
command.Execute()

' End Transaction
MyConnection.CommitTrans()

' Get the (AutoIncrement) Key value for this record
Dim strSQL As String = "SELECT @@Identity"
Dim rsNewAutoIncrement As ADODB.Recordset = New ADODB.Recordset
rsNewAutoIncrement.Open(strSQL, Me.DBConn, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText)
' Verify proper value has been returned
MsgBox("New Auto-increment value is: " & rsNewAutoIncrement(0).Value.ToString)

rsNewAutoIncrement.Close()
rsNewAutoIncrement = Nothing

Catch ex As Exception
' Error Trap any Error
' Set return value to False
blnErrorFlag = False
Try
Dim str As String = "An exception of type " & ex.GetType().ToString() & CtrlChrs.CrLf & _
" was encountered while performing the DataBase update transaction. " & _
"The File has not been updated in the DataBase! " & _
"The system returned the following error: " & ex.Message.ToString
MsgBox(mdlUtilityFcns.BlockFormatText(str, 60), MsgBoxStyle.Critical Or MsgBoxStyle.OKOnly, "Internal System Error")
' Attempt to RollBack the transaction
MyConnection.RollbackTrans()
Catch ex1 As Exception
' Error Trap Transaction RollBack
If Not MyConnection Is Nothing Then
Dim str As String = ex.Message.ToString & CtrlChrs.CrLf & _
"The File has not been updated in the DataBase!"
MsgBox(str, MsgBoxStyle.Critical Or MsgBoxStyle.OKOnly, "Internal System Error")
End If
End Try
Finally
MyConnection = Nothing
CloseDBConn()
End Try

I hope this helps!

Also check out the following link for additional details:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q232/1/44.ASP&NoWebContent=1

LGB

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum