get last inserted ID with

[tarscher]

Hi all,

I have a database layer (DataSet) I use to attach my controls to. The
dataset is generated via the wizard. I want to know how I can get the
las inserted id I did with one of the queries I defined in that
databaselayer. I know of both @@IDENTITY and scope_identity() but I
can't get the id from that query.

I try:
int lastId = Convert.ToInt16(myDataSetTableAdaptor.GetLastIDQue ry());

where GetLastIDQuery() is the query that returns the last id ("SELECT
SCOPE_IDENTITY()") .

All help is greatly appreciated

Regards,
Stijn

[Eliyahu Goldin]

Stijn,

You have to do it in the same batch with the insert statement.

"INSERT ...;SELECT SCOPE_IDENTITY()"

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
<ta******@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

Hi all,

I have a database layer (DataSet) I use to attach my controls to. The
dataset is generated via the wizard. I want to know how I can get the
las inserted id I did with one of the queries I defined in that
databaselayer. I know of both @@IDENTITY and scope_identity() but I
can't get the id from that query.

I try:
int lastId = Convert.ToInt16(myDataSetTableAdaptor.GetLastIDQue ry());

where GetLastIDQuery() is the query that returns the last id ("SELECT
SCOPE_IDENTITY()") .

All help is greatly appreciated

Regards,
Stijn

[Phil Hall]

Dear Stijn

This may not be the most concise way of achiving it but I tend to use
Dataview objects with Dataset tables. You can use code that is easier
to follow with them.

e.g.

da.fill(ds)

'assume da, ds have been declared previously

dim dv as new DataView(ds.tables("tablename"))

dim drv as DataRowView = dv.AddNew

'Now load values into drv with statements like drv("columnName") =
value, except for the column that holds the identity which will be left
as null

drv.EndEdit()

da.Update(ds)

drv now holds the new identity in the appropriate column e.g. new_id =
drv("record_id")

Hope that's useful

[stijngoris]

Thanks, that worked. I also changed the query type to scalar.
Eliyahu Goldin schreef:

Stijn,

You have to do it in the same batch with the insert statement.

"INSERT ...;SELECT SCOPE_IDENTITY()"

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
<ta******@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

Hi all,

I have a database layer (DataSet) I use to attach my controls to. The
dataset is generated via the wizard. I want to know how I can get the
las inserted id I did with one of the queries I defined in that
databaselayer. I know of both @@IDENTITY and scope_identity() but I
can't get the id from that query.

I try:
int lastId = Convert.ToInt16(myDataSetTableAdaptor.GetLastIDQue ry());

where GetLastIDQuery() is the query that returns the last id ("SELECT
SCOPE_IDENTITY()") .

All help is greatly appreciated

Regards,
Stijn