Getting ID from SQL after inserting

[Przemo]

Hi,

I have a datatable with 1 row. It consists of columns: name,
date, time and value. I use dataadapter to insert these data
to a SQL2000 table. Structure of the table is almost the
same. It contains all these 4 columns plus one more which
is a table PrimaryKey and IdentityColumn -
autoincrementing integer.
After SqlDataAdapter.Update I would like to get that value of
PrimaryKey assigned by sql2000 to my new row in sql table.
How can I do it ?

Thank in advance!

Przemo

 

[Dotnetified]

Let's assume this:

Table Customer:
CustomerId int Identity Not Null
FirstName varchar(100)
LastName varchar(100)
FavoriteColor varchar(100)

----

Your Dataadapter InsertCommand text should look something like this:

INSERT INTO Customer (FirstName, LastName, FavoriteColor) VALUES
(@FirstName, @LastName, @FavoriteColor); SELECT * FROM Customer WHERE
CustomerId = @@IDENTITY

After you perform your update, you should be able to retrieve the new ID
from the datarow that you added. Hope this helps . . .

Mike Joseph
Hypersite.net

 

[David Elliott]

I was looking to do basically the same thing.

The only difference would be
1) DataTable with MANY rows of data
2) Using DataAdapter pointing to a Stored Proc

To carry the previous example forward, would this be sufficient to allow
the DataTable to be updated with the primary key that could be checked
after returning from the insert???

Thanks,
Dave
(e-mail address removed)

=================================================
CREATE PROCEDURE up_Customer_add

@Firstname varchar(100),
@LastName varchar(100),
@Color varchar(100),
@CustomerID int Output

AS

insert into Customer(FirstName, LastName, FavoriteColor)
values (@Firstname, @LastName, @FavoriteColor)

set @CustomerID = @@IDENTITY
GO
=================================================