Building an Address Book using ASP.NET

Remember the little scrapbook you used to carry, where you stored addresses and telephone numbers. It was quite handy during the good old days. Then came the computer and digital diary. Yet, you never left the habit of writing down things somewhere. Well, after all, old habits die hard.

With the advent of cell phones, you now have yet another place to store data. Instead of storing stuff all over the place, it makes sense to store everything on the net and access it from anywhere.

In this article, we will see how to build an address book using ASP.NET and MS SQL 2000 will be used as the back-end database. The project consists of a single data entity and four logical functions to manipulate the data. The following steps will help you build the application:

  • Create a database table that will store the data;
  • Create a Form to retrieve all entries from the table;
  • Create a Form to add a new record;
  • Create a Form to modify an existing record; and
  • Create a Form to delete a record.

Creating a database table

In this article, all the information pertaining to each person will be stored in a record in a single table. For some of the entries, you can even break the single table into multiple tables. For example, instead of having duplicate address fields in a single record, you may want to create an address table that is linked to the primary table with a primary key/foreign key relationship. However, in this article we keep things simple and use a single table called adtable.

Create the following table in SQL Server..

Table: adtable

Name

Data Type

Length

ID

Int

4

Firstname

varchar

25

Lastname

varchar

25

Title

varchar

25

Companyname

varchar

50

Address

varchar

200

Hphone

varchar

15

Wphone

varchar

15

Fax

varchar

15

Email

varchar

50

Webpage

varchar

50

Notes

text

 

Build a Viewer

To build this page, you can use either C# or Visual Basic .NET for the code. These two languages are supported in ASP.NET, which contains a number of controls that are designed to make life easier for developers. With these controls, you can use data binding, which is an automatic way to put data on the page. Let us now see how to retrieve records from the database.

In our example, DataGrid control displays tabular data and optionally supports selecting, sorting, paging and editing the data. By default, DataGrid generates a BoundColumn for each field in the data source (AutoGenerateColumns=true). Each field in the data is rendered in a separate column, in the order it occurs in the data. Field names appear in the grid's column headers and values are rendered in text labels.

Follow these steps to show records from the SQL Server database:

  • Create a connection to the database from the ASP.NET application using SqlConnection;
  • Retrieve data from the database using a SqlDataReader object; and
  • Retrieve data from the loop and display the results as HTML output in Label control.

The actual coding work starts now! Open Visual Studio.NET, click on Visual Basic Projects and select ASP.NET web application project. Refer figure 1.

addressbook1.tif

Add the following code in addresses.aspx page.

Code

<%@ Page Language="VB" Debug="true" %>
<% @ Import Namespace="System" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<title>All Contacts from Address Book</title>

</HEAD>
<body bgcolor="#ffffff">
<h2>All Contacts from the Address Book</h2>
<table cellpadding="4" cellspacing="0" width="100%">
<tr>
<th>
Name</th>
<th>
Title</th>
<th>
Company Name</th>
<th>
Home Phone</th>
</tr>
<asp:label id="lblOutput" runat="server"></asp:label>
</table>
<script runat="SERVER">

Sub Page_Load(Src As Object, e As EventArgs)
Dim sqlConn As New SqlConnection
Dim sqlCmd  As New SqlCommand
Dim sdrData As SqlDataReader
Dim sb As New StringBuilder

   sqlConn.ConnectionString = _
"server=localhost;database=newaddbook;uid=sa;pwd=;"
sqlConn.Open()
sqlCmd.CommandText = "SELECT * FROM adtable " _
& "ORDER BY FirstName, LastName"
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sdrData = sqlCmd.ExecuteReader()
While sdrData.Read()
sb.Append("<tr>" + vbCrLf)
sb.AppendFormat("   <td>{0}, {1}</td>" + vbCrLf, _
sdrData("FirstName"), _
sdrData("LastName"))
sb.AppendFormat("   <td>{0}</td>" + vbCrLf, sdrData("Title"))
sb.AppendFormat("   <td>{0}</td>" + vbCrLf, _
sdrData("CompanyName"))
sb.AppendFormat("   <td>{0}</td>" + vbCrLf, sdrData("Hphone"))
sb.Append("</tr>" + vbCrLf)
End While
sdrData.Close()
sqlConn.Close()
lblOutput.Text = sb.ToString()
End Sub

       </script>
