When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, May 2, 2007

Accessing and Updating Data in ASP.NET: Inserting Data

By Scott Mitchell


A Multipart Series on ASP.NET's Data Source Controls
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )

    Introduction


    ASP.NET 2.0's data source and data Web controls make working with data much easier than in previous versions. These controls allow for a declarative approach to working with data, without needing to write any cumbersome data access code. The articles we have explored in the series thus far have focused on understanding the core concepts of the data source controls and how to retrieve data from the database. In addition to retrieving data, the data source controls can also insert, update, and delete data.

    In this article we will focus on using the SqlDataSource control to insert records into a database. We will look at three examples: using a SqlDataSource to insert a record using values entered by the user through various TextBoxes, DropDownLists, and other Web controls on an ASP.NET page; inserting using a SqlDataSource and a DetailsView control; and determining the value of the just-inserted record's IDENTITY column. Read on to learn more!

    - continued -

    An Overview of Inserting Data Using the Data Source Controls


    The AccessDataSource, SqlDataSource, and ObjectDataSource controls all support inserting, updating, and deleting capabilities. In a nutshell, all three have an Insert method that, when invoked, kicks off the following inserting workflow:
    1. The data source's Inserting event is fired
    2. The inserting "action" occurs
    3. The data source's Inserted event is fired
    The inserting "action" differs between the data source controls. For the AccessDataSource and SqlDataSource, the action involves connecting to the specified database and executing the INSERT statement specified by the control's InsertCommand property. For the ObjectDataSource, the inserting "action" involves creating an instance of the data source's underlying object and invoking the specified InsertMethod. This article focuses on the SqlDataSource control; for a detailed look at inserting with the ObjectDataSource, refer to the "Editing, Inserting, and Deleting" tutorials in my Working with Data in ASP.NET 2.0 tutorial series.

    Let's explore the inserting "action" for the AccessDataSource and SqlDataSource controls in more detail. The INSERT statement or stored procedure specified by the control's InsertCommand uses a parameterized query. That is, if you use an ad-hoc SQL statement for the InsertCommand, that INSERT statement will use parameters like so:

    INSERT INTO TableName (ColumnName1, ColumnName2, ..., ColumnNameN)
    VALUES (@Parameter1, @Parameter2, ..., @ParameterN)

    In the Filtering Database Data with Parameters article, we looked at using parameters in the SelectCommand to filter the results, such as SELECT * FROM Products WHERE Price < @UnitPrice. The parameter - @UnitPrice, in this instance - has its value specified via the data source control's SelectParameters, which can specify a source for the parameter value. The source may be: a hard-coded value like "3.95", which would return all products less than $3.95; the value of a Web control on the page, allowing the user to enter a price bound in a TextBox; from the querystring; from session state; and so on.

    Likewise, the parameter values in the INSERT statement are assigned based on the parameters in the data source control's InsertParameters collection, and these parameters can use the same sources as the SelectParameters.

    The AccessDataSource and SqlDataSource controls, behind the scenes, use the standard ADO.NET classes to perform their data access. That is, they connect to the database using a SqlConnection or OleDbConnection object and specify the command text and parameters via a SqlCommand or OleDbCommand object.

    Given this information, the inserting workflow for the AccessDataSource and SqlDataSource can be more specifically expressed as:

    1. The data source's Inserting event is fired
    2. SqlConnection and SqlCommand (or OleDbConnection and OleDbCommand) objects are created
    3. The command object's CommandText property is assigned the data source control's InsertCommand property
    4. The parameters in the data source control's InsertParameters collection are added the command object's Parameters collection
    5. A connection to the database is established and the command is executed, thereby inserting the data
    6. The data source's Inserted event is fired
    The remainder of this article examines three inserting scenarios using the SqlDataSource control: inserting data through a manually-created Web form; inserting data using the DetailsView control; and retrieving the value of the just-inserted record's IDENTITY column. The full code for these demos is available in the download at the end of this article.

    Inserting Data Using a Manually-Created Web Form


    The demos available at the end of this article illustrate different techniques for inserting data into the Products table of the Northwind database. The Products table contains a number of columns. Each product record is uniquely identified by its ProductID, which is an AutoNumber/IDENTITY column. When inserting a record into this table, the only two columns that are required are ProductName and Discontinued; all other columns can accept a value of NULL.

    Imagine that we were tasked with creating a page that allowed users to add new records to the Products table by specifying the new item's name, category, price, and discontinued status. We could create a simple Web Form that included TextBoxes, a DropDownList, and a CheckBox control to collect these input fields, along with an "Add Product" Button control that, when clicked, would insert the new product into the database.

    In addition to these user input controls we could also add a SqlDataSource control to handle the actual insertion. We could set this control's InsertCommand to the following INSERT statement:

    INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
    VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

    Note the use of the parameters following the VALUES keyword. These sources for these parameters' values would simply be the user input Web controls on the page. Map these parameters' values in the InsertParameters collection using ControlParameters that point to the appropriate Web controls on that page. There are a variety of ways to accomplish this. From the Designer, click on the SqlDataSource and go to the Properties window. There you will see an InsertQuery option that, if clicked, displays the Command and Parameter Editor shown below. Here you can specify the InsertCommand, the parameters, and their source. Note that each of the four parameters uses a Control as its Parameter source, with the ControlID drop-down list set to the appropriate Web control on the page.

    Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:

    <asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."
          InsertCommand="INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"
          ProviderName="...">
       <InsertParameters>
          <asp:ControlParameter ControlID="ProductName" Name="ProductName" PropertyName="Text" />
          <asp:ControlParameter ControlID="Categories" Name="CategoryID" PropertyName="SelectedValue" />
          <asp:ControlParameter ControlID="UnitPrice" Name="UnitPrice" PropertyName="Text" />
          <asp:ControlParameter ControlID="Discontinued" Name="Discontinued" PropertyName="Checked" />
       </InsertParameters>
    </asp:SqlDataSource>

    Once the Web controls have been added to the page and the SqlDataSource's InsertCommand and InsertParameters properties have been correctly configured, inserting a new record is as simple as calling the data source control's Insert() method. That is, the only code you need to write is the following line of code (which would be placed in the "Add Product" Button's Click event handler):

    Protected Sub btnAddProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddProduct.Click
       'Add the new product!
       AddProductDataSource.Insert()
    End Sub

    Inserting Data Using a DetailsView Control


    A number of new data Web controls were introduced with ASP.NET 2.0. These include the GridView, DetailsView, and FormView, among others. The DetailsView and FormView controls display information about one record at a time (unlike the GridView, which displays information about a set of records). The DetailsView and FormView controls can also be configured to display an inserting interface. In short, you can use the DetailsView or FormView controls to create an interface for inserting data into the database without having to write a line of code!

    The DetailsView vs. the FormView Control
    The DetailsView and FormView controls have a lot in common - they both display one record at a time and can display interfaces for inserting and updating data. The difference between the two is that the DetailsView control is composed of DataFields (BoundFields, CheckBoxFields, TemplateFields, and so on), just like the GridView. This results in a very boxy appearance. The FormView, on the other hand, uses templates instead of DataFields; consequently, it allows for a much more flexible layout of its display, inserting, and updating interfaces.

    Start by adding a SqlDataSource control to the page and use the same InsertCommand as in the previous example:

    INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
    VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

    Next, add the parameters to the control's InsertParameters collection. Rather than using ControlParameters, use the default Parameter object. Also, the DetailsView we will create for this demo will not include an interface for the user to specify the category. Therefore, set the corresponding Parameter object's DefaultValue to "1". This will assign every product added through this page to the Beverages category.

    <asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."
          InsertCommand="INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"
          ProviderName="...">
       <InsertParameters>
          <asp:Parameter Name="ProductName" Type="String" />
          <asp:Parameter Name="CategoryID" DefaultValue="1" />
          <asp:Parameter Name="UnitPrice" Type="Decimal" />
          <asp:Parameter Name="Discontinued" Type="Boolean" />
       </InsertParameters>
    </asp:SqlDataSource>

    Next, add a DetailsView to the page and set its DataSourceID property to the ID of the SqlDataSource control (AddProductDataSource). From the DetailsView's smart tag, check the "Enable Inserting" checkbox. This adds a CommandField to the DetailsView and sets its ShowInsertButton property to True. A CommandField with this configuration displays a "New" Button when the DetailsView control is in its ReadOnly mode. When the "New" Button is clicked, a postback ensues and the DetailsView is shifted into its Insert mode, causing the CommandField to display "Insert" and "Cancel" Buttons.

    The SqlDataSource control does not contain a value for its SelectCommand, so no data will be displayed in the DetailsView. In fact, for this example we want the DetailsView to always be in an insertable mode (that is, we don't want to make the user have to click "New" to start adding a new record). Set the DetailsView's DefaultMode property to Insert to indicate that the DetailsView control should display just its inserting interface.

    Next, add two BoundFields and a CheckBoxField to the DetailsView, setting the HeaderText and DataField properties so that they are bound to the ProductName, UnitPrice, and Discontinued columns used by the SqlDataSource. Finally, set the AutoGenerateRows property to False.

    You can accomplish these two tasks from the Fields dialog box or by manually entering the control's declarative markup. To use the Fields dialog box, click the Edit Fields link from the DetailsView's smart tag. Add the two BoundFields and CheckBoxField and set their properties from the list on the right. To set the AutoGenerateRows property to False, simply uncheck the "Auto-generate fields" checkbox in the lower left corner.

    Alternatively, you can specify the DetailsView's fields and set the AutoGenerateRows property to False through the declarative syntax:

    <asp:DetailsView ID="DetailsView1" runat="server"

    AutoGenerateRows="False" DataSourceID="AddProductDataSource"


          DefaultMode="Insert" CellPadding="4" ForeColor="#333333" GridLines="None">
       <Fields>
          <asp:BoundField DataField="ProductName" HeaderText="Product Name:" />
          <asp:BoundField DataField="UnitPrice" HeaderText="Unit Price:" />
          <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued:" />
          <asp:CommandField ShowInsertButton="True" />
       </Fields>
    </asp:DetailsView>

    That's all there is to it! When a user visits this page and enters the name, price, and discontinued status of a product and clicks Insert, a postback occurs. The DetailsView automatically assigns the values assigned to its input controls to the SqlDataSource's corresponding InsertParameters before starting the inserting workflow. The net result is that a new record is inserted into the database without writing a lick of code and without having to manually map the SqlDataSource's InsertParameters to their sources (since the DetailsView does this for us automatically when Insert is clicked).

    This example illustrates only the simplest facilities of the DetailsView and omits important steps in a real-world application, such as input validation and customizing the inserting interface. For example, since the ProductName column is required, the insert will fail if the user leaves this textbox empty. Likewise, if the user puts in an invalid unit price value (like "expensive!"), an error will occur when attempting to insert the illegal value into the database. The download at the end of this article includes another DetailsView example that illusrated adding validation controls and customizing the inserting interface to prompt the user for the new product's category via a drop-down list of available category choices. For more information on customizing the DetailsView's inserting interface, see Customizing the Data Modification Interface (VB Version) (C# version).

    Inserting and Returning the Value of the Just-Inserted Record's IDENTITY Column


    Most database tables provide some mechanism to uniquely identify each record. There are a variety of approaches, but a very common one is the use of an IDENTITY column, which is also referred to as an AutoNumber. An IDENTITY column is one whose value is automatically assigned by the database system when a new record is added to the table. These values start at some seed (usually 1) and increment by some specified amount with each new record (usually incremented by 1). Therefore, if you add three new records to the table, the IDENTITY column values for those first three records will be 1, 2, and 3, respectively.

    When using IDENTITY columns a common requirement is to be able to retrieve the value of the just-inserted record's IDENTITY column. Perhaps after inserting a new record you want to automatically whisk the user to a details page where you need to pass along the IDENTITY column value through the querystring, or maybe you want to add additional records in a child table and need the just-inserted parent record's IDENTITY column value to properly link the child records to the parent. In either case, in Microsoft SQL Server you can use the SCOPE_IDENTITY() keyword to get the IDENTITY column value of the just-insert record.

    In order to pull back this information when using the SqlDataSource we need to do the following:

    1. Create a stored procedure that returns the just-inserted record's IDENTITY column value using an OUTPUT parameter. See Retrieving Scalar Data from a Stored Procedure for more information on this topic.
    2. Configure the SqlDataSource to use this stored procedure. This involves updating the InsertCommand to the name of the stored procedure created from step 1, setting the data source control's InsertCommandType to StoredProcedure, and adding an output parameter to the InsertParameters collection.
    3. To access the resulting output parameter's value we need to create an event handler for the SqlDataSource's Inserted event. Recall that this event fires after the insert "action" has been performed. Once we have the IDENTITY value of the just-inserted record we can use it as needed.
    In the database included in the download at the end of this article you will find a stored procedure named AddProductAndReturnNewProductIDValue that accepts four input parameters and has an OUTPUT parameter (@NewProductID). As the following T-SQL syntax shows, this stored procedure inserts a new record into Products and then assigns the value returned by SCOPE_IDENTITY() to @NewProductID:

    ALTER PROCEDURE dbo.AddProductAndReturnNewProductIDValue (
        @ProductName nvarchar(40),
        @CategoryID int,
        @UnitPrice money,
        @Discontinued bit,

        @NewProductID int OUTPUT


    )
    AS

    -- Insert the record into the database
    INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
    VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

    -- Read the just-inserted ProductID into @NewProductID
    SET @NewProductID = SCOPE_IDENTITY()

    Next, update the SqlDataSource to use the AddProductAndReturnNewProductIDValue as its InsertCommand instead of an ad-hoc SQL statement. Also, add an ouput parameter to the InsertParameters collection. Note that the output parameter in the InsertParameters collection is a Parameter object whose Direction property is set to Output:

    <asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."
          InsertCommand="AddProductAndReturnNewProductIDValue"
          ProviderName="..." InsertCommandType="StoredProcedure">
       <InsertParameters>
          <asp:ControlParameter ControlID="ProductName" Name="ProductName" PropertyName="Text" />
          <asp:ControlParameter ControlID="Categories" Name="CategoryID" PropertyName="SelectedValue" />
          <asp:ControlParameter ControlID="UnitPrice" Name="UnitPrice" PropertyName="Text" />
          <asp:ControlParameter ControlID="Discontinued" Name="Discontinued" PropertyName="Checked" />
          

    <asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" />


       </InsertParameters>
    </asp:SqlDataSource>

    Adding an output parameter to the SqlDataSource's InsertParameters collection adds an output parameter to the Parameters collection of the internal SqlCommand object used by the data source control during the inserting "action." The value of this parameter can be examined in the Inserted event handler. As the following event handler code shows, the internal SqlCommand object is accessible through the e.Command property in the event handler. Here we can grab the specific parameter instance and insepct its Value property to determine the IDENTITY column value of the just-inserted record:

    Protected Sub AddProductDataSource_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles AddProductDataSource.Inserted
       'Read the value of the @NewProductID OUTPUT parameter
       Dim newProductID As Integer = Convert.ToInt32(e.Command.Parameters("@NewProductID").Value)

       'Display confirmation message
       NewProductAddedMsg.Text = String.Format("Product {0} has been added to the database... This new product's ProductID value is {1}...", ProductName.Text, newProductID)
    End Sub

    Conclusion


    In this article we looked at how to use the SqlDataSource to insert data into a database. We worked through three examples: inserting using a manually-created Web Form; inserting from a DetailsView control; and retrieving the IDENTITY column value of the just-inserted record. In all three cases, the SqlDataSource control encapsulates many of the data access tasks, such as connecting to the database, creating the command object, and executing the parameterized query. In future installments of this article series we will take a similar in-depth look at updating and deleting.

    Until then... Happy Programming!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

    Further Readings:


  • Tutorials on Editing, Inserting, and Deleting Data (using the ObjectDataSource)
  • Inserting, Updating, and Deleteing Data with the SqlDataSource (VB Version) (C# version)
  • Customizing the Data Modification Interface (VB Version) (C# version)
  • A Multipart Series on ASP.NET's Data Source Controls
    ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article