|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
returning SCOPE_IDENTITY() in Insert command with C# DataSetsI'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.
Show quote
On 23 Apr 2006 16:53:13 -0700, "Gus M" <gus.sa***@xtra.co.nz> wrote: The query builder can't parse multiple selects, nor can it parse the>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. 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 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. On 23 Apr 2006 20:13:16 -0700, "Gus M" <gus.sa***@xtra.co.nz> wrote: You're welcome Gus.>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. 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 On 23 Apr 2006 20:13:16 -0700, "Gus M" <gus.sa***@xtra.co.nz> wrote: 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>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. 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(); -------------------------------------------------------- Note there is no GO between the two statements (the semi colon is optional). And in the code you should be able to just use IDataReader.ReadScalar() to fetch the identity number. 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" <kee***@dynafus.com> wrote in message note with the command I saw in your example is that 'GO' is not anews:bjqs429sv1j8k539u7u3gi80mqvnsqigdc@4ax.com... > On 23 Apr 2006 20:13:16 -0700, "Gus M" <gus.sa***@xtra.co.nz> wrote: > > >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. > > I thought you could run multiple statements in one command. The point to > vaild T-Sql statement. It is only used by query analizer and osql to mark the end of a batch.> fetch the identity number.> Have you tried this: > > INSERT INTO [contact details] ( ... ) VALUES ( ... ); > SELECT SCOPE_IDENTITY(); > > -------------------------------------------------------- > Note there is no GO between the two statements (the semi colon is optional). > > And in the code you should be able to just use IDataReader.ReadScalar() to 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.  |
|||||||||||||||||||||||
Other interesting topics