Asp.net grid view search using sql server and update results

1. Create Database in Sql Server for Asp.net gridview search
Database Name: GridDB
TableName: tblStudent

From above table structure, status represents 0 and 1. 0 indicates fail and 1 indicates success. It means registration is failed or success,Add few dummy records into the table with status “0”.

2.Create .Net Empty Web Project
Project Name: GridViewSearchDemo.

Project contains three types of files
a) Class file(Processor.cs)
b) Web.config
c) Web form i.e., frmGridSearch.aspx

In this project, we will do three operations.
• Search the records based on Name, Mobile No, Date and Status
• Update the mobile number of successfully registered students from grid
• Updating the registration status by using resend button.

Before going to design the page, we will implement the business logic for the above operations.
a) Processor.cs : Used for business logic for three operations.
Below is the code for business logic.

// Processor.cs file represents business logic for all the operations
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace GridViewSearchDemo.Helpers
{
    /// <summary>
    /// This class represents the business logic
    /// </summary>
    public class Processor
    {
        string connectionString = ConfigurationManager.ConnectionStrings["GridDBConnectionString"].ConnectionString;
        SqlConnection conn;

        /// <summary>
        /// Retrieves the data depends on the input provided
        /// </summary>
        /// <param name="name">Name</param> 
        /// <param name="mobileNo">Mobile No</param>
        /// <param name="date">Date of registration</param>
        /// <param name="status">Register Status of Student, 0 indicates fail & 1 indicates success</param>
        /// <returns>returns data table</returns>
        public DataTable Search(string name, string mobileNo, string date, int status)
        {
            conn = new SqlConnection(connectionString);
            string sqlCommand = string.Empty;
            if (name != string.Empty && mobileNo != string.Empty && date != string.Empty)
            {
                sqlCommand = "SELECT * FROM tblStudent WHERE Name like '%" + name + "%' and mobileNo like '%" + mobileNo + "%' and DateOfRegistration='" + Convert.ToDateTime(date).ToString("yyyy-MM-dd") + "' and status=" + status;
            }
            else if (name != string.Empty && mobileNo != string.Empty)
            {
                sqlCommand = "SELECT * FROM tblStudent WHERE Name like '%" + name + "%' and mobileNo like '%" + mobileNo + "%' and status=" + status;
            }
            else if (name != string.Empty && date != string.Empty)
            {
                sqlCommand = "SELECT * FROM tblStudent WHERE Name like '%" + name + "%' and DateOfRegistration='" + Convert.ToDateTime(date).ToString("yyyy-MM-dd") + "' and status=" + status;
            }
            else if (mobileNo != string.Empty && date != string.Empty)
            {
                sqlCommand = "SELECT * FROM tblStudent WHERE mobileNo like '%" + mobileNo + "%' and DateOfRegistration='" + Convert.ToDateTime(date).ToString("yyyy-MM-dd") + "' and status=" + status;
            }
            else if (name != string.Empty)
            {
                sqlCommand = "SELECT * FROM tblStudent WHERE Name like '%" + name + "%' and status=" + status ;
            }
            else if (mobileNo != string.Empty)
            {
                sqlCommand = "SELECT * FROM tblStudent WHERE mobileNo like '%" + mobileNo + "%' and status=" + status;
            }
            else if (date != string.Empty)
            {
                sqlCommand = "SELECT * FROM tblStudent WHERE DateOfRegistration='" + Convert.ToDateTime(date).ToString("yyyy-MM-dd") + "' and status=" + status;
            }
            SqlDataAdapter adp = new SqlDataAdapter(sqlCommand, conn);
            DataSet ds = new DataSet();
            DataTable dt;
            conn.Open();
            adp.Fill(ds);
            dt = ds.Tables[0];
            conn.Close();
            return dt;
        }

        /// <summary>
        /// Updates Mobile Number
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="mobileNo">mobile number</param>
        public void UpdateMobileNumber(string id, string mobileNo)
        {
            conn = new SqlConnection(connectionString);
            string query="UPDATE tblStudent SET MobileNo='"+mobileNo+ "' WHERE ID="+id;
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// Registers failed records data and updates the status as success i.e., 1
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="status">status</param>
        public void Resend(string id, int status)
        {
            conn = new SqlConnection(connectionString);
            string query = "UPDATE tblStudent SET Status=" + status+ " WHERE ID=" + id;
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        } 
    }
}

b)Web.config: create config file to create connection string to communicate with database,check below code once to get better idea.

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
  <connectionStrings>
    <add name="GridDBConnectionString" connectionString="Data Source=.;Initial Catalog=GridDB;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

