Building as Address Book using ASP.NET - II

Posted On September 26, 2007 by Sneha Latha filed under Internet

In this article we will add we will add three links. That is 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 need to add new records to your database. From that Project Menu click on the add webform, name the webform called add_record.aspx. Add the following codeto add_record.aspx page.

<% @ 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>
   
   </body>
</HTML>

      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>

You can view the insert form.

In the above code you can see the control visible for all the fields except for the Primary Key field (ID). This field (ID) will be automatically generated when we save the record. In the above code we have included a “accept.aspx” include file. This file will share the form between the two files. This way we can avoid the 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 able to use in the code in the ID parameter. The Column parameter specifies what length the box should appear on the screen. Maxlength parameter specifies the maximum length of the data that can be enter by the user.

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 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 shows to the user as a single file. When the user enter all the fields in the form, and when he click on the save button, a connection will established to a database and store a new record in the adtable.

Deleting a Record from Database

Deleting a record is the easiest program compare to all the programs in any language.

Add the following code in the “delete_record.aspx” page. This program will delete the record from the database and returns to the address view page.

<% @ 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>

You can view the Delete record form the form.

Modifying a record

To modify an existing record, we will use the same form that used for the adding records, 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 make changes, when he clicks the save button it has to save the changes.

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

<% @ 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>

</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. It’s because some of the field can be Null, which will return an error message if you try to put a null value in the field. We used tostring() method to make sure all the field are enter before the user update the record.

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

Added on September 26, 2007 Comment

Building as Address Book using ASP.NET - II

Comments

Post a comment