RE: Best way to find ID of new record
- From: Kolbis Guy <KolbisGuy@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 28 Mar 2006 10:26:02 -0800
Hi,
In SQL you can use:
select @@identity from dbo.tmp
"John Brown" wrote:
I often need to add a new record to a table, find out what that new record’s.
auto-increment ID is, and add records to another table that refer the new
record in the first table using the before mentioned auto-incremented ID. For
example, lets call the first table Transaction and I want to add an Invoice.
I open a recordset, rsTrans, (with no records to minimize overhead) from the
Transaction table, rsTrans.addnew, put values in the fields/columns
(including only the invoice total amount $100, not individual amounts from
items), and rsTrans.update. In my experience this is where it gets squirrely.
Sometimes you can accurately refer to rsTrans(“ID”) and sometimes it is not
available or not accurate. Then I immediately want to open another no record
recordset, rsInvoiceItems, from the InvoiceItems table and add 3 records;
item1 for $60, item2 for $35, and item3 for $5 where each
rsInvoiceItems(“TransID”) = rsTrans(“ID”)
I know from trial and error that the following code works but I am not
confident that it will always work the way it should.
Dim conWW As ADODB.Connection
Dim rsTrans As ADODB.Recordset
Dim rsInvoiceItem As ADODB.Recordset
Dim i As Integer
Set conWW = New ADODB.Connection
conWW.Mode = adModeReadWrite
conWW.Open strConnection
Set rsTrans = New ADODB.Recordset
rsTrans.Open "SELECT * FROM Transactions WHERE 1=0", _
conWW, adOpenDynamic, adLockOptimistic
rsTrans.AddNew
rsTrans("Amount") = 100
rsTrans("TypeTrans") = "Invoice"
rsTrans("UserID") = User.ID
rsTrans.Update
Set rsInvoiceItem = New ADODB.Recordset
rsInvoiceItem.Open "SELECT * FROM InvoiceItems WHERE 1=0", _
conWW, adOpenDynamic, adLockOptimistic
rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 60
rsInvoiceItem("Description") = "item1"
rsInvoiceItem.Update
rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 35
rsInvoiceItem("Description") = "item2"
rsInvoiceItem.Update
rsInvoiceItem.AddNew
rsInvoiceItem("TransactionID") = rsTrans("TransactionID")
rsInvoiceItem("Amount") = 5
rsInvoiceItem("Description") = "item3"
rsInvoiceItem.Update
rsInvoiceItem.Close: Set rsInvoiceItem = Nothing
rsTrans.Close: Set rsTrans = Nothing
conWW.Close: Set conWW = Nothing
Thanks, John Brown
- References:
- Best way to find ID of new record
- From: John Brown
- Best way to find ID of new record
- Prev by Date: "Operation is not allowed when the object is closed" Error
- Next by Date: Re: pessimistic concurrency
- Previous by thread: Re: Best way to find ID of new record
- Next by thread: Re: Oracle stored procedure with table as input parameter?
- Index(es):
Relevant Pages
|
|
