ASP.NET Topics: Using a Database on Your Web Site

	ASP.NET Topics: Simple Data Entry

Introduction

One of the most regular operations you will perform on a web application is to allow visitors to create and submit records to you. On the server side, you can use a database that stores the records. On the browser side, you can create a web page that the visitors will use. Web applications are not exclusively used for the Internet anymore. You can create an application for a company but instead of making it a traditional Windows program, you can create a web-based application but only for internal (intranet) use. In this case also, the employees will use a web page to create and submit records that would be stored on the server.

In this introduction, we will see a simple (probably the simplest) way to create a record, using a simple SQL statement.

Practical Learning: Introducing Data Entry

Microsoft SQL Server (Start -> (All) Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio and click Connect to connect to the database)
On the main menu, click File -> New ->Query With Current Connection

To create a database and a table, type the following statement:

IF DB_ID (N'people1') IS NOT NULL
DROP DATABASE people1;
GO
CREATE DATABASE people1;
GO
Use People1;
GO
CREATE Table Persons(
PersonID INT IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(20),
LastName varchar(20) NOT NULL);
GO

To execute the statement, press F5
Start Microsoft Visual Studio or Microsoft Visual C#
To create a web site, on the main menu, click File -> New -> Web Site...
Change the name of the web site to persons1 and set the language to Visual C#
Click OK

In the Solution Explorer, right-click Default.aspx and click Rename

Change the name to index.aspx and press Enter

To create a new web page, on the main menu, click Website -> Add New Item...
In the Templates, make sure Web Form is selected. Change the name to persons.
Make sure the language is set to Visual C# and click Add
In the bottom section of the code editor, click Design
From the Data section of the Toolbox, drag SqlDataSource and drop it on the form
Click Configure Data Source
In the Configure Data Source wizard, click New Connection
Select the name of the server in the Server Name combo box
In the Select Or Enter A Database Name combo box, select people1
Click Test Connection
Click OK and OK

In the first page of the Configure Data Source wizard, click Next

In the second page of the wizard, change the name of the connection string to cstPeople and click Next

Make sure the Specify Columns From A Table Or View radio button is selected.
Make sure Persons is selected in the Name combo box box.
In the Columns list box, click the * check box

Configure Data Source

Click Next and click Finish
In the form, click below the data source and type List of People
From the Data section of the Toolbox, drag GridView and drop it on the form below the List of People title
While the data grid is still selected on the form, in the Properties window, set its DataSourceID to SqlDataSource1

Click Source and change the file as follows:

<%@ Page Language="C#" 
         AutoEventWireup="true" 
         CodeFile="persons.aspx.cs" 
         Inherits="persons" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>People</title>
</head>
<body>
    <form id="frmPersons" runat="server">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" 
                           runat="server" 
                    ConnectionString="<%$ ConnectionStrings:cstPeople %>"
                           SelectCommand="SELECT * FROM [Persons]">
                           </asp:SqlDataSource>
        </div>
        <h1>List of People</h1>
        <asp:GridView ID="GridView1"
                      runat="server" 
                      AutoGenerateColumns="False" 
                      DataKeyNames="PersonID"
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="PersonID" 
                                HeaderText="PersonID" 
                                InsertVisible="False"
                                ReadOnly="True" 
                                SortExpression="PersonID" />
                <asp:BoundField DataField="FirstName" 
                                HeaderText="FirstName" 
                                SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" 
                                HeaderText="LastName" 
                                SortExpression="LastName" />
            </Columns>
        </asp:GridView>
    </form>
    <p>
        <a href="index.aspx">Home</a>
    </p>
</body>
</html>

Save the file

Preparing the Application

Before performing data entry, you must get a connection to the database server, which you can do by creating or using a SqlConnection object.

Probably the simplest way to perform data entry in ADO.NET is by using the INSERT INTO TABLE statement. After creating the statement, you can pass it to a command. Once the command is ready, you can execute it.

Practical Learning: Performing Data Entry

To create a new web page, on the main menu, click Website -> Add New Item...

In the Templates, make sure Web Form is selected. Change the name to newperson.
Make sure the language is set to Visual C# and click Add

In the bottom section of the code editor, click Design

Design the form as follows:

Control	Text	(ID)
Label	First Name:
TextBox		txtFirstName
Label	Last Name:
TextBox		txtLastName
Button	Create	btnNewPerson

In the bottom section, click Source and change the file as follows (you don't have to do any of these things, it would not affect your exercise, it will only make the form look good):

<%@ Page Language="C#"
         AutoEventWireup="true" 
         CodeFile="newperson.aspx.cs" 
         Inherits="newperson" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>People - New Person</title>
</head>
<body>
    <form id="frmNewPerson" runat="server" method="get">
    <div>
        <h1>People - New Person</h1>
        <table>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label1" 
                               runat="server" 
                               Text="First Name:" 
                               Width="94px">
                    </asp:Label></td>
                <td style="width: 84px">
                    <asp:TextBox ID="txtFirstName" 
                                 runat="server">
                    </asp:TextBox></td>
            </tr>
            <tr>
                <td style="width: 100px">
                    <asp:Label ID="Label2" 
                               runat="server" 
                               Text="Last Name:">
                    </asp:Label></td>
                <td style="width: 84px">
                    <asp:TextBox ID="txtLastName" 
                                 runat="server">
                    </asp:TextBox></td>
            </tr>
            <tr>
                <td style="width: 100px">
                </td>
                <td style="width: 84px">
                    <asp:Button ID="btnCreate"
                                runat="server" 
                                Text="Create Person" /></td>
            </tr>
        </table>
    
    </div>
    </form>
    <p>
        <a href="index.aspx">Home</a>
    </p>
</body>
</html>

In the bottom section of the code editor, click Design

On the form, double-click Create and implement its event as follows:

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class newperson : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnNewPerson_Click(object sender,
                                      EventArgs e)
    {
        OleDbConnection cnnPerson = 
            new OleDbConnection(
                  "Provider=Microsoft.Jet.OLEDB.4.0;data source=" +
                        Server.MapPath("~/CGI-BIN/people.mdb"));

        try
        {
            cnnPerson.Open();

            string strStatement = "INSERT INTO Persons(FirstName, " +
                                                 "LastName) VALUES('" +
                                                  txtFirstName.Text +
                                                 "', '" +
                                                  txtLastName.Text + "');";
            OleDbCommand cmdPerson = 
			new OleDbCommand(strStatement, cnnPerson);

            cmdPerson.ExecuteNonQuery();

            txtFirstName.Text = "";
            txtLastName.Text = "";
            txtFirstName.Focus();
        }
        finally
        {
            cnnPerson.Close();
        }
    }
}

Click the index.aspx tab to access its file

In the bottom section of the code editor, click Source is necessary and change the file as follows:

<%@ Page Language="C#" 
         AutoEventWireup="true"  
         CodeFile="index.aspx.cs" 
         Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>People</title>
</head>
<body>
<h1>People</h1>
<p><a href="persons.aspx">List of People</a></p>
<p><a href="newperson.aspx">New Person</a></p>

    <form id="form1" runat="server">
    <div>
    
    </div>
    </form>
</body>
</html>

To execute the application, on the main menu, click Debug -> Start Without Debugging

Click New Person and create a new people before returning to the home page, then click the List of People link

Close the browser and the application


Home	Copyright © 2007-2009 FunctionX, Inc.