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:
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:
- Index.aspx (this page will help us to insert the record in database)
- Index.aspx.vb
- Result.aspx (this page will help us to find the particular record)
- Result.aspx.cs
- web.config (this will hold the configuration of database that is credentials of database)
- App_Data (this is folder for 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 |
Here is screenshot of my database:
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"%>
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">
<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
</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) HandlesButton1.Click
SqlDataSource1.Insert()
End SubEnd Class
Result.aspx page
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Result.aspx.cs" Inherits="Result" %>
SqlDataSource1.Insert()
End SubEnd 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>
<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;
</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 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();
}
}
}
HAVE A HAPPY CODING!
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();
}
}
}
HAVE A HAPPY CODING!
No comments:
Post a Comment