How to return identity value from sqldatasource.insert() in code behind?

Question

I want to return id from insert. I found solution it's use select scope_identify. But I want example for my code.

sqlDatasource ds = new sqlDatasource();
ds.InsertCommand = "INSERT INTO Product (Name) VALUES (@Name)";
ds.InsertParameters.Add("@Name", txtName.text);
ds.Insert();

Answer 1

The exact answer, which is difficult to find, is here: SQLDataSourceIdentity Post

Which refers user here: Retrieving the Just-Inserted ID of an IDENTITY Column Using a SqlDataSource Control

Step 1 - add an Output Parameter

 <asp:Parameter Direction="Output" Name="NewID" Type="Int32" />

Step 2 - Add additional statement after insert command in SqlDataSource InsertCommand

SELECT @NewID=Scope_Identity()

Step 3 - Retrieve inserted value in SqlDataSource_Inserted event handler

e.command.Parameters("@NewID").value.tostring

Answer 2

Having not found an acceptable answer after searching the internet for 3 days; this is what works:

Using an example of inserting a user name and returning an Id...

Create a stored procedure similar to this:

create procedure Users_Insert 
      @UserName varchar(25) 
     ,@UserId int = null output
as

insert into Users (UserName) values (@UserName)

select @UserId = scope_identity();

return @UserId;

Now in your aspx page configure your SqlDataSource as such:

<asp:SqlDataSource ID="dsUsers" runat="server"
        ConnectionString="<%$ ConnectionStrings:dbConnection %>"
        InsertCommand="Users_Insert" InsertCommandType="StoredProcedure" OnInserted="dsUsers_Inserted">
        <InsertParameters>
            <asp:ControlParameter ControlID="txtUserName" Name="UserName" Type="String" PropertyName="Text" />
             <asp:Parameter Name="UserId" Type="Int32" Direction="ReturnValue" />
        </InsertParameters>
    </asp:SqlDataSource>

Note the OnInserted event is wired up.

Now, in your code behind, perhaps in a Save button click event, call the insert method:

 dsUsers.Insert();

And finally, in the dsUsers_Inserted method that we previously wired up, get the identity:

protected void dsUsers_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
     int userId = Convert.ToInt32(e.Command.Parameters["@UserId"].Value);
}

Hope this helps at least one person.

Happy coding!

Answer 3

An INSERT statement cannot return anything. You have to execute as SELECT to get the scope identity. If you want to execute it all in one statement, you cannot do it using SqlDataSource.Insert. As the documentation says, it returns an int counting the rows affected by the statement.

Instead, you could do something like this:

string query = "INSERT INTO Product (Name) VALUES (@Name);Select SCOPE_IDENTITY();;";
SqlCommand sqlCommand = new SqlCommand(query, connection);
connection.Open();
string NewID= sqlCommand.ExecuteScalar();

Hope I helped!

Answer 4

If you are working with oracle, RETURNING INTO might be what you are looking for. It allows you to save your new id in an output variable. I have only used it with ExecuteNonQuery() though, so it might not work with Insert()

If you are working with sql developer, OUTPUT might help, though I have not used that one myself yet.