Inserting Data into a Microsoft SQL 2008 Database in ASP.NET 3.5

It is recommended that you read the first part as well, to completely create the demo project in that tutorial in Visual Web Developer Express 2008. That project will be used and extended in this tutorial.

Case Study Example: Home Renovation Project

The case project study of the previous article displays database records in the web browser and then lets the user (the supervisor) either update or delete records (see screen shot): http://www.dotnetdevelopment.net/tutorials/gridview6.jpg

Let's improve this by adding a feature that can accept new records from the browser and put them into the database. You can do this by following the plan below:

You can create a new ASPX file called Insert.aspx which will be the page used for inserting records into the MS SQL database. The yellow color means that it is a new feature to be added, while the gray one is for the existing features of the previous web project.

Of course, there should be an HTML hyperlink from the Default.aspx going to Insert.aspx and vice versa, so users can easily navigate though web contents for adding and updating data.

Since Insert.aspx will be used to insert records, it needs ASP.NET controls for communicating with the Renovation project MS SQL database (details will be discussed below).

Create the Insert.aspx File in Visual Web Developer

Take the following steps to create the Insert.aspx file using Visual Web Developer Express 2008:

1. Launch Visual Web Developer.

2. Open "renovationproject" in Visual Web Developer.

3. Once the project has been loaded, go to File à New File à Under "Visual installed templates," select "Web Form." Type the file name to be Insert.aspx, select the language "Visual Basic." Check "Place code in separate file." See the screen shot below:

Finally, click "Add."

4. Visual Web Developer will automatically create a new file named Insert.aspx, and it will be placed in the same directory as Default.aspx. See screen shot below:

Add/Configure SqlDataSource Controls to Insert.aspx

Since Insert.aspx will communicate with the Renovationproject database, which uses Microsoft SQL Server, you need add SqlDataSource controls. To do this, you need to go to View à Toolbox à SqlDataSource; click and drag it to sit between: 

<form id="form1" runat="server">

    <div>

AND

</div>

</form>

Rename the SqlDataSource ID from "SqlDataSource1" to "SqlDataSource2," since SqlDataSource1 is being used by data source controls in Default.aspx.

Okay once the control has been added, go to Design View and you will see a button-like control there labeled "SqlDataSource - SqldataSource2." Right click on it, and click "Configure Data Source."

Since we had decided in the previous tutorial to place the connection parameters in the web.config file, the data connection is now named "ConnectionString." So under: Which data connection should your application use to connect to the database? Select "ConectionString." Then click next.

In the section "How would you like to retrieve data from your database?" check "Specify columns from table or view" and under name, select "renovationproject." Under "columns" you need to check "*" to signify that you are inserting data that covers entire database fields (workid, tasks, incharge and deadline).

Also, since you are inserting and editing data to the MS SQL database, you need to enable SQL query functionality. To do that, click "Advanced," and then check "Generate INSERT, UPDATE and DELETE statements," and click OK.

Once all of these items are set and configured in the "Configure the Select statement," click NEXT. The next thing you will see is Test Query. Try to click "Test Query" to see if MS SQL Server provides all the database records. If it is okay, hit Finish. You have now configured the SqlDataSource control in Insert.aspx.

Add/Configure DetailsView Controls to Insert.aspx

In the previous tutorial, we used the GridView data control to display database records back to the browser. Since the GridView control cannot be used to insert new records into the database using a web browser, you need a new ASP.NET control. This is called the DetailsView control.

This control works similarly to GridView, except that it can be used to insert records into the database. Also unlike the GridView control, which can display all database records at once to the web browser, DetailsView can only display one record at a time to the web browser, and the user can view the next record using pagination.

To add the DetailsView control to Insert.aspx, follow these step:

1. Add two breaks after </asp:SqlDataSource>

<br />

<br />

2. Go to View à Toolbox à Data; then click and drag DetailsView next to the <br /> statements.

After dragging the control, the DetailsView section markup should look like this (in source view):

     <br />

        <br />

        <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px">

        </asp:DetailsView>

    </div>

    </form>

The next thing you will need to do is configure DetailsView.

1. Click Design view in Visual Web Developer for Insert.aspx.

2. Right click on the DetailsView table (which is placed below SqlDataSource), and then click "Show Smart Tag."

3. Under "choose Data Source," select SqlDataSource2, which is the ID of the Sqldatasource used by Insert.aspx.

4. To enable pagination and insertion of new database records from the browser, check "Enable Paging" and "Enable Inserting" only.

5. To make the view of the DetailsView control consistent with the Default.aspx control, click "Auto format" and select "Classic."

Link the main form's Default.aspx and Insert.aspx

Since the user will see the main form first (Default.aspx) to update and delete records, he/she needs to see a hyperlink pointing to Insert.aspx to add new records. To add a link from Default.aspx to Insert.aspx and vice versa:

First, go to Default.aspx file in Design view and type the text "Insert new tasks here (below the GridView layout). Next, go to the Default.aspx source code view and put a hyperlink code on it pointing to Insert.aspx.

Screen shot:

Source code:

    <p class="style4">

        <a href="Insert.aspx">Insert new task here</a></p>

Add Finishing Touches to Insert.aspx

Finally, you can also add a link from Insert.aspx pointing back to Default.aspx. You can use the anchor text "Go to GridView table for task updating and deletion." Go through the same procedure described above for adding hyperlinks.

Source code:

<p class="style4">

        <a href="Insert.aspx">Insert new task here</a></p>

The font can also be changed (e.g to Verdana) to make it look consistent with Default.aspx. Likewise, you can also copy and paste the h3 and p tag layouts on Default.aspx.

Source code:

<h3>

            <span class="style1">Alexis Agency Home Renovation Project Code 115</span></h3>

        <p class="style1" style="height: 28px; width: 497px">

            <span class="style3">Note to Supervisors: </span><span class="style2">Insert new

            task below by clicking &quot;New&quot;.</span></p>

This will be placed on top of the DetailsView layout. Finally, this is how the complete Insert.aspx in Design View should look:

View and Test Project in the Browser

Finally, save all your files and then go to File à View in Browser. Go to Insert.aspx; it should look like this screen shot:

When you click "New" and enter a sample record as shown below, it will be inserted into the database after clicking "Insert."

To confirm that it was actually inserted, click "Go to GridView table for task updating and deletion," because it will pull out all of the database records, including the newly-inserted data.

Above, you can see that it was indeed inserted as workid12.

As a summary, these basic ASP.NET development skills can be used to create more complex websites like those used in E-commerce, content management systems and other web platforms involving the Microsoft SQL Server database.