CODEWITHSUNDEEP
c#sql.nett-sqlazure
Kyle G
Kyle G

Reputation: 149

Access SCOPE_IDENTITY() in CodeBehind C# TSQL

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" 
       />

Upvotes: 0

Views: 246

Answers (1)

psoshmo
psoshmo

Reputation: 1550

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);
}

Upvotes: 2

Related Questions