Export (0) Print
Expand All

SqlDataSource.Inserted Event

Updated: February 2010

Occurs when an insert operation has completed.

Namespace:  System.Web.UI.WebControls
Assembly:  System.Web (in System.Web.dll)

public event SqlDataSourceStatusEventHandler Inserted
<asp:SqlDataSource OnInserted="SqlDataSourceStatusEventHandler" />

Handle the Inserted event to examine the values of output parameters after an insert operation has completed. The output parameters are available from the SqlDataSourceStatusEventArgs object that is associated with the event.

For more information about handling events, see Consuming Events.

The following code example demonstrates how to retrieve data from Microsoft SQL Server and display it in a GridView control and how to use a DetailsView control to see details of a selected row in the GridView and as a form to insert new records.

NoteNote:

This example shows how to use declarative syntax for data access. For information about how to access data by using code instead of markup, see Accessing Data (Visual Studio).

Initially, the data is displayed in the GridView control, and the selected row of the GridView is also displayed in the DetailsView control. The GridView and DetailsView controls use different data source controls; the one that is associated with the DetailsView has the FilterExpression and FilterParameters properties, which ensures that the selected row of the GridView is displayed.

If you click the automatically generated Insert button of the DetailsView control, the DetailsView shows a different user interface, which is used to insert a new record. The example uses a stored procedure to insert records and returns the primary key of the inserted row. If you insert a record, the DetailsView automatically populates the InsertParameters collection with values from the bound columns and calls the Insert method. The DetailsView can infer the correct parameters from any BoundField object and a parameter for the TemplateField object when the ASP.NET two-way data-binding syntax is used. In this example, an additional parameter is added in the OnInserting event handler to handle the primary key that is returned by the stored procedure.

Finally, after data is inserted into the database by the DetailsView control, the OnInserted event handler is called to handle the Inserted event, the value of the primary key of the inserted row is displayed, and the DataBind method of the GridView control is called explicitly to refresh the data.

<%@Page  Language="C#" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
 private void On_Inserting(Object sender, SqlDataSourceCommandEventArgs e) {

    SqlParameter insertedKey = new SqlParameter("@PK_New", SqlDbType.Int);
    insertedKey.Direction    = ParameterDirection.Output;        
    e.Command.Parameters.Add(insertedKey);
 }

 private void On_Inserted(Object sender, SqlDataSourceStatusEventArgs e) {
    DbCommand command = e.Command;    

    // The label displays the primary key of the recently inserted row.
    Label1.Text = command.Parameters["@PK_New"].Value.ToString();

    // Force a refresh after the data is inserted.
    GridView1.DataBind();
 }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <asp:GridView
        id="GridView1"
        runat="server"
        AutoGenerateColumns="False"
        DataKeyNames="EmployeeID"        
        DataSourceID="SqlDataSource1">
        <columns>          
          <asp:BoundField HeaderText="First Name" DataField="FirstName" />
          <asp:BoundField HeaderText="Last Name" DataField="LastName" />
          <asp:BoundField HeaderText="Title" DataField="Title" />
          <asp:ButtonField ButtonType="Link" CommandName="Select" Text="Details..." />
        </columns>
      </asp:GridView>

      <asp:SqlDataSource
        id="SqlDataSource1"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind %>"
        SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
      </asp:SqlDataSource>

      <hr />

      <asp:DetailsView
        id="DetailsView1"
        runat="server"
        DataSourceID="SqlDataSource2"
        AutoGenerateRows="False"
        AutoGenerateInsertButton="True">
        <fields>
          <asp:BoundField HeaderText="First Name" DataField="FirstName" ReadOnly="False"/>
          <asp:BoundField HeaderText="Last Name" DataField="LastName" ReadOnly="False"/>
          <asp:TemplateField HeaderText="Title">
            <ItemTemplate>
              <asp:DropDownList
                id="TitleDropDownList"
                runat="server"
                selectedvalue="<%# Bind('Title') %>" >
                <asp:ListItem Selected="True">Sales Representative</asp:ListItem>
                <asp:ListItem>Sales Manager</asp:ListItem>
                <asp:ListItem>Vice President, Sales</asp:ListItem>
              </asp:DropDownList>
            </ItemTemplate>
          </asp:TemplateField>
          <asp:BoundField HeaderText="Notes" DataField="Notes" ReadOnly="False"/>
        </fields>
      </asp:DetailsView>


      <asp:SqlDataSource
        id="SqlDataSource2"
        runat="server"
        ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
        SelectCommand="SELECT * FROM Employees"
        InsertCommandType = "StoredProcedure"
        InsertCommand="sp_insertemployee"        
        OnInserting="On_Inserting"
        OnInserted ="On_Inserted"
        FilterExpression="EmployeeID={0}">
        <FilterParameters>
          <asp:ControlParameter Name="EmployeeID" ControlId="GridView1" PropertyName="SelectedValue" />
        </FilterParameters>
      </asp:SqlDataSource>

<!-- 
     -- An example sp_insertemployee stored procedure that returns
     -- the primary key of the row that was inserted in an OUT parameter.
     CREATE PROCEDURE sp_insertemployee 
        @FirstName nvarchar(10), 
        @LastName nvarchar(20) , 
        @Title nvarchar(30), 
        @Notes nvarchar(200), 
        @PK_New int OUTPUT
      AS
        INSERT INTO Employees(FirstName,LastName,Title,Notes)VALUES (@FirstName,@LastName,@Title,@Notes)
        SELECT @PK_New = @@IDENTITY
        RETURN (1)    
      GO
-->      

      <asp:Label 
        id="Label1"
        runat="server" />

    </form>
  </body>
</html>

Windows 7, Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2008 R2, Windows Server 2008, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98

The .NET Framework and .NET Compact Framework do not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

.NET Framework

Supported in: 3.5, 3.0, 2.0

Date

History

Reason

February 2010

Added link to information about programmatic data access.

Customer feedback.

Community Additions

ADD
Show:
© 2014 Microsoft