Searching Any Record from Database in ASP.NET
Introduction
Once upon a time my
examination result was published online first, on that time I was totally
unknown about the working process of internet and even computing. The main
reason, I was very remote villager. But today I have created lots of web
applications where we search record or say result from huge database. Now I
would like to share this experience to all my friends. Let’s take a look on the
process.
Perquisite
To learn this article you
should have the basic knowledge of ASP.Net and SQL Server Database. Because in
this article, will not explain the technique to create SQL Database. Only we
cover the entity and attribute details and you have to create it yourself.
Requirements
Create a web application
which has the following things:
(i) Index.aspx (this page will help us to insert the record in database)
(ii) Index.aspx.vb
(iii) Result.aspx (this page will help us to find the particular record)
(iv) Result.aspx.cs
(v) web.config (this will hold the configuration of database that is credentials of database)
(vi) App_Data (this is folder for Database)
(i) Index.aspx (this page will help us to insert the record in database)
(ii) Index.aspx.vb
(iii) Result.aspx (this page will help us to find the particular record)
(iv) Result.aspx.cs
(v) web.config (this will hold the configuration of database that is credentials of database)
(vi) App_Data (this is folder for Database)
Creating
Database
Create a Database as per details given below:
Database Name: myDb.mdf
Table Name: myTb
Column Name Data Type Allow Nulls
**************************************************
roll int Not Checked
name varchar(50) Not Checked
address varchar(100) Not Checked
result char(10) Not Checked
Connection-string
in web.config file
There are couples of ways to
configure the database but I have used as follows:
<connectionStrings>
<add
name="myDbConnectionString1" connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDb.mdf;Integrated
Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
If you want to create your own configuration in web.config file, then simply drag the table from Database Explorer on form and now open web.config file. Now you don’t have to type above coding for your configuration. You can delete the dragged items from web form, web.config file will not be effected now.
Index.aspx
page
<%@
Page Language="VB" AutoEventWireup="false"
CodeFile="Index.aspx.vb" 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>Searching Any Record From
Database In ASP.Net</title>
</head>
<body>
<form id="form1"
runat="server">
<div>
<span style="font-size: 14pt;
color: #0000ff; font-family: Verdana; text-decoration: underline">
<strong>Searching Any Record
From Database In ASP.Net</strong></span><br />
<br />
<br />
<table style="width:
345px" bgcolor="#dcdcdc">
<tr
bgcolor="#008b8b">
<td
colspan="3">
<br />
<div style="width:
286px; height: 2px">
<strong>Insert
the students result here</strong></div>
</td>
</tr>
<tr>
<td style="width:
26px">
</td>
<td style="width:
86px">
<br />
</td>
<td style="width:
134px">
</td>
</tr>
<tr>
<td style="width:
26px">
Roll</td>
<td style="width:
86px">
<asp:TextBox
ID="roll" runat="server"></asp:TextBox></td>
<td style="width:
134px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator1"
runat="server" ControlToValidate="roll"
ErrorMessage="RequiredFieldValidator">*</asp:RequiredFieldValidator></td>
</tr>
<tr>
<td style="width:
26px">
Name</td>
<td style="width:
86px">
<asp:TextBox
ID="name" runat="server"></asp:TextBox></td>
<td style="width:
134px">
<asp:RequiredFieldValidator
ID="RequiredFieldValidator2" runat="server"
ControlToValidate="name"
ErrorMessage="RequiredFieldValidator">*</asp:RequiredFieldValidator></td>
</tr>
<tr>
<td style="width:
26px">
Address</td>
<td style="width:
86px">
<asp:TextBox
ID="address"
runat="server"></asp:TextBox></td>
<td style="width:
134px">
<asp:RequiredFieldValidator ID="RequiredFieldValidator3"
runat="server" ControlToValidate="address"
ErrorMessage="RequiredFieldValidator">*</asp:RequiredFieldValidator></td>
</tr>
<tr>
<td style="width:
26px">
Result</td>
<td style="width:
86px">
<asp:DropDownList
ID="result" runat="server" Width="154px">
<asp:ListItem>PASS</asp:ListItem>
<asp:ListItem>FAIL</asp:ListItem>
</asp:DropDownList></td>
<td style="width:
134px">
</td>
</tr>
<tr>
<td style="width:
26px">
</td>
<td style="width: 86px;
text-align: left;">
<br />
<asp:Button
ID="Button1" runat="server" Text="Insert"
Width="91px" /><br />
<asp:SqlDataSource
ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:myDbConnectionString1 %>"
InsertCommand="INSERT
INTO myTb(roll, name, address, result) VALUES (@roll, @name, @address,
@result)"
SelectCommand="SELECT
roll, name, address, result FROM myTb">
<InsertParameters>
<asp:FormParameter
Name="roll" FormField="roll"/>
<asp:FormParameter
Name="name" FormField="name" />
<asp:FormParameter
Name="address" FormField="address" />
<asp:FormParameter
Name="result" FormField="result" />
</InsertParameters>
</asp:SqlDataSource>
</td>
<td style="width:
134px">
</td>
</tr>
<tr
bgcolor="#008b8b">
<td
colspan="3">
<br />
<asp:HyperLink
ID="HyperLink1" runat="server" Font-Bold="True"
ForeColor="Red" NavigateUrl="~/Result.aspx">Result
Here</asp:HyperLink></td>
</tr>
</table>
<br />
<br />
</div>
</form>
</body>
</html>
Index.aspx.vb
page
Partial
Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As
Object, ByVal e As System.EventArgs) Handles Button1.Click
SqlDataSource1.Insert()
End Sub
End Class
Result.aspx
page
<%@
Page Language="C#" AutoEventWireup="true"
CodeFile="Result.aspx.cs" Inherits="Result" %>
<!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>Searching Any Record From
Database In ASP.Net</title>
</head>
<body>
<form id="form1"
runat="server">
<div>
<div style="width: 375px;
height: 66px">
<asp:TextBox
ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button
ID="Button1" runat="server"
OnClick="Button1_Click" Text="Search" /><br />
<br />
<asp:Label ID="Label1"
runat="server"
Width="318px"></asp:Label></div>
</div>
<br />
<asp:GridView
ID="GridView1" runat="server" Width="718px">
</asp:GridView>
</form>
</body>
</html>
Result.aspx.cs
page
using
System;
using
System.Collections;
using
System.Configuration;
using
System.Data;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.HtmlControls;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
public
partial class Result : System.Web.UI.Page
{
protected void Page_Load(object sender,
EventArgs e)
{
}
protected void Button1_Click(object sender,
EventArgs e)
{
//data soure control that works with
sql database
SqlDataSource sds = new
SqlDataSource();
//get connection string from
application's web.config file
sds.ConnectionString =
ConfigurationManager.ConnectionStrings["myDbConnectionString1"].ToString();
//create parameters with specified name
and values
sds.SelectParameters.Add("roll", TypeCode.String,
this.TextBox1.Text);
//set the sql string to retrive data
from the database
sds.SelectCommand = "SELECT * FROM
[myTb] WHERE [roll]=@roll";
//retrive data
DataView dv =
(DataView)sds.Select(DataSourceSelectArguments.Empty);
if (dv.Count == 0)
{
this.Label1.Text = "No Data
Found";
return;
}
else
{
GridView1.DataSource = sds;
GridView1.DataBind();
}
}
}
Comments
Post a Comment