Getting the automatically generated id after SqlDataSource.Insert()

Hello!

I have a SqlDataSource that inserts some data in a database. The field "id" is auto-increment. Is it possible to use the "id", this data-row got from the database automatically, directly after the SqlDataSource.Insert() command in my CodeBehind file?

Thank you!

You would need to append "SELECT SCOPE_IDENTITY()" to your insert command then retrieve the value in the Inserted event.

More info here (note, this shows a pure ADO.NET approach, but my be of use): http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

|||

Take a look at this FANTASTIC tutorial:

http://www.asp.net/learn/data-access/tutorial-01-cs.aspx

This tutorial has helped me a lot and I am using it now to build an application for our company.

Here you will see detailed instructions on how to create different queries and retrieve the id usingSELECT SCOPE_IDENTITY().

|||

Ah thank you!

I added the "SELECT SCOPE_IDENTITY"-statement at the end of my insertcommand. But if I try

int id = SqlDataSource1.Insert();

it returns me only "1", the number of affected rows.

 How can I tell him to return me the result from the SELECT SCOPE_IDENTITY-statement? 

|||I have never tried using the SqlDataSource to do this kind of thing. It is limited in it's useage. You may have uncovered another limit. You can try changing the insert command to a stored procedure which returns an output parameter, or add an output parameter to the current collection of InsertCommand parameters, or use plain ADO.NET.|||

Folow the instructions bellow and you will get what you need:

1- Create your sql connection statement:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);

2 - Create your insert and select statement together:

SqlCommand newCmd = new SqlCommand("INSERT INTO Orders (FirstName,LastName,Email)" + "Values (@.FirstName,@.LastName,@.Email)" + "SELECT SCOPE_IDENTITY()", conn);

3 - Define your parameters

newCmd.Parameters.Add("FirstName", SqlDbType.NChar, 50).Value = cFirstNTB.Text;

newCmd.Parameters.Add("LastName", SqlDbType.NChar, 50).Value = cLastNTB.Text;

newCmd.Parameters.Add("Email", SqlDbType.NVarChar, 50).Value = cEmailTB.Text;

4 - Then you perform insert/select and get your scope identity:

conn.Open();

newCmd.ExecuteNonQuery();

decimal ThisID =0;

SqlDataReader ServiceReader = newCmd.ExecuteReader();

while (ServiceReader.Read())

{

ThisID = (decimal)ServiceReader.GetValue(0);

}

ServiceReader.Close();

string ThisID = newLeadCmd.ExecuteScalar().ToString();

newLeadconn.Close();

Now, Istrongly advise you to start using Strongly-typed data sets. Take a look at the tutorial in the link I posted before. It will help you do some serious coding.

Take care!

|||

The SqlDataSource's insert internally does a ExecuteNonQuery. Unfortunately, that means that it will throw away the resultset generated by your SELECT SCOPE_IDENTITY().

The easiest way to work around this is to use a parameter value. Add an output parameter to your SqlDatasource's Insert Parameters declaritively. Then add this to the end of your insert command: " SET @.NewParameter=SCOPE_IDENTITY();" Then in the SqlDatasource_Inserted event, pick up the contents of the NewParameter parameter and do what you want with it (possibly setting a class/form level variable).

How to add an output parameter:

Select your SqlDatasource control. In the properties window, find the InsertQuery property and select the "..." button to call up the Command and Parameter editor. Click the Add property button. Click the show advanced properties button. Change the Direction property to "Output". Change the Type to "Int32".

How to retrieve the contents in the SqlDatasource_Inserted event:

Private LastInsertID as integer

ProtectedSub SqlDataSource1_Inserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Inserted

LastInsertID = e.Command.Parameters("@.NewParameter").Value

EndSub

Now you can either do what you want to do after an insert in your SqlDataSource_Inserted event, or if you need to manually call the insert method of the SqlDatasource you can do something like the following:

SqlDatasource1.Insert()

label1.text=LastInsertID.ToString()

|||

Thank you for your replies!

@.Motley:

That looks good, but I have a little problem with implementing your code. If I place

 protectedvoid SqlDataSource2_On_Inserted(object sender, SqlDataSourceStatusEventArgs e) { newID = (int)e.Command.Parameters["newID"].Value; }

in my CodeBehind file, the newID is always zero. I guess, the problem is, that the name of this method might be wrong.

How do I have to name this method correctly?

Thank you!

|||

Yes you do. If you go to your page in Design view and select the SqlDataSource, hit F4 and you will get the properties window. There is a lightning bolt there. Click that and you will be presented with a list of the control's events. Double click the Inserted event, and it will create an event handler automatically in your code-behind.

|||

It works! Great! Thank you all very much!