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

27 Feb 2006 5:55 PM

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!

27 Feb 2006 8:20 PM

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.

Rico wrote:
Show quoteHide quote

> 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!
>
>

28 Feb 2006 12:15 AM

Dennis

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

--
Dennis in Houston

Show quoteHide quote

"Gman" wrote:

> 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.
>
> Rico wrote:
> > 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!
> >
> >
>

1 Mar 2006 6:58 PM

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!

Show quoteHide quote

"Gman" <nah> wrote in message news:OgquPt9OGHA.536@TK2MSFTNGP09.phx.gbl...
> 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.
>
> Rico wrote:
>> 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!