.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ASP.NET Controls >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

GridView Insert update delete search

Posted By:satyapriyanayak       Posted Date: November 14, 2013    Points: 40    Category: ASP.NET Controls    URL: http://www.dotnetspark.com  

We will know how to insert update delete search in GridView.
 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" 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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView ID="ResultGridView" runat="server" AutoGenerateColumns="False" ShowFooter="true"
        DataKeyNames="VendorId"
        AllowPaging="True"
        CellPadding="3"
        OnPageIndexChanging="ResultGridView_PageIndexChanging"
        OnRowDeleting="ResultGridView_RowDeleting"
        OnRowEditing="ResultGridView_RowEditing"
            OnRowUpdating="ResultGridView_RowUpdating"
            OnRowCancelingEdit="ResultGridView_RowCancelingEdit" PageSize="5"
            BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px"
            CellSpacing="2" OnRowCommand="ResultGridView_RowCommand" AllowSorting="true"
            onsorting="ResultGridView_Sorting">
            <Columns>
                <asp:BoundField DataField="VendorId" HeaderText="VendorId" InsertVisible="False"
                    ReadOnly="True" SortExpression="VendorId" />
                <asp:TemplateField HeaderText="FirstName" SortExpression="VendorFName">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtFName" Width="100px" runat="server" Text='<%# Bind("VendorFName") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                    <asp:TextBox ID="txtFName1" runat="server"  Width="100px"></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("VendorFName") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="LastName" SortExpression="VendorLName">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtLName"  Width="100px" runat="server" Text='<%# Bind("VendorLName") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                    <asp:TextBox ID="txtLName1" Width="100px" runat="server" ></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("VendorLName") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="City" SortExpression="VendorCity">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtCity"  Width="100px" runat="server" Text='<%# Bind("VendorCity") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                    <asp:TextBox ID="txtCity1"  Width="100px" runat="server" ></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("VendorCity") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="State" SortExpression="VendorState">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtState" Width="100px" runat="server" Text='<%# Bind("VendorState") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                    <asp:TextBox ID="txtState1" Width="100px" runat="server" ></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label4" runat="server" Text='<%# Bind("VendorState") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Country" SortExpression="VendorCountry">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtCountry" Width="100px" runat="server" Text='<%# Bind("VendorCountry") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                    <asp:TextBox ID="txtCountry1" Width="100px" runat="server" ></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label5" runat="server" Text='<%# Bind("VendorCountry") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="VendorDescription" SortExpression="VendorDescription">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtDescription" TextMode="MultiLine" runat="server" Text='<%# Bind("VendorDescription") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <FooterTemplate>
                    <asp:TextBox ID="txtDescription1" runat="server" TextMode="MultiLine" ></asp:TextBox>
                    </FooterTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Label6" runat="server" Text='<%# Bind("VendorDescription") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                
                
                
                
                <asp:TemplateField HeaderText="Edit" ShowHeader="False">
                <EditItemTemplate>
                  <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update" Text="Update"></asp:LinkButton>
                  <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                </EditItemTemplate>
                <FooterTemplate>
                  <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="AddNew" Text="Add New"></asp:LinkButton>
                </FooterTemplate>
                <ItemTemplate>
                  <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"></asp:LinkButton>
                </ItemTemplate>
                </asp:TemplateField>
                <asp:CommandField HeaderText="Delete" ShowDeleteButton="True"  ShowHeader="True" />
                <asp:CommandField HeaderText="Select" ShowSelectButton="True"  ShowHeader="True" />

            </Columns>
            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
        </asp:GridView><br /><br /><br />
        <asp:Label ID="Label7" runat="server" Text="Search By Firstname"></asp:Label>
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    <asp:Button ID="btn_search" runat="server" Text="Search"
        onclick="btn_search_Click" />
    </div>
    
    </form>
    
</body>
</html>