c)Web form i.e., frmGridSearch.aspx
Web page frmGridSearch.aspx has design(.aspx) page and code behind file(.aspx.cs)
Below code for design the web page i.e., frmGridSearch.aspx

 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="frmGridSearch.aspx.cs" Inherits="GridViewSearchDemo.frmGridSearch" %>
<!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></title>
<style type="text/css">
.tableStyle
{
width: 100%;
}
.tdStyle
{
width: 130px;
background-color: #FFFFCC;
}
.HeaderStyle
{
width: 100%;
background-color: #990000;
color:White;
font-weight:bold;
}
.btnStyleBlue
{
background-color:#0099FF;
color:White;
border: 1px solid #ffffff;
font-weight:bold;
}
.btnStyleGreen
{
background-color:Green;
color:White;
border: 1px solid #ffffff;
font-weight:bold;
}
.btnStyleRed
{
background-color:Red;
color:White;
border: 1px solid #ffffff;
font-weight:bold;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="tableStyle">
<%-- SEARCH FORM DESIGN--%>
<tr>
<td colspan="3" style="font-size:medium;color:Blue">
Search the data with any input. One input is required.   
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="Label2" runat="server" Text="Mobile No."></asp:Label>
</td>
<td>
<asp:TextBox ID="txtMobileNo" runat="server"></asp:TextBox>
</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="Label3" runat="server" Text="Date"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtDate" runat="server"></asp:TextBox>
<asp:Label ID="Label5" runat="server" Text="(DD/MM/YYYY)"></asp:Label>
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" runat="server" Text="Status"></asp:Label>
</td>
<td>
<asp:RadioButton ID="rbtnSuccess" runat="server" GroupName="RegisterStatus" 
Text="Success" />
<asp:RadioButton ID="rbtnFail" runat="server" GroupName="RegisterStatus" 
Text="Fail" AutoPostBack="True" Checked="True" />
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td>
<asp:Button ID="btnSearch" runat="server" Text="Search" 
onclick="btnSearch_Click" BackColor="#0099FF" BorderColor="White" 
BorderStyle="Solid" Font-Bold="True" ForeColor="White" Width="120px"/>
</td>
<td>
<asp:Button ID="btnReset" runat="server" onclick="btnReset_Click" 
Text="Reset" BackColor="#0099FF" BorderColor="White" 
BorderStyle="Solid" Font-Bold="True" ForeColor="White" Width="120px"/>
</td>
<td>
&nbsp;</td>
</tr>
<tr>
<td colspan="3">
<asp:Label ID="lblResult" runat="server" ForeColor="Red"></asp:Label>
</td>
</tr>
<%-- EDIT DATA LIST GRID  --%>
<tr>
<td colspan="3">
<asp:DataList ID="dlEditData" runat="server" GridLines="Both" RepeatColumns="1" 
BorderColor="Black" RepeatDirection="Horizontal" CellPadding="4" 
ForeColor="#333333" 
onitemcommand="dlEditData_ItemCommand">
<HeaderTemplate>
<table style="border:1">
<tr class="HeaderStyle">
<td>Name</td>
<td>Date</td>
<td>Mobile No</td>
<td>Address</td>
<td>Change of Mobile No</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr >
<td class="tdStyle"><%# Eval("Name") %></td>
<td class="tdStyle"><%# Convert.ToDateTime(Eval("DateOfRegistration")).ToShortDateString()%></td>
<td class="tdStyle">
<asp:Label runat="server" ID="lblDLMobileNumber" Text='<%#Eval("MobileNo")%>' ></asp:Label>
<asp:TextBox runat="server" ID="txtDLMobileNumber" Visible="false" Text='<%#Eval("MobileNo")%>'  ></asp:TextBox>
<asp:Label runat="server" ID="lblDLId" Visible="false" Text='<%#Eval("ID")%>' ></asp:Label>
</td>
<td class="tdStyle"><%# Eval("Address")%></td>
<td class="tdStyle">
<asp:Button runat="server" ID="btnChangeMobileNo" Text="Change Mobile No" CommandName="edit" CssClass="btnStyleBlue"/>
<asp:Button runat="server" ID="btnUpdate" Text="Update" Visible="false" CommandName="update" CssClass="btnStyleGreen"/>
<asp:Button runat="server" ID="btnCancel" Text="Cancel" Visible="false" CommandName="cancel" CssClass="btnStyleRed"/>
</td>   
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:DataList>
</td>
</tr>
<%-- RESEND DATA LIST GRID  --%>
<tr>
<td colspan="3">
<asp:DataList ID="dlResendData" runat="server" GridLines="Both" RepeatColumns="1" 
BorderColor="Black" RepeatDirection="Horizontal" CellPadding="4" 
ForeColor="#333333" 
onitemcommand="dlResendData_ItemCommand">
<HeaderTemplate>
<table style="border:1">
<tr class="HeaderStyle">
<td>Name</td>
<td>Date</td>
<td>Mobile No</td>
<td>Address</td>
<td>Resend</td>
<%--<td>No. of attempts</td>
<td>No. of OTP</td>--%>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr >
<td class="tdStyle"><%# Eval("Name") %></td>
<td class="tdStyle"><%# Convert.ToDateTime(Eval("DateOfRegistration")).ToShortDateString()%></td>
<td class="tdStyle">
<%#Eval("MobileNo")%>
<asp:Label runat="server" ID="lblDLResendId" Visible="false" Text='<%#Eval("ID")%>' ></asp:Label>
</td>
<td class="tdStyle"><%# Eval("Address")%></td>
<td class="tdStyle">
<asp:Button runat="server" ID="btnResend" Text="Resend" CommandName="resend" CssClass="btnStyleBlue" />
</td>   
<%--<td class="tdStyle">2</td>
<td class="tdStyle">4</td>--%>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:DataList>
</td>
</tr>                   
</table>
</div>
</form>
</body>
</html>

Code for the events for this page is below.

/*
*Form Name: frmGridSearch
*Search the data and will show the results in grid.
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.UI.HtmlControls;
using System.Drawing;
namespace GridViewSearchDemo
{
public partial class frmGridSearch : System.Web.UI.Page
{
private int status;
/// <summary>
/// Event handler of page load
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{ 
// if page loads first time then hide all the grids
dlEditData.Visible = false;
dlResendData.Visible = false;
}
}
/// <summary>
/// Performs search operation
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSearch_Click(object sender, EventArgs e)
{
if (ValidateInput())
{
if (rbtnSuccess.Checked == true)
{
status = 1;
}
else
{
status = 0;
}
Helpers.Processor query = new Helpers.Processor();
DataTable dt = query.Search(txtName.Text, txtMobileNo.Text, txtDate.Text, status);
if (dt.Rows.Count != 0)
{
if (rbtnSuccess.Checked == true)
{
dlEditData.DataSource = dt;
dlEditData.Visible = true;
dlResendData.Visible = false;
dlEditData.DataBind();
dlEditData.DataSource = dt;
dlEditData.DataBind();
}
else if (rbtnFail.Checked == true)
{
dlResendData.DataSource = dt;
dlResendData.Visible = true;
dlEditData.Visible = false;
dlResendData.DataBind();
}
lblResult.Visible = false;
}
else
{
lblResult.Text = "No records found.";
lblResult.Visible = true;
lblResult.ForeColor = Color.Red;
dlResendData.Visible = false;
dlEditData.Visible = false;
}
}
}
/// <summary>
/// Validations for the input controls
/// </summary>
/// <returns></returns>
public bool ValidateInput()
{
if (txtDate.Text == string.Empty && txtMobileNo.Text == string.Empty && txtName.Text == string.Empty)
{
lblResult.Text = "Please enter Name (or) Mobile No. (or) Date ";
lblResult.Visible = true;
lblResult.ForeColor = Color.Red;
return false;
}
// TO DO : You can add other validations here, if required.
return true;
}
/// <summary>
/// Performs reset operation, clears all the input fields
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnReset_Click(object sender, EventArgs e)
{
txtDate.Text = string.Empty;
txtMobileNo.Text = string.Empty;
txtName.Text = string.Empty;
rbtnFail.Checked = true;
lblResult.Text = string.Empty;
}
/// <summary>
/// Event handler for command buttons
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dlEditData_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "edit")
{
// Change mobile number button action
Button btnChangeMobileNo = (Button)e.Item.FindControl("btnChangeMobileNo");
Button btnUpdate = (Button)e.Item.FindControl("btnUpdate");
Button btnCancel = (Button)e.Item.FindControl("btnCancel");
TextBox txtDLMobileNumber = (TextBox)e.Item.FindControl("txtDLMobileNumber");
Label lblDLMobileNumber = (Label)e.Item.FindControl("lblDLMobileNumber");
btnUpdate.Visible = true;
btnCancel.Visible = true;
btnChangeMobileNo.Visible = false;
lblDLMobileNumber.Visible = false;
txtDLMobileNumber.Visible = true;
txtDLMobileNumber.Focus();
}
else if(e.CommandName == "update")
{
// updates the mobile number 
Button btnChangeMobileNo = (Button)e.Item.FindControl("btnChangeMobileNo");
Button btnUpdate = (Button)e.Item.FindControl("btnUpdate");
Button btnCancel = (Button)e.Item.FindControl("btnCancel");
TextBox txtDLMobileNumber = (TextBox)e.Item.FindControl("txtDLMobileNumber");
Label lblDLMobileNumber = (Label)e.Item.FindControl("lblDLMobileNumber");
Label lblDLId = (Label)e.Item.FindControl("lblDLId");
Helpers.Processor processor = new Helpers.Processor();
processor.UpdateMobileNumber(lblDLId.Text, txtDLMobileNumber.Text);
string mobileNumber = txtDLMobileNumber.Text;
txtDLMobileNumber.Visible = false;
lblDLMobileNumber.Visible = true;
if (rbtnSuccess.Checked == true)
status = 1;
else
status = 0;
dlEditData.DataSource = processor.Search(txtName.Text, txtMobileNo.Text, txtDate.Text, status);
dlEditData.DataBind();
lblResult.Text="Record updated succesfully";
lblResult.Visible = true;
lblResult.ForeColor = Color.Blue;
btnUpdate.Visible = false;
btnCancel.Visible = false;
btnChangeMobileNo.Visible = true;
}
else if (e.CommandName == "cancel")
{
// Cancels the updation
Button btnChangeMobileNo = (Button)e.Item.FindControl("btnChangeMobileNo");
Button btnUpdate = (Button)e.Item.FindControl("btnUpdate");
Button btnCancel = (Button)e.Item.FindControl("btnCancel");
TextBox txtDLMobileNumber = (TextBox)e.Item.FindControl("txtDLMobileNumber");
Label lblDLMobileNumber = (Label)e.Item.FindControl("lblDLMobileNumber");
btnUpdate.Visible = false;
btnCancel.Visible = false;
btnChangeMobileNo.Visible = true;
txtDLMobileNumber.Visible = false;
lblDLMobileNumber.Visible = true;
}
}
/// <summary>
/// Event handler for command buttons ie., resend
/// </summary>
/// <param name="source"></param>
/// <param name="e"></param>
protected void dlResendData_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "resend")
{
Button btnChangeMobileNo = (Button)e.Item.FindControl("btnChangeMobileNo");
Label lblDLResendId = (Label)e.Item.FindControl("lblDLResendId");
// Resend the data here
Helpers.Processor processor = new Helpers.Processor();
status = 1;  // It indicates success
processor.Resend(lblDLResendId.Text, status);
DataTable dt = processor.Search(txtName.Text, txtMobileNo.Text, txtDate.Text, 0);
if (dt.Rows.Count > 0)
{
dlResendData.DataSource = dt;
dlResendData.DataBind();
lblResult.Visible = true;
lblResult.Text = "Record submitted succesfully.";
lblResult.ForeColor = Color.Blue;
}
else
{
lblResult.Text = "No records found.";
lblResult.ForeColor = Color.Red;
lblResult.Visible = true;
dlResendData.Visible = false;
dlEditData.Visible = false;
}
}
}
}
}

After completion of coding. Build and run the application.

Search button: will search the records based on the input provided.
Reset button: will clears all the fields.

Clicking on resend will update the status as success, the record will be displayed in success list.
You can update the mobile number of successfully registered students by clicking on “Change Mobile No” button.

Thanks for reading this article ,for any queries please use comment system.

One Comment

Giselle Little November 12, 2018 at 8:44 pm - Reply

Hello! I just wanted to ask if you ever have any issues with hackers?
My last blog (wordpress) was hacked and I ended up losing several weeks
of hard work due to no data backup. Do you have any solutions
to protect against hackers?

Asp.net gridview search using sql server and update results

About the Author: Saritha Reddy Syamala

One Comment

Leave A Comment Cancel reply

Asp.net gridview search using sql server and update results

Asp.net gridview search using sql server and update results

Share This Article

About the Author: Saritha Reddy Syamala

One Comment

Leave A Comment Cancel reply