Reader Level:
ARTICLE

Searching Any Record from Database in ASP.Net

Posted by Abhimanyu K Vatsa Articles | ASP.NET Programming June 14, 2010
This article will explain you how to search particular record from SQL Server Database in ASP.Net
  • 0
  • 0
  • 36122

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:

  • 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)
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

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

<!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();
        }
    }
}


HAVE A HAPPY CODING!
 

COMMENT USING

Trending up