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 using SELECT 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, I strongly 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 

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted

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

End Sub

 

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  

    protected void 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!

 Hi

I have been trying to follow this but I made a syntax error

 

InsertCommand="INSERT INTO [Orders] ([CustomerID], [OrderDate], [ShipByDate], [ShipToAddress],[deliveryDate],[custpo])"+"VALUES (@CustomerID, @OrderDate, @ShipByDate,@ShipToAddress, @deliveryDate, @custPo)" +  "SET @NewParameter=SCOPE_IDENTITY();"  

 

Can anyone spot my mistake. This is driving me nuts.

 

Thanks 

Thanks all but I solved it using query builder. Great Solution. Excellent thread. 

For future reference, you should always start a new post if you have a question, even though you think you have found a thread that covers the topic you want answered.  This thread is many months old, and it's unlikely that any of the original participants will still be watching it (although in this case, I still visit regularly).  Since the post was marked as resolved a long time ago, no current members will be watching it at all.

So far as your question is concerned, you should find the answer here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=54 

 

Worked like a charm for me. I couldn't figure out how to set the parameter, but this showed me how. Specifically the post by Motley on 8-24-2007 @2:50 Thanks a million for the help.