returning SCOPE_IDENTITY() in Insert command with C# DataSets

G

Gus M

Hi,

I've got a bit of a problem, and from what I've read on here it should
be very easy but I just can't get it to work.

I've got a dataset of my table, and using a tableadapter I've created a
insert command through the wizard. This is all good except that I want
it to return the value SCOPE_IDENTITY() so that I know the PK (set to
auto increment) of the inserted row. If I edit the SQL command through
query builder so that it goes from:
-------------------

INSERT INTO [contact details]
(cnt_phone, cnt_fax, cnt_mobile_number,
cnt_direct_phone, cnt_extension, cnt_direct_fax, cnt_contact_address_1,
cnt_contact_address_2,
cnt_contact_address_3, cnt_suburb, cnt_city,
cnt_state, cnt_postcode, cnt_country, cnt_postal_address_one,
cnt_postal_address_two,
cnt_postal_address_three,
cnt_postal_address_four, cnt_website_address, cnt_email_address,
cnt_alternate_email, cnt_date_table_last_updated,
cnt_history,
cnt_table_last_updated_by_per_id_link)
VALUES
(@cnt_phone,@cnt_fax,@cnt_mobile_number,@cnt_direct_phone,@cnt_extension,@cnt_direct_fax,@cnt_contact_address_1,@cnt_contact_address_2,@cnt_contact_address_3,@cnt_suburb,@cnt_city,@cnt_state,@cnt_postcode,@cnt_country,@cnt_postal_address_one,@cnt_postal_address_two,@cnt_postal_address_three,@cnt_postal_address_four,@cnt_website_address,@cnt_email_address,@cnt_alternate_email,@cnt_date_table_last_updated,@cnt_history,@cnt_table_last_updated_by_per_id_link)
-------------------
to having this added onto the end:
----------------------
GO
SELECT cnt_id
WHERE cnt_id = SCOPE_IDENTITY()
-----------------

I get an "Unable to parse query text." error message.

I've been doing a lot of research on this and from what I've read this
should all work. I've changed the command type over from
ExecuteNonQuery to Scalar but it still doesn't work.

I'm using VS2005 release and SQL server 2005 release.


Any help would be great as I'm tearing my hair out at the moment!! :)
Thanks,
Gus.
 
O

Otis Mukinfus

Hi,

I've got a bit of a problem, and from what I've read on here it should
be very easy but I just can't get it to work.

I've got a dataset of my table, and using a tableadapter I've created a
insert command through the wizard. This is all good except that I want
it to return the value SCOPE_IDENTITY() so that I know the PK (set to
auto increment) of the inserted row. If I edit the SQL command through
query builder so that it goes from:
-------------------

INSERT INTO [contact details]
(cnt_phone, cnt_fax, cnt_mobile_number,
cnt_direct_phone, cnt_extension, cnt_direct_fax, cnt_contact_address_1,
cnt_contact_address_2,
cnt_contact_address_3, cnt_suburb, cnt_city,
cnt_state, cnt_postcode, cnt_country, cnt_postal_address_one,
cnt_postal_address_two,
cnt_postal_address_three,
cnt_postal_address_four, cnt_website_address, cnt_email_address,
cnt_alternate_email, cnt_date_table_last_updated,
cnt_history,
cnt_table_last_updated_by_per_id_link)
VALUES
(@cnt_phone,@cnt_fax,@cnt_mobile_number,@cnt_direct_phone,@cnt_extension,@cnt_direct_fax,@cnt_contact_address_1,@cnt_contact_address_2,@cnt_contact_address_3,@cnt_suburb,@cnt_city,@cnt_state,@cnt_postcode,@cnt_country,@cnt_postal_address_one,@cnt_postal_address_two,@cnt_postal_address_three,@cnt_postal_address_four,@cnt_website_address,@cnt_email_address,@cnt_alternate_email,@cnt_date_table_last_updated,@cnt_history,@cnt_table_last_updated_by_per_id_link)
-------------------
to having this added onto the end:
----------------------
GO
SELECT cnt_id
WHERE cnt_id = SCOPE_IDENTITY()
-----------------

I get an "Unable to parse query text." error message.

I've been doing a lot of research on this and from what I've read this
should all work. I've changed the command type over from
ExecuteNonQuery to Scalar but it still doesn't work.

I'm using VS2005 release and SQL server 2005 release.


Any help would be great as I'm tearing my hair out at the moment!! :)
Thanks,
Gus.
Click to expand...

The query builder can't parse multiple selects, nor can it parse the
scope_identity() function.

Save the query and add the second select after you save it.

Having said that, I'm not sure what you are trying to do will work with a
TableAdapter.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
G

Gus M

That explains it then.

I'll give stored procedures a go instead as that will do what I want. I
was just wanting to do everything within the IDE as it made life easy
with a large DB.

Thanks for the response.
Gus.
 
G

Gus M

That explains it then.

I'll give stored procedures a go instead as that will do what I want. I
was just wanting to do everything within the IDE as it made life easy
with a large DB.

Thanks for the response.
Gus.
 
O

Otis Mukinfus

That explains it then.

I'll give stored procedures a go instead as that will do what I want. I
was just wanting to do everything within the IDE as it made life easy
with a large DB.

Thanks for the response.
Gus.
Click to expand...

You're welcome Gus.

One comment regarding using an sp for inserts. Code the sp ending as

return scope_identity()

Then add a return parameter to your command object. After you execute the
command you just get the ID from the return parameter.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
C

Chris

That explains it then.

I'll give stored procedures a go instead as that will do what I want. I
was just wanting to do everything within the IDE as it made life easy
with a large DB.

Thanks for the response.
Gus.
Click to expand...

I thought you could run multiple statements in one command. The point to note with the command I saw in your example is that 'GO' is not a
vaild T-Sql statement. It is only used by query analizer and osql to mark the end of a batch.

Have you tried this:

INSERT INTO [contact details] ( ... ) VALUES ( ... );
SELECT SCOPE_IDENTITY();
 
J

Jim Rand

You might try something like this:

INSERT INTO Customer (CompanyName, ContactName, ContactTitle, Address, City,
Region, PostalCode, Country, Phone, Fax) VALUES (@CompanyName, @ContactName,
@ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone,
@Fax); SELECT CustomerID, CAST(TS AS INT) AS TS FROM Customer WHERE
(CustomerID = SCOPE_IDENTITY())

By the was, 'TS' is a timestamp field used for concurrency checking for the
related UPDATE and DELETE statements.


Chris said:
I thought you could run multiple statements in one command. The point to
Click to expand...
note with the command I saw in your example is that 'GO' is not a
vaild T-Sql statement. It is only used by query analizer and osql to mark the end of a batch.

Have you tried this:

INSERT INTO [contact details] ( ... ) VALUES ( ... );
SELECT SCOPE_IDENTITY();
Click to expand...
fetch the identity number.
 
Top