"michel" <mm@mmm> wrote in message
news:Oc1rvgEVHHA.4796@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> i have to insert values into two tables.
> The first table (base table) has a primary key (type 'int') which is
> incremented automatically with each insert.
> The second table (detail table) contains a field (type 'int') which must
get
> the value of the primarty key of the last record in table 1.
> So if the primary key value of the last record in table1 is e.g. 24, then
> one field in table 2 must get that value.
>
> I tried this but doesn't work: (nrec is always 0)
>
> Dim nrec As Integer
> insert into table1 (field1, ....) values( ....)
> ...
> nrec = comd.ExecuteScalar()
> comd.ExecuteNonQuery()
>
> insert into table2 (field1, ....) values(nrec ....)
> comd.ExecuteNonQuery()
>
>
> Thanks for help
> Michel
>
>

"michel" <mm@mmm> wrote in message
news:Oc1rvgEVHHA.4796@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> i have to insert values into two tables.
> The first table (base table) has a primary key (type 'int') which is
> incremented automatically with each insert.
> The second table (detail table) contains a field (type 'int') which must
> get the value of the primarty key of the last record in table 1.
> So if the primary key value of the last record in table1 is e.g. 24, then
> one field in table 2 must get that value.
>
> I tried this but doesn't work: (nrec is always 0)
>
> Dim nrec As Integer
> insert into table1 (field1, ....) values( ....)
> ...
> nrec = comd.ExecuteScalar()
> comd.ExecuteNonQuery()
>
> insert into table2 (field1, ....) values(nrec ....)
> comd.ExecuteNonQuery()
>
>
> Thanks for help
> Michel

> First of all, for a table defined with an IDENTITY attribute, you cannot
> include that field in the INSERT statement list.
>
> Next, SET @var = SCOPE_IDENTITY() will retrieve the last IDENTITY value
> generated within the scope of your batch. (Be careful; there is also an
> @@IDENTITY function, but it will return the last inserted IDENTITY in the
> database, not necessarily within the same scope as yours).
>
> You can then use the @var variable just populated to INSERT into dependent
> tables.
>
> Sincerely,
>
>
> Anthony Thomas
>
>
> --
>
> "michel" <mm@mmm> wrote in message
> news:Oc1rvgEVHHA.4796@TK2MSFTNGP05.phx.gbl...
>> Hi,
>>
>> i have to insert values into two tables.
>> The first table (base table) has a primary key (type 'int') which is
>> incremented automatically with each insert.
>> The second table (detail table) contains a field (type 'int') which must
> get
>> the value of the primarty key of the last record in table 1.
>> So if the primary key value of the last record in table1 is e.g. 24,
>> then
>> one field in table 2 must get that value.
>>
>> I tried this but doesn't work: (nrec is always 0)
>>
>> Dim nrec As Integer
>> insert into table1 (field1, ....) values( ....)
>> ...
>> nrec = comd.ExecuteScalar()
>> comd.ExecuteNonQuery()
>>
>> insert into table2 (field1, ....) values(nrec ....)
>> comd.ExecuteNonQuery()
>>
>>
>> Thanks for help
>> Michel
>>
>>
>
>