Getting primary key from new record instered into one table using ADO.net

[Rico]

Hello,

I have a situation where I have a normalized table (Table1=InvoiceHeader,
Table2=invoicedetails). When I create an InvoiceHeader and Details, I'd
like to save the invoice header first, then associated the detail records
with that invoice by storing the InvoiceHeaders primary key (an incrimental
identifier) in the invoiceDetails table. How do I go about this?

THanks!

 

[Gman]

This is a snippet of how I do it with VB6, connecting using ADO:
rs is a recordset, cn is a connection, cmd a command
With cmd
set .ActiveConnection = cn
.CommandType = adCmdText
.CommandText = mySQL ' the insert string

' Run the command (perform the Insert)
.Execute , , adExecuteNoRecords
If Err.Number <> 0 Then GoTo ErrorHandler

' Get the new AutoNumber value
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
AutoNumberValue = rs(0).Value
End With

I haven't done this in ADO.NET/VB.NET yet so I don't know if it works...

If this doesn't work for you I would suggest you:
(a) post back with details of your database type and how you connect to it.
(b) alternatively you could consider using a GUID for the PK approach.
That way you define the PK before you perform your insert and therefore
you already have it for the associated inserts -- you don't need to get
it back from the database, just know whether or not your insert was
successful.

 

[Guest]

Yes, the @@IDENITY works in ADO.Net at least for Access DataBases. I think
SQL databases have a different approach.

 

[Rico]

Hi GMan,

Thanks. It will take me some fighting through the variables you have here
(sorry I'm a newbie) to see if I can get it to work, but will let you know.
I'm also unfamiliar with the "PK approach" (did I mention I was a newbie?)

I'm using an Access database as a back end. Right now it's using an ADO.net
connection built "from scratch". To this point, all that's been required is
just updating stand alone tables.

Any help would be great.

Thanks!