Access SCOPE_IDENTITY() in CodeBehind C# TSQL

Question

I'm trying to retrieve SCOPE_IDENTITY() and place it in a parameter I can access in codebehind. My .aspx:

<asp:sqldatasource
    id="SqlDataSource1"
    runat="server"
    connectionstring="<%$ ConnectionStrings:myString %>"
    insertcommand="INSERT INTO [CountyHelp](County,City)
    VALUES (@County,@City) ;SELECT @Id = SCOPE_IDENTITY()">
    <insertparameters>      
          <asp:formparameter name="County"  formfield="County" />
          <asp:formparameter name="City"  formfield="City" />
          <asp:parameter Direction="Output"  Name="Id" Type="Int32" />             
    </insertparameters>

Then access it in this code behind:

protected void btn_send_Click(object sender, SqlDataSourceStatusEventArgs e)
{
    string sID = e.Command.Parameters["@Id"].Value.ToString();
    SqlDataSource1.Insert();
    Response.Redirect("documentsnew.aspx?id=" + sID);
}

Maybe it is my button code is not sending the parameter value:

     <asp:button
       id="Button1"
       runat="server"
       text="Add Help"
       onclick="btn_send_Click" 
       />

I get this error: Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0123: No overload for 'btn_send_Click' matches delegate 'System.EventHandler' — Kyle G
– Kyle G, Commented Jul 8, 2015 at 12:44
I'm currently using this for the Output parameter as well with no luck: <asp:Parameter Direction="Output" Name="Id" Type="Int32" /> — Kyle G
– Kyle G, Commented Jul 8, 2015 at 12:46
You can't use SqlDataSourceStatusEventArgs for a button click event. — DavidG
– DavidG, Commented Jul 8, 2015 at 12:47

psoshmo · Accepted Answer · 2015-07-08 13:08:27Z

2

The error you are getting is because you are using the wrong EventArgs for your button Click event. Use a regular EventArgs for the Click event and then see what happens.

You could grab the Scope_Identity by handling the SqlDataSources Inserted event so

<asp:sqldatasource
id="SqlDataSource1"
OnInserted ="On_Inserted"
runat="server"
connectionstring="<%$ ConnectionStrings:myString %>"
insertcommand="INSERT INTO [CountyHelp](County,City)
VALUES (@County,@City) ;SELECT @Id = SCOPE_IDENTITY()">
<insertparameters>      
      <asp:formparameter name="County"  formfield="County" />
      <asp:formparameter name="City"  formfield="City" />
      <asp:sessionparameter Direction="Output"  Name="Id" Type="Int32" />             
</insertparameters>
</asp:sqldatasource>

then

protected void btn_send_Click(object sender, EventArgs e)
{

     SqlDataSource1.Insert();

}

protected void On_Inserted(Object sender, SqlDataSourceStatusEventArgs e) 
{
    string sID = e.Command.Parameters["@Id"].Value.ToString();
      Response.Redirect("documentsnew.aspx?id=" + sID);
}

edited Jul 8, 2015 at 13:08

answered Jul 8, 2015 at 12:50

psoshmo

1,58010 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kyle G Over a year ago

I started out that way but just got NULLs in the QueryString but I'll try again now.

psoshmo Over a year ago

@KyleGMotion Edited my answer. Try this approach and see if it works for you

Kyle G Over a year ago

I'm getting this error with that code: CS0122: 'SendMail.On_Inserted(object, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)' is inaccessible due to its protection level

psoshmo Over a year ago

@KyleGMotion change private to protected for the On_Inserted event and let me know if that works

Kyle G Over a year ago

thanks for educating me about OnInserted it will be very valuable as I learn more.

|

Collectives™ on Stack Overflow

Access SCOPE_IDENTITY() in CodeBehind C# TSQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related