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 data. Now I would like to share this experience to all my friends.

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)

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 id="Head1" 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 id="Head1" 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

Popular posts from this blog

Customize User's Profile in ASP.NET Identity System

Image
Note: You should read this post instead, I found below walkthrough will not work on ASP.NET Identity 2. I will re-work on this post soon. In this post you will learn how to customize User’s Profile and add some more fields like FirstName, LastName, EmailID etc with ASP.NET Identity System. In my last post I listed some of the great new features introduced with Identity system .
Read More »

Lambda two tables and three tables inner join code samples

In this blog post you will learn inner join using lambda queries. There will be two samples, in first sample you will see how you can join two tables and in second sample you will see how you can extend even further to join three tables and so on. I will sample in-memory data, but the same implementation will work with IQueryable (lazy query) too. Here's the code snippet:- namespace ConsoleApp1 {     class Program     {         static void Main()         {             var table1 = new List<Table1>();             table1.Add( new Table1 { Id = 1, Name = "Name 1" , Address = "Address 1" });             table1.Add( new Table1 { Id = 2, Name = "Name 2" , Address = "Address 2" });  ...
Read More »