Introduction
Introducing a daily use application developed in ASP.NET to manage the contact
numbers, birthday, email etc. This article will guide using step by step
approach to create such applications. I am using SQL Server and Access Databases
separately; any one may be used by the user of this application.
Why I Developed This?
Actually, my mobile phone a huge list of contact and it is very difficult to
manage on handset always, reason may be anything like data-loose or difficult to
find on mobile keypad. I exported my entire contact in CSV format and then I
imported that in Microsoft Access database and then in SQL Server.
Next, I developed a web application to manage that database having searching,
shorting, inserting, selecting, editing deleting like features. I would like to
share the processes here. Find the screen of running application.
![image002.jpg]()
Database Structure
Find the database structure that I have created for my application.
![image004.jpg]()
AccessDB.aspx Page (Front) Code
I am using show-hide div option to insert new record in database by using some
javascript codes.
<%@
Page Language="C#"
AutoEventWireup="true"
CodeFile="AccessDB.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>Using
Access Database</title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<script
type="text/javascript">
function toggle(o) {
var e =
document.getElementById(o);
e.style.display = e.style.display ==
'block' ?
'none' : 'block';
}
onload = function () {
var e, i = 0;
while (e =
document.getElementById(['list1'][i++])) {
e.style.display = 'none';
}
}
</script>
<div
class="tabledata1">
<a
href="#"
onclick="toggle('list1');">Insert
New Record</a>
</div>
<div
id="list1"
style="font-family:Verdana;
font-size:11px;
background-color:#cccccc;
padding:10px;">
<table>
<tr>
<td>
<hr
/>
<table>
<tr>
<td>
Name
</td>
<td>
Mobile
</td>
<td>
Home Number
</td>
<td>
Company Number
</td>
<td>
Email
</td>
<td>
Office
</td>
<td>
Fax
</td>
<td>
Birthday
</td>
</tr>
<tr>
<td>
<asp:TextBox
ID="name"
runat="server"
Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox
ID="mobile"
runat="server"
Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox
ID="homenumber"
runat="server"
Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox
ID="companynumber"
runat="server"
Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox
ID="email"
runat="server"
Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox
ID="office"
runat="server"
Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox
ID="fax"
runat="server"
Width="105px"></asp:TextBox>
</td>
<td>
<asp:TextBox
ID="birthday"
runat="server"
Width="105px"></asp:TextBox>
</td>
</tr>
</table>
<div
style="text-align:
right">
<asp:Button
ID="btnSubmit"
runat="server"
Text="Insert"
onclick="btnSubmit_Click"
/>
</div>
<hr
/>
</td>
</tr>
</table>
</div>
</div>
<br
/>
<div
style="font-family:Verdana;
font-size:11px;
background-color:#c0c0c0;
padding:10px;">
Search by
<asp:DropDownList
ID="DropDownList1"
runat="server">
<asp:ListItem>Person_Name</asp:ListItem>
<asp:ListItem>Mobile</asp:ListItem>
<asp:ListItem>Home</asp:ListItem>
<asp:ListItem>Company</asp:ListItem>
<asp:ListItem>Email</asp:ListItem>
<asp:ListItem>Office</asp:ListItem>
<asp:ListItem>Fax</asp:ListItem>
<asp:ListItem>Birthday</asp:ListItem>
</asp:DropDownList>
where like
<asp:TextBox
ID="TextBox1"
runat="server"></asp:TextBox>
<asp:Button
ID="Button1"
runat="server"
Text="Filter"
onclick="Button1_Click"
/>
<asp:Button
ID="Button2"
runat="server"
Text="Clear Filter"
OnClick="Button2_Click"
/>
<br
/>
<br
/>
<asp:GridView
ID="GridView1"
runat="server"
AutoGenerateColumns="False"
BackColor="White"
BorderColor="White"
BorderStyle="Ridge"
BorderWidth="2px"
CellPadding="3"
CellSpacing="1"
DataKeyNames="ID"
DataSourceID="AccessDataSource1"
EmptyDataText="There
are no data records to display." GridLines="None"
Width="871px"
AllowSorting="True">
<Columns>
<asp:CommandField
ShowDeleteButton="True"
ShowEditButton="True"
ShowSelectButton="True"
/>
<asp:BoundField
DataField="Person_Name"
HeaderText="Person_Name"
SortExpression="Person_Name"
/>
<asp:BoundField
DataField="Mobile"
HeaderText="Mobile"
SortExpression="Mobile"
/>
<asp:BoundField
DataField="Home"
HeaderText="Home"
SortExpression="Home"
/>
<asp:BoundField
DataField="Company"
HeaderText="Company"
SortExpression="Company"
/>
<asp:BoundField
DataField="Email"
HeaderText="Email"
SortExpression="Email"
/>
<asp:BoundField
DataField="Office"
HeaderText="Office"
SortExpression="Office"
/>
<asp:BoundField
DataField="Fax"
HeaderText="Fax"
SortExpression="Fax"
/>
<asp:BoundField
DataField="Birthday"
HeaderText="Birthday"
SortExpression="Birthday"
/>
</Columns>
<FooterStyle
BackColor="#C6C3C6"
ForeColor="Black"
/>
<HeaderStyle
BackColor="#4A3C8C"
Font-Bold="True"
ForeColor="#E7E7FF"
/>
<PagerStyle
BackColor="#C6C3C6"
ForeColor="Black"
HorizontalAlign="Right"
/>
<RowStyle
BackColor="#DEDFDE"
ForeColor="Black"
/>
<SelectedRowStyle
BackColor="#9471DE"
Font-Bold="True"
ForeColor="White"
/>
<SortedAscendingCellStyle
BackColor="#F1F1F1"
/>
<SortedAscendingHeaderStyle
BackColor="#594B9C"
/>
<SortedDescendingCellStyle
BackColor="#CAC9C9"
/>
<SortedDescendingHeaderStyle
BackColor="#33276A"
/>
</asp:GridView>
<asp:AccessDataSource
ID="AccessDataSource1"
runat="server"
DataFile="App_Data\ContactsDB.mdb"
DeleteCommand="DELETE
FROM `ContactsTB` WHERE `ID` = ?"
InsertCommand="INSERT
INTO `ContactsTB` (`ID`, `Person_Name`, `Mobile`, `Home`, `Company`, `Email`,
`Office`, `Fax`, `Birthday`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
SelectCommand="SELECT
`ID`, `Person_Name`, `Mobile`, `Home`, `Company`, `Email`, `Office`, `Fax`,
`Birthday` FROM `ContactsTB`"
UpdateCommand="UPDATE
`ContactsTB` SET `Person_Name` = ?, `Mobile` = ?, `Home` = ?, `Company` = ?,
`Email` = ?, `Office` = ?, `Fax` = ?, `Birthday` = ? WHERE `ID` = ?">
<DeleteParameters>
<asp:Parameter
Name="ID"
Type="Int32"
/>
</DeleteParameters>
<InsertParameters>
<asp:Parameter
Name="ID"
Type="Int32"
/>
<asp:Parameter
Name="Person_Name"
Type="String"
/>
<asp:Parameter
Name="Mobile"
Type="String"
/>
<asp:Parameter
Name="Home"
Type="String"
/>
<asp:Parameter
Name="Company"
Type="String"
/>
<asp:Parameter
Name="Email"
Type="String"
/>
<asp:Parameter
Name="Office"
Type="String"
/>
<asp:Parameter
Name="Fax"
Type="String"
/>
<asp:Parameter
Name="Birthday"
Type="String"
/>
</InsertParameters>
<UpdateParameters>
<asp:Parameter
Name="Person_Name"
Type="String"
/>
<asp:Parameter
Name="Mobile"
Type="String"
/>
<asp:Parameter
Name="Home"
Type="String"
/>
<asp:Parameter
Name="Company"
Type="String"
/>
<asp:Parameter
Name="Email"
Type="String"
/>
<asp:Parameter
Name="Office"
Type="String"
/>
<asp:Parameter
Name="Fax"
Type="String"
/>
<asp:Parameter
Name="Birthday"
Type="String"
/>
<asp:Parameter
Name="ID"
Type="Int32"
/>
</UpdateParameters>
</asp:AccessDataSource>
</div>
</form>
</body>
</html>
AccessDB.aspx Page Code-Behind
In the code given below I am using quicker code to filter contact and to insert
new record I am calling a execution() method that has sql query and rest
mechanism.
public
partial class
_Default : System.Web.UI.Page
{
protected void
Button1_Click(object sender,
EventArgs e)
{
string FilterExpression =
string.Concat(DropDownList1.SelectedValue,
" LIKE '%{0}%'");
AccessDataSource1.FilterParameters.Clear();
AccessDataSource1.FilterParameters.Add(new
ControlParameter(DropDownList1.SelectedValue,
"TextBox1",
"Text"));
AccessDataSource1.FilterExpression = FilterExpression;
}
protected void
Button2_Click(object sender,
EventArgs e)
{
AccessDataSource1.SelectParameters.Clear();
}
protected void
btnSubmit_Click(object sender,
EventArgs e)
{
execution(Convert.ToString(name.Text),
Convert.ToString(mobile.Text),
Convert.ToString(homenumber.Text),
Convert.ToString(companynumber.Text),
Convert.ToString(email.Text),
Convert.ToString(office.Text),
Convert.ToString(fax.Text),
Convert.ToString(birthday.Text));
Response.Redirect("~/AccessDB.aspx");
}
private void
execution(string person_name,
string mobile,
string home, string company,
string email,
string office, string fax,
string birthday)
{
try
{
string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + Server.MapPath("App_Data\\ContactsDB.mdb")
+ ";";
OleDbConnection conn
= new
OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd =
new
OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT
INTO ContactsTB (Person_Name, Mobile, Home, Company, Email, Office, Fax,
Birthday) VALUES (@person_name, @mobile, @home, @company, @email, @office, @fax,
@birthday)";
cmd.Parameters.Add("@person_name",
OleDbType.VarChar).Value =
person_name;
cmd.Parameters.Add("@mobile",
OleDbType.VarChar).Value =
mobile;
cmd.Parameters.Add("@home",
OleDbType.VarChar).Value = home;
cmd.Parameters.Add("@company",
OleDbType.VarChar).Value =
company;
cmd.Parameters.Add("@email",
OleDbType.VarChar).Value = email;
cmd.Parameters.Add("@office",
OleDbType.VarChar).Value =
office;
cmd.Parameters.Add("@fax",
OleDbType.VarChar).Value = fax;
cmd.Parameters.Add("@birthday",
OleDbType.VarChar).Value =
birthday;
cmd.ExecuteNonQuery();
conn.Close();
}
catch (System.Data.SqlClient.SqlException
ex_msg)
{
string msg =
"Error occured while inserting the
comment.";
msg += ex_msg.Message;
throw
new Exception(msg);
}
}
}
The same process used for SQL Server Database, you can download the attached
project and take a look at procedures.
I hope you like it. Please post your comments.