using System;
using System.Configuration;
using System.Data;
using System.Linq;
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;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    //SqlConnection conn = new SqlConnection(connStr);
    SqlDataAdapter ad = new SqlDataAdapter();
    SqlCommand cmd = new SqlCommand();
    DataTable dataTable;
    SqlDataAdapter sqlda;
    DataSet ds;
    string str;
    protected void Page_Load(object sender, EventArgs e)
    {
        Session["sortBy"] = null;
        if (!IsPostBack)
        {
            FillVendorGrid();
        }
    }
    private void FillVendorGrid()
    {
        SqlConnection conn = new SqlConnection(connStr);
        dataTable = new DataTable();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Vendor";
        ad = new SqlDataAdapter(cmd);
        ad.Fill(dataTable);
        ResultGridView.DataSource = dataTable;
        ResultGridView.DataBind();
        
    }

    protected void ResultGridView_RowEditing(object sender, GridViewEditEventArgs e)
    {
        ResultGridView.EditIndex = e.NewEditIndex;
        FillVendorGrid();
    }


    protected void ResultGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        ResultGridView.PageIndex = e.NewPageIndex;
        FillVendorGrid();
    }

    protected void ResultGridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        SqlConnection conn = new SqlConnection(connStr);
        cmd.Connection = conn;
        cmd.CommandText = "DELETE FROM Vendor WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        FillVendorGrid();

    }

    protected void ResultGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox txtFName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtFName");
        TextBox txtLName = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtLName");
        TextBox txtCity = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCity");
        TextBox txtState = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtState");
        TextBox txtCountry = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtCountry");
        TextBox txtDescription = (TextBox)ResultGridView.Rows[e.RowIndex].FindControl("txtDescription");

        SqlConnection conn = new SqlConnection(connStr);
        cmd.Connection = conn;
        cmd.CommandText = "UPDATE Vendor SET VendorFName ='" + txtFName.Text + "',VendorLName ='" + txtLName.Text + "',VendorCity ='" + txtCity.Text + "',VendorState ='" + txtState.Text + "',VendorCountry ='" + txtCountry.Text + "',VendorDescription ='" + txtDescription.Text + "'   WHERE VendorId='" + ResultGridView.DataKeys[e.RowIndex].Values[0].ToString() + "'";
        conn.Open();
        cmd.ExecuteNonQuery();
        ResultGridView.EditIndex = -1;
        FillVendorGrid();
        conn.Close();

    }

    protected void ResultGridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        ResultGridView.EditIndex = -1;
        FillVendorGrid();

    }

    protected void ResultGridView_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {

            TextBox txtFName = (TextBox)ResultGridView.FooterRow.FindControl("txtFName1");
            TextBox txtLName = (TextBox)ResultGridView.FooterRow.FindControl("txtLName1");
            TextBox txtCity = (TextBox)ResultGridView.FooterRow.FindControl("txtCity1");
            TextBox txtState = (TextBox)ResultGridView.FooterRow.FindControl("txtState1");
            TextBox txtCountry = (TextBox)ResultGridView.FooterRow.FindControl("txtCountry1");
            TextBox txtDescription = (TextBox)ResultGridView.FooterRow.FindControl("txtDescription1");
            SqlConnection conn = new SqlConnection(connStr);
            cmd.Connection = conn;
            cmd.CommandText = "INSERT INTO Vendor(VendorFName, VendorLName,VendorCity,VendorState,VendorCountry,VendorDescription) Values('" + txtFName.Text + "', '" + txtLName.Text + "', '" + txtCity.Text + "', '" + txtState.Text + "', '" + txtCountry.Text + "' , '" + txtDescription.Text + "')";
            conn.Open();
            cmd.ExecuteNonQuery();
            FillVendorGrid();
            conn.Close();
        }
    }
    protected void btn_search_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        str = "select * from Vendor where VendorFName like '" + TextBox1.Text + "%'";
        cmd = new SqlCommand(str, conn);
        sqlda = new SqlDataAdapter(cmd);
        ds = new DataSet();
        sqlda.Fill(ds, "Vendor");
        conn.Close();
       
        ResultGridView.DataSource = ds;
        ResultGridView.DataMember = "Vendor";
        ResultGridView.DataBind();

    }
    protected void ResultGridView_Sorting(object sender, GridViewSortEventArgs e)
    {
        Session["sortBy"] = e.SortExpression;
        FillVendorGrid();
    }
}


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend