Get identity after insert using default using Text Sql Commands

Question

User714464429 posted
I cannot for the life of me get the Identity after inserting a record into a table using the Table Adapater.
I am using Text SQL Commands.
I have configured the table adapter to "Refresh the Data Table" and the Insert SQL Command:

INSERT INTO [Item] ([name], [description]) VALUES (@name, @description);
SELECT itemId, name, description FROM Item WHERE (itemId = SCOPE_IDENTITY())

The Visual Studio generated Insert Method used the following to execute the InsertCommand

                int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
                return returnValue;

This returns the number of rows affected.
I've tried changing it to: int returnValue = (int)this.Adapter.InsertCommand.ExecuteScalar();
however it returns null;

I've tried playing with the InsertCommand Parameters and creating a new parameter and setting it as a ReturnValue however this doesnt work either.
I know this can't be this hard to do, but I cannot get it and none fo the posts on the site give a working solution.

Thanks

Answer 1

User1224304770 posted

Hi, why not create a stored procedure to perform the insert which returns the current identity? Here is a very simple example:

CREATE PROC sp_getIdentity @id int output,@name varchar(20)
as

INSERT INTO t1 (name) VALUES (@name);
select @id = SCOPE_IDENTITY()
go

Then call the stored procedure in a sql command:

SqlCommand cmd = new SqlCommand("sp_getIdentity", connection);
            connection.Open();
            SqlParameter sp= new SqlParameter("@id",SqlDbType.Int);
            sp.Direction = ParameterDirection.Output;
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(sp);

            int id=sp.Value.ToString();

Answer 2

User714464429 posted
I want to stay away from Stored Procs for now. Currently performance is not an issue and I would rather not have to worry about deploying the stored procs whenever I add another query code-side.

Here is another thing - I am using the GetData.. sql accessor model which returns a DataTable. On Insert, I have to pass each field as an indivdual parameter instead of a DataRow object. Would implementing the Fill interface help me out any?

Thanks

Answer 3

User714464429 posted
I found out how to get the identity of an insert using a SQL Text Command (not a Stored Procedure). I wanted to get the new identity id after an insert into my Item table while leveraging the Visual Studio 2005 generated DataSetTableAdapter class and .xsd interface.

Here is how I was able to do it:

1) Create a  TableAdapter in the.xsd file
2) Add a GetData() Query to the TableAdapter, under Advanced Options selected "Generate Insert, Update and Delete Statements".
3) In the DataSet Designer right-click on the TableAdapter and select Properties.
4) In the Properties panel expand InsertCommand and append to the CommandText: "SELECT SCOPE_IDENTITY() AS itemId;" (itemId is the name of my table's identity column)
5) Edit the InsertCommand > Parameters
6) Add a new Member
6a) Set ColumnName: itemId (itemId is the name of my table's identity column)
6b) Direction: ReturnValue
6c) ProviderType: int (or whatever your column's dataType is)
6d) SourceColumn: itemId (itemId is the name of my table's identity column)
6e) Parameter Name: @ReturnValue
7) Save the XSD file.
8) Open ItemDataSet.Designer.cs with View Code
9) Locate your Insert method

10) It will contain code that looks like:
            try {
                int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
                return returnValue;
           }

Change this to:
            try {
                int returnValue = (int)this.Adapter.InsertCommand.ExecuteScalar();
                return returnValue;
           }
10a) When i ran the above code an exeption was thrown - it turned out that my ExecuteScalar() was returning a decimal even tho the itemId column is of type int32. I am looking into why this is, but in the meantime, the quick fix this to merely change the code to:

            try {
                decimal returnValueDecimal = (decimal)this.Adapter.InsertCommand.ExecuteScalar();
                int returnValue = System.Convert.ToInt32(returnValueDecimal);
                return returnValue;
            }

and voila - when i insert a record using the Insert(..) method, the new itemId is returned!

BEWARE! if you start mucking around in the ItemDataSet.xsd Designer mode and regenerate the Insert statement all these changes will be lost and you will have to go through the steps again.

Please post back if you run into problems using this method!

Thanks

Answer 4

User1224304770 posted
Hi empire29, you did a great job![:)]

Answer 5

User316086421 posted
empire.  Thanks for the help.  I had the same problem using a SP recently.  I changed the auto-generated code and got it to work using a decimal.  I didn't really like the idea of changing the auto-generated code though.  So, I worked a little more on the problem and found the following would work to return an int datatype back to the table adapter.  Thus, the exception was not thrown and the value in the @RETURN_VALUE parameter is correct.  It is very similar to what lori_Jay did, but uses a return value instead of an OUTPUT parameter.

In the SP declare an int

DECLARE @RETURNVALUE int

then, after you inserted the row that generates your new identity value

SELECT @RETURNVALUE = SCOPE_IDENTITY()

then at the bottom of your SP


SELECT @RETURNVALUE

Now you can call an insert method from your typed TableAdapter and get the identity back as the return value (as an int).

Answer 6

User-771894964 posted

Empire,

If you are already adding a new parameter, you don't have to change the .net generated code. In your steps, you can:

6b). Set direction to output.

4). Change the SQL SELECT SCOPE_IDENTITY() to

SELECT @ReturnValue=SCOPE_IDENTITY().

You don't have to make the changes (10, 10a) in ItemDataSet.Designer.cs. I have tested it in VB other than C#.

Good luck.

Answer 7

User521627780 posted

Dear Empire,

Great work! This was exactly the solution I was looking for!

Unfortunately, however, I am getting stopped in the middle of the proces, because there is no (or I cannot access) the dataset.designer class, probably because I am not using Visual Studio, but Visual Web Developer Express.

 So, alas, my insertcommand still returns the bloody useluss nr of rows affected... [:(]

Cheers,

 Fedor

 

Answer 8

User521627780 posted

I overlooked jz8's answer that actually contains the solution to this problem.

 However, when I now call the insert method, I get an error message:

"Must declare the variable '@ReturnValue'."

Why do I have to declare a variable that is designated as output!?  [:S]

 

Answer 9

User630252983 posted

After a little trial and error I've figured out something that works for me, and hopefully will help.

 

You create your TableAdapter as usual in the XSD file, be sure to set the option to refresh the data table after udpate/insert under advanced options.  If you have not done this, just go back to the first fill/get and reconfigure it.

 

Next create your insert statement, you should notice a select statement appended to the end automatically.  You will need to delete all of the extra columns from the statement so it only returns the identity column.  Once you have done that go to the properties of the insert statement, and set the ExecuteMode to Scalar, which will make it return a single value, in this instance the identity.

 

The insert satement should look something like this.

 

INSERT INTO TABLE (COLUMN1, COLUMN2, ...)
VALUES     (@COLUMN1,@COLUMN2, ...);
SELECT IDENTITY_COLUMN FROM TABLE WHERE (IDENTITY_COLUMN = SCOPE_IDENTITY())
 

Then in your code do something like this

 

int identity = Convert.ToInt32(TableAdapter.InsertMethod(value1, value2, value3)); 

 

You now have your identity.

 

Hope this helped. 

Answer 10

User1663397006 posted

I use an OUTPUT Parameter by going into the tableadapter's insert method and adding my own output parameter.  I open my stored procedure and add the same output parameter as seen below.  Whenever you call the insert method in your code, just create an integer variable to be used as a parameter.

ALTER   PROCEDURE dbo.usp_insert_honorsapplication
(
@col1 varchar(50),
@col2 varchar(50),
@ID int OUTPUT
)
AS

INSERT INTO [mytable] ([col1], [col2], )
VALUES (@col1, @col2,)
SET @ID = SCOPE_IDENTITY()
SELECT reqID, col1, col2
FROM mytable WHERE (reqID = SCOPE_IDENTITY())

 

Answer 11

User1663397006 posted

I use an OUTPUT Parameter by going into the tableadapter's insert method and adding my own output parameter.  I open my stored procedure and add the same output parameter as seen below.  Whenever you call the insert method in your code, just create an integer variable to be used as a parameter.

ALTER   PROCEDURE dbo.usp_insert_mytable
(
@col1 varchar(50),
@col2 varchar(50),
@ID int OUTPUT
)
AS

INSERT INTO [mytable] ([col1], [col2], )
VALUES (@col1, @col2,)
SET @ID = SCOPE_IDENTITY()
SELECT reqID, col1, col2
FROM mytable WHERE (reqID = SCOPE_IDENTITY())

 

Answer 12

User-1234544320 posted

HI -

found your solution very useful - have you ever figured out how to do this for an update query? (I'm updating not by ID, put other params and need the ID of the modified record back).

Thanks for any help - I am going nuts with that ...

Oliver

Answer 13

User-1155992655 posted

 

I found JonnyP's solution the easiest to implement.  However you need to make sure the ExecuteMode property on the query is set to "scalar".  Otherwise you get a value of '1'

Hope this helps.

Answer 14

User-1660523257 posted

Awesome! this worked for me.  I was actually trying to do a "SELECT @ID = SCOPE_IDENTITY()" in my stored procedure, but this was returning NULL when I ran my program.

 Changing SELECT to SET works. [:)]

 

Answer 15

User-381147361 posted

I'm struggling with this as well in a Windows form application, using designer-configured DataSets.

These are my tables:

Student (parent)

• StudentID (int, pk)
• Name (varchar)
• BirthDate (datetime)

Teacher (parent)

• TeacherID (int, pk)
• Name (varchar)
• BirthDate (datetime)

Student_Teacher (child)

• StudentID (int, pk, fk)
• TeacherID (int, pk, fk)

The designer has built DataTables and DataAdapters for each table. 

I have a form with a BindingNavigator and TextBoxes that iterates through teacher records.  I've also dragged a Student_Teacher DataGridView onto the form to show students corresponding to the currently displayed teacher.  I'm having difficulty inserting a new record from this screen with a new Teacher and his/her corresponding Student_Teacher students.

In the designer, I'm not sure how to configure the project to pass the returned identity variable from one DataAdapter to another DataAdapter's insert since the whole save process is encapsulated in the UpdateAll() method.  So, to be specific, how would one return the inserted TeacherID identity from the Teacher table, and then pass it to the Student_TeacherTableAdapter for its insert, so that it seamlessly occurs in the UpdateAll() method?  SQL Server Profiler shows it's currently trying to insert the AutoIncrementSeed/AutoIncrementStep value of -1 into the TeacherID column for each new record in the Student_Teacher table.

 
        private void teacherBindingNavigatorSaveItem_Click_1(object sender, EventArgs e)
        {
            this.Validate();
            this.teacherBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.testStudentTeacherManyToManyDataSet);
        }
 

I've experimented with the TeacherTableAdapter - InsertCommand - Parameters, but cannot see how to map the return value over to the Student_TeacherTableAdapter as an InsertCommand argument.

Thanks, I'm so glad that I found this thread after all my searching!

Answer 16

User483937376 posted

Hi,

 Can anyone tell me how to set the Exectue method in Visual Stuido? I can not look at the Properties of the insert method ? i don't see the option? i'v expanded everything, right clicked and gone through menues, option and wizrds etc....

 

Please. 

 

Thank you.

Answer 17

User-1155992655 posted

 

Choose the query from the DataTable in the DataSet Designer and then choose Execute mode from the Properties Window. Example below.

http://screencast.com/t/2wVJo4hdx<o:p>  </o:p>

Answer 18

User483937376 posted

 Hi, thks for that,

 

still a problem, the Insertcommand isn't listed uder the table adapter only in the tables adapter's properies... :) i only have few configuartion rpoperies for it therefore.. like command type, command text, parameters....modifier name.

 any other suggestions?

 

Thank you again for your previous reply.

Answer 19

User483937376 posted

 Hi,

 I'm having a problem....

 I'v followed the original posters advice and it works, but if every time i open my solution up i have to go back into the Datasets code and chnage ExecuteNonQuery to ExecuteScalur

 This is a bit annoying..

 Does anyone have a work around please? 

Answer 20

User-186718899 posted

To get the indentity after the insert query see this

http://www.plugins4asp.net/tipsdetails.aspx?ID=36 

Answer 21

User-200165662 posted

on page:

<asp:sqldatasource id="whateverSqlDataSource" ... OnInserted="onInsertedMethod" ... InsertCommand=" (your insert command)...; SELECT @paramName=SCOPE_IDENTITY();">

<InsertParameters>

...

<asp:Parameter Name="paramName" Direction="Output" Type="Int16" />

</InsertParameters>

</asp:sqldatasource>

on code behind (.cs in this case):

protected void onInsertedMethod(object sender, SqlDataSourceStatusEventArgs  e)
{

whatever(e.Command.Parameters["@q"].Value.ToString());

}

Answer 22

User-1300606171 posted

That's a pity that most users get stuck with the designer.

What's the purpose of it, since it most often make since worse: a lot of users do experience huge pb with it ...

Well, I followed the advice on this thread and could handle to retrieve the scope_identity WITHOUT Stored Proc.

For those still looking, here's a reminder fine tuned for my solution :

You create your TableAdapter as usual in the XSD file, (note: designer is fine) be sure to set the option to refresh the data table after udpate/insert under advanced options.  If you have not done this, just go back to the first fill/get and reconfigure it.

 Next create A NEW insert statement, you should notice a select statement appended to the end automatically.  You will need to delete all of the extra columns from the statement so it only returns the identity column.  Once you have done that go to the properties of the insert statement => you have a property for that ! just (right) click the new query, and set the ExecuteMode to Scalar, which will make it return a single value, in this instance the identity.

 The insert satement should look something like this.

INSERT INTO TABLE (COLUMN1, COLUMN2, ...)
VALUES     (@COLUMN1,@COLUMN2, ...);
SELECT ID FROM TABLE WHERE (ID = SCOPE_IDENTITY())

Then in your code do something like this

int identity = Convert.ToInt32(TableAdapter.InsertMethod(value1, value2, value3)); 

Works for me ! Thanks !

Hope this helped.

Answer 23

User1933477727 posted

This was the ticket!  Thank you so much for posting this empire!

Answer 24

User-223700580 posted

I'm trying out this to see how it works.