</body>
</HTML>

The first line is the page directive. Language parameter specifies that Visual Basic .Net be used as the language for the code page. Debug parameter specifies that detailed debugging information is to be provided whenever any errors occur. The next three lines specify various system libraries that need to be referenced in order to get different objects that we need for our application.

In the above code, Page_load event occurs when the user requests this particular page from the server. You can also specify more than one language by adding Language parameter to the script tag. In our article, we use the default language VB.

System.Data.SqlClient namespace is the .NET Framework Data Provider for SQL Server. It describes a collection of classes used to access an SQL Server database in the managed space. Following is a list of event handlers used in code 1:

  • SqlConnection: This object represents a unique session to a SQL Server data source. In the case of a client/server database system, it is equivalent to a network connection to the server;
  • SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against an SQL Server database. Also, you can use OleDbCommand with other databases; and
    SqlDataReader: Provides access to data returned from the SqlCommand query. DataReader class is the fastest and most efficient way to get data from a database in read-only mode. For other databases, OleDbDataReader class is available.

The StringBuilder class represents a mutable string of characters. It's called mutable because it can be modified once it has been created, by using Append, Insert, Remove, and Replace methods. The StringBuilder class is defined in the System.Text namespace.

By using connection string, you can connect to the database and use SQLCommand object to run the query. The result is available through the SqlDataReader, by calling the ExecuteReader method on the SqlCommand object. Then we can build the HTML output.

In this section, we will add three links. These are for adding a new record, deleting a record and modifying an existing record from the address book table.

Adding a New Record

There are a lot of instances where you may have to add new records to your database. From the Project Menu, click on the add webform option and name the new webform as add_record.aspx. Append code 1 to add_record.aspx page.

Code 1

<% @ Page   Language="VB" Debug="True" %>
<% @ Import Namespace="System" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<% @ Import Namespace="System.Data" %>


<HTML>
<HEAD>
<title>Adding Records</title>
</HEAD>
<body>

<p class="pheading">Add New Contact</p> 
<!-- #Include File="accept.aspx" -->
</body>
</HTML>

<script runat="SERVER">
sub Page_Load(objectsender as object, objectArgs as EventArgs)

   Dim sqlConn as new SqlConnection
dim sqlCmd as new SqlCommand
dim sb as new StringBuilder()

   if Page.IsPostBack then
sqlConn.ConnectionString = _
"server=localhost;database=newaddbook;uid=sa;pwd=;"
sqlConn.Open()
sb.Append("INSERT INTO adtable (Firstname, Lastname,")
sb.Append("Title, Companyname, Address, Hphone, ")
sb.Append("Wphone, Fax, Email, Webpage, Notes) VALUES (")

      sb.AppendFormat("'{0}', '{1}', '{2}',", _
Request.Form("txtFirstName"), _
Request.Form("txtlastName"), _
Request.Form("txtTitle"))

sb.AppendFormat("'{0}', '{1}', '{2}',", _
Request.Form("txtCompanyName"), _
Request.Form("txtAddress"), _
Request.Form("txtHomePhone"))

      sb.AppendFormat("'{0}', '{1}', '{2}',", _
Request.Form("txtWorkPhone"), _
Request.Form("txtFax"), _
Request.Form("txtEMail"))

sb.AppendFormat("'{0}', '{1}')", _
Request.Form("txtWebPage"), _
Request.Form("txtNotes"))

sqlCmd.CommandText = sb.ToString()
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
Response.Redirect("addresses.aspx")

end if
end sub

       </script>

In the above code, you can see the control visible for all the fields except for the Primary Key field (ID). This field will be automatically generated when we save a record. Also, in the above code we have included an “accept.aspx” include file. This file will share the form between the two files. This way, we can avoid repetition of the fields and code.

The “accept.aspx” include file contains the following information. The code repeats over and over again for each field that we want to accept. The ASP:textbox control represents a server control that holds the text when the user enters. Each text box is given a name that will be used in the code for the ID parameter. The Column parameter specifies what length the box should appear on the screen. Maxlength parameter specifies the maximum length of data that can be entered by the user. Check out code 2.


Code 2

accept.aspx page

   <form runat="server">
<input id="rid" type="hidden" name="rid" value =5 runat="server">
<table cellSpacing="5">
<tr class="tabletext">
<td align="right"><STRONG>First Name</STRONG>:</td>
<td><asp:textbox id="txtFirstName" runat="server" columns="30" maxlength="40"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>Last Name:</STRONG></td>
<td><asp:textbox id="txtLastName" runat="server" columns="30" maxlength="40"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>Title:</STRONG></td>
<td><asp:textbox id="txtTitle" runat="server" Width="216px" columns="40" maxlength="80"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>Company Name:</STRONG></td>
<td><asp:textbox id="txtCompanyName" runat="server" Width="216px" columns="40" maxlength="80"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td vAlign="middle" align="right"><STRONG>Address:</STRONG></td>
<td><asp:textbox id="txtAddress" runat="server" Width="280px" columns="40" maxlength="240" rows="5"
wrap="true" textmode="Multiline"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>Home Phone:</STRONG></td>
<td><asp:textbox id="txtHomePhone" runat="server" columns="25" maxlength="40"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>Work Phone:</STRONG></td>
<td><asp:textbox id="txtWorkPhone" runat="server" columns="25" maxlength="40"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>Fax Number:</STRONG></td>
<td><asp:textbox id="txtFaxNumber" runat="server" columns="25" maxlength="40"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>E-Mail:</STRONG></td>
<td><asp:textbox id="txtEMail" runat="server" Width="184px" columns="40" maxlength="120"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="right"><STRONG>Web Page:</STRONG></td>
<td><asp:textbox id="txtWebPage" runat="server" Width="184px" columns="40" maxlength="120"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td vAlign="middle" align="right"><STRONG>Notes:</STRONG></td>
<td><asp:textbox id="txtNotes" runat="server" Width="288px" columns="40" rows="5" wrap="true" textmode="Multiline"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td align="center" colSpan="2"><input id="Submit1" type="submit" value="Submit" name="btnSubmit" runat="server">
<input id="Reset1" type="reset" value="Clear" name="btnReset" runat="server">
</td>
</tr>
</table>
</form>

 

Once the page is merged from different files, it is shown to the user as a single file. After the user enters all the fields in the form and clicks on the save button, a connection will be established to the database and stored as a new record in the adtable.

Deleting a Record from Database

Deleting a record is the easiest program, compared to all the programs in any language.
Add code 3 in the “delete_record.aspx” page. This program will delete the record from a database and return to the address view page.

 

Code 3

<% @ Page Language="VB" Debug="True" %>
<% @ Import Namespace="System" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<script runat="SERVER">
Sub Page_Load(objSender As Object, objArgs As EventArgs)
Dim sqlConn As New SqlConnection
Dim sqlCmd  As New SqlCommand

if Page.IsPostBack then
sqlConn.ConnectionString = _
"server=localhost;database=newaddbook;uid=sa;pwd=;"
sqlConn.Open()
sqlCmd.CommandText = "DELETE FROM adtable " _
& "WHERE ID = " _
& Request.form("txtID")
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
Response.Redirect("addresses.aspx")
end if
End Sub
</script>
<HTML>
<HEAD>
<title>Deleting a Record</title>

</HEAD>
<body>
<form runat="server" ID="Form1">
<p class="pheading"><STRONG>Deleting a Record</STRONG></p>
<table>
<tr class="tabletext">
<td align="right"><STRONG>Enther the ID :</STRONG></td>
<td><asp:textbox id="txtID" runat="server" maxlength="40" columns="25" Width="88px"></asp:textbox></td>
</tr>
<tr class="tabletext">
<td colspan="2" align="center">
<input type="submit" name="btnSubmit" runat="server" value="Submit" ID="Submit1">
<input type="reset" name="btnReset" runat="server" value="Clear" ID="Reset1">
</td>
</tr>
</table>
</form>

</body>
</HTML>

Modifying a Record

To modify an existing record, we will use the same form that we used for adding records, however, with a couple of modifications. First you need to get the record ID that we are modifying. Once you pass the record ID as a parameter from the URL, then you need to populate the rest of the field with the current field values for the record. Once the user has made the necessary changes, the same will be saved after the save button is clicked.

Add code 4 to the “modify_record.aspx” page.

 

Code 4

<% @ Page   Language="VB" Debug="True" %>
<% @ Import Namespace="System" %>
<% @ Import Namespace="System.Data.SqlClient" %>
<% @ Import Namespace="System.Data" %>
<HTML>
<HEAD>
<title>Modifying Records</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<p class="pheading">Modifying a Record</p>
<!-- #Include File="accept.aspx" -->

</body>
</HTML>
<script runat="SERVER">
Sub Page_Load(objSender As Object, objArgs As EventArgs)
Dim sqlConn as new SqlConnection
dim sqlCmd as new SqlCommand
dim sb as new StringBuilder
Dim sdrData As SqlDataReader

sqlConn.ConnectionString = _
"server=localhost;database=newaddbook;uid=sa;pwd=;"
sqlConn.Open()
If Not Page.IsPostBack Then
sqlCmd.CommandText = "SELECT * FROM adtable " _
& "WHERE ID = " _
& Request.QueryString("rid")

sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sdrData = sqlCmd.ExecuteReader()
sdrData.Read()
recordID.Value = Request.querystring("recordID")
txtFirstName.Text = sdrData("FirstName").ToString()
txtLastName.Text = sdrData("LastName").ToString()
txtTitle.Text = sdrData("Title").ToString()
txtCompanyName.Text = sdrData("CompanyName").ToString()
txtAddress.Text = sdrData("Address").ToString()
txtHomePhone.Text = sdrData("Hphone").ToString()
txtWorkPhone.Text = sdrData("Wphone").ToString()
txtFaxNumber.Text = sdrData("Fax").ToString()
txtEMail.Text = sdrData("EMail").ToString()
txtWebPage.Text = sdrData("WebPage").ToString()
txtNotes.Text = sdrData("Notes").ToString()
sdrData.Close()
sqlConn.Close()
Else
sb.Append("UPDATE adtable SET ")
sb.AppendFormat("FirstName = '{0}',", _
txtLastName.Text)
sb.AppendFormat("LastName = '{0}',", _
txtFirstName.Text)
sb.AppendFormat("Title = '{0}',", _
txtTitle.Text)
sb.AppendFormat("CompanyName = '{0}',", _
txtCompanyName.Text)
sb.AppendFormat("Address = '{0}',", _
txtAddress.Text)
sb.AppendFormat("Hphone = '{0}',", _
txtHomePhone.Text)
sb.AppendFormat("Wphone = '{0}',", _
txtWorkPhone.Text)
sb.AppendFormat("Fax = '{0}',", _
txtFaxNumber.Text)
sb.AppendFormat("EMail = '{0}',", _
txtEMail.Text)
sb.AppendFormat("WebPage = '{0}',", _
txtWebPage.Text)
sb.AppendFormat("Notes = '{0}' ", _
txtNotes.Text)
sb.AppendFormat("WHERE ID = {0}", _
Request.QueryString("rid"))

sqlCmd.CommandText = sb.ToString()
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
Response.Redirect("addresses.aspx")
End If
End Sub

       </script>

In the above code, when the form is populated, we used tostring() method to retrieve data from each field. This is because some of the fields may be Null, which will return an error message if you try to put a null value in the field. We used tostring() method to ensure that all the fields are filled before the user updates the record.

We have now learned how to create an addressbook using Asp.net. You can add additional beautification in the form design according to your requirements.

 

 








}