Net-informations.com
SiteMap  | About    

Gridview - Add, Edit and delete

gridview add , edit and delete

In this article we create a Gridview from database and add some additional operations such a add, edit and delete data in the GridView control. In the previous article , we learned how to create a simple GridView at runtime .

GridView at Runtime

Database

In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.

Download

Here we connect the Stores table of Pubs database for these operations. You can see the structure of the table here.

store

After setting the database, create a new Asp.Net project and open the design view and write the following code in the aspx file.

Default.aspx

  

<!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>Gridview Add, Edit and Delete</title>
<script type="text/javascript">
    function deleteConfirm(pubid) {
        var result = confirm('Do you want to delete ' + pubid + ' ?');
        if (result) {
            return true;
        }
        else {
            return false;
        }
    }
</script>
</head>
<body>
    <form id="form1" runat="server">
<div>
<asp:GridView ID="gridView" DataKeyNames="stor_id" runat="server"
        AutoGenerateColumns="false" ShowFooter="true" HeaderStyle-Font-Bold="true"
        onrowcancelingedit="gridView_RowCancelingEdit"
        onrowdeleting="gridView_RowDeleting"
        onrowediting="gridView_RowEditing"
        onrowupdating="gridView_RowUpdating"
        onrowcommand="gridView_RowCommand"
        OnRowDataBound="gridView_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="stor_id">
    <ItemTemplate>
        <asp:Label ID="txtstorid" runat="server" Text='<%#Eval("stor_id") %>'/>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:Label ID="lblstorid" runat="server" width="40px" Text='<%#Eval("stor_id") %>'/>
    </EditItemTemplate>
    <FooterTemplate>
        <asp:TextBox ID="instorid" width="40px" runat="server"/>
        <asp:RequiredFieldValidator ID="vstorid" runat="server" ControlToValidate="instorid" Text="?" ValidationGroup="validaiton"/>
    </FooterTemplate>
</asp:TemplateField>
 <asp:TemplateField HeaderText="stor_name">
      <ItemTemplate>
         <asp:Label ID="lblname" runat="server" Text='<%#Eval("stor_name") %>'/>
     </ItemTemplate>
     <EditItemTemplate>
         <asp:TextBox ID="txtname" width="70px"  runat="server" Text='<%#Eval("stor_name") %>'/>
     </EditItemTemplate>
     <FooterTemplate>
         <asp:TextBox ID="inname"  width="120px" runat="server"/>
         <asp:RequiredFieldValidator ID="vname" runat="server" ControlToValidate="inname" Text="?" ValidationGroup="validaiton"/>
     </FooterTemplate>
 </asp:TemplateField>
 <asp:TemplateField HeaderText="stor_address">
     <ItemTemplate>
         <asp:Label ID="lbladdress" runat="server" Text='<%#Eval("stor_address") %>'/>
     </ItemTemplate>
     <EditItemTemplate>
         <asp:TextBox ID="txtaddress" width="70px"  runat="server" Text='<%#Eval("stor_address") %>'/>
     </EditItemTemplate>
    <FooterTemplate>
        <asp:TextBox ID="inaddress" width="110px"  runat="server"/>
        <asp:RequiredFieldValidator ID="vaddress" runat="server" ControlToValidate="inaddress" Text="?" ValidationGroup="validaiton"/>
    </FooterTemplate>
 </asp:TemplateField>
  <asp:TemplateField HeaderText="city">
       <ItemTemplate>
         <asp:Label ID="lblcity" runat="server" Text='<%#Eval("city") %>'/>
     </ItemTemplate>
     <EditItemTemplate>
         <asp:TextBox ID="txtcity" width="50px"   runat="server" Text='<%#Eval("city") %>'/>
     </EditItemTemplate>
    <FooterTemplate>
        <asp:TextBox ID="incity" width="60px"  runat="server"/>
        <asp:RequiredFieldValidator ID="vcity" runat="server" ControlToValidate="incity" Text="?" ValidationGroup="validaiton"/>
    </FooterTemplate>
 </asp:TemplateField>
   <asp:TemplateField HeaderText="state">
     <ItemTemplate>
         <asp:Label ID="lblstate" runat="server" Text='<%#Eval("state") %>'/>
     </ItemTemplate>
     <EditItemTemplate>
         <asp:TextBox ID="txtstate" width="30px"  runat="server" Text='<%#Eval("state") %>'/>
     </EditItemTemplate>
    <FooterTemplate>
        <asp:TextBox ID="instate" width="40px"   runat="server"/>
        <asp:RequiredFieldValidator ID="vstate" runat="server" ControlToValidate="instate" Text="?" ValidationGroup="validaiton"/>
    </FooterTemplate>
 </asp:TemplateField>
    <asp:TemplateField HeaderText="zip">
     <ItemTemplate>
         <asp:Label ID="lblzip" runat="server" Text='<%#Eval("zip") %>'/>
     </ItemTemplate>
    <EditItemTemplate>
         <asp:TextBox ID="txtzip" width="30px"  runat="server" Text='<%#Eval("zip") %>'/>
     </EditItemTemplate>
    <FooterTemplate>
        <asp:TextBox ID="inzip" width="40px"   runat="server"/>
        <asp:RequiredFieldValidator ID="vzip" runat="server" ControlToValidate="inzip" Text="?" ValidationGroup="validaiton"/>
    </FooterTemplate>
 </asp:TemplateField>
 <asp:TemplateField>
    <EditItemTemplate>
        <asp:Button ID="ButtonUpdate" runat="server" CommandName="Update"  Text="Update"  />
        <asp:Button ID="ButtonCancel" runat="server" CommandName="Cancel"  Text="Cancel" />
    </EditItemTemplate>
    <ItemTemplate>
        <asp:Button ID="ButtonEdit" runat="server" CommandName="Edit"  Text="Edit"  />
        <asp:Button ID="ButtonDelete" runat="server" CommandName="Delete"  Text="Delete"  />
    </ItemTemplate>
    <FooterTemplate>
        <asp:Button ID="ButtonAdd" runat="server" CommandName="AddNew"  Text="Add New Row" ValidationGroup="validaiton" />
    </FooterTemplate>
 </asp:TemplateField>
 </Columns>
</asp:GridView>
    </div>
<div >
<br />&nbsp;&nbsp;&nbsp;&nbsp;
<asp:Label ID="lblmsg" runat="server"></asp:Label>
</div>
    </form>
</body>
</html>

After created the design view and open the code behind and write the following code in the source file

C# Source Code

  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
    private SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            loadStores();
        }
    }
    protected void loadStores()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from stores", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        int count = ds.Tables[0].Rows.Count;
        con.Close();
        if (ds.Tables[0].Rows.Count > 0)
        {
            gridView.DataSource = ds;
            gridView.DataBind();
        }
        else
        {
            ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
            gridView.DataSource = ds;
            gridView.DataBind();
            int columncount = gridView.Rows[0].Cells.Count;
            lblmsg.Text = " No data found !!!";
        }
    }
    protected void gridView_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gridView.EditIndex = e.NewEditIndex;
        loadStores();
    }
    protected void gridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string stor_id = gridView.DataKeys[e.RowIndex].Values["stor_id"].ToString();
        TextBox stor_name = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtname");
        TextBox stor_address = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtaddress");
        TextBox city = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtcity");
        TextBox state = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtstate");
        TextBox zip = (TextBox)gridView.Rows[e.RowIndex].FindControl("txtzip");
        con.Open();
        SqlCommand cmd = new SqlCommand("update stores set stor_name='" + stor_name.Text + "', stor_address='" + stor_address.Text + "', city='" + city.Text + "', state='" + state.Text + "', zip='" + zip.Text + "' where stor_id=" + stor_id, con);
        cmd.ExecuteNonQuery();
        con.Close();
        lblmsg.BackColor  = Color.Blue ;
        lblmsg.ForeColor = Color.White ;
        lblmsg.Text = stor_id +   "        Updated successfully........    ";
        gridView.EditIndex = -1;
        loadStores();
    }
    protected void gridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gridView.EditIndex = -1;
        loadStores();
    }
    protected void gridView_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        string stor_id = gridView.DataKeys[e.RowIndex].Values["stor_id"].ToString();
        con.Open();
        SqlCommand cmd = new SqlCommand("delete from stores where stor_id=" + stor_id, con);
        int result = cmd.ExecuteNonQuery();
        con.Close();
        if (result == 1)
        {
            loadStores();
            lblmsg.BackColor  = Color.Red;
            lblmsg.ForeColor = Color.White ;
            lblmsg.Text = stor_id + "      Deleted successfully.......    ";
        }
    }
    protected void gridView_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            string stor_id = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "stor_id"));
            Button lnkbtnresult = (Button)e.Row.FindControl("ButtonDelete");
            if (lnkbtnresult != null)
            {
                lnkbtnresult.Attributes.Add("onclick", "javascript:return deleteConfirm('" + stor_id + "')");
            }
        }
    }
    protected void gridView_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {
            TextBox instorid = (TextBox)gridView.FooterRow.FindControl("instorid");
            TextBox inname = (TextBox)gridView.FooterRow.FindControl("inname");
            TextBox inaddress = (TextBox)gridView.FooterRow.FindControl("inaddress");
            TextBox incity = (TextBox)gridView.FooterRow.FindControl("incity");
            TextBox instate = (TextBox)gridView.FooterRow.FindControl("instate");
            TextBox inzip = (TextBox)gridView.FooterRow.FindControl("inzip");
            con.Open();
            SqlCommand cmd =
                new SqlCommand(
                    "insert into stores(stor_id,stor_name,stor_address,city,state,zip) values('" + instorid.Text + "','" +
                    inname.Text + "','" + inaddress.Text + "','" + incity.Text + "','" + instate.Text + "','" + inzip.Text + "')", con);
            int result = cmd.ExecuteNonQuery();
            con.Close();
            if (result == 1)
            {
                loadStores();
                lblmsg.BackColor = Color.Green;
                lblmsg.ForeColor = Color.White ;
                lblmsg.Text = instorid.Text + "      Added successfully......    ";
            }
            else
            {
                lblmsg.BackColor = Color.Red;
                lblmsg.ForeColor = Color.White;
                lblmsg.Text = instorid.Text + " Error while adding row.....";
            }
        }
    }
}

After created the design view and open the code behind and write the following code in the source file

VB.Net Source Code

  

Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Private con As New SqlConnection("Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****")
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            loadStores()
        End If
    End Sub
    Protected Sub loadStores()
        con.Open()
        Dim cmd As New SqlCommand("Select * from stores", con)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        Dim count As Integer = ds.Tables(0).Rows.Count
        con.Close()
        If ds.Tables(0).Rows.Count > 0 Then
            gridView.DataSource = ds
            gridView.DataBind()
        Else
            ds.Tables(0).Rows.Add(ds.Tables(0).NewRow())
            gridView.DataSource = ds
            gridView.DataBind()
            Dim columncount As Integer = gridView.Rows(0).Cells.Count
            lblmsg.Text = " No data found !!!"
        End If
    End Sub
    Protected Sub gridView_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        gridView.EditIndex = e.NewEditIndex
        loadStores()
    End Sub
    Protected Sub gridView_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
        Dim stor_id As String = gridView.DataKeys(e.RowIndex).Values("stor_id").ToString()
        Dim stor_name As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtname"), TextBox)
        Dim stor_address As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtaddress"), TextBox)
        Dim city As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtcity"), TextBox)
        Dim state As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtstate"), TextBox)
        Dim zip As TextBox = DirectCast(gridView.Rows(e.RowIndex).FindControl("txtzip"), TextBox)
        con.Open()
        Dim cmd As New SqlCommand(Convert.ToString("update stores set stor_name='" + stor_name.Text + "', stor_address='" + stor_address.Text + "', city='" + city.Text + "', state='" + state.Text + "', zip='" + zip.Text + "' where stor_id=") & stor_id, con)
        cmd.ExecuteNonQuery()
        con.Close()
        lblmsg.BackColor = Color.Blue
        lblmsg.ForeColor = Color.White
        lblmsg.Text = stor_id & Convert.ToString("        Updated successfully........    ")
        gridView.EditIndex = -1
        loadStores()
    End Sub
    Protected Sub gridView_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
        gridView.EditIndex = -1
        loadStores()
    End Sub
    Protected Sub gridView_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)
        Dim stor_id As String = gridView.DataKeys(e.RowIndex).Values("stor_id").ToString()
        con.Open()
        Dim cmd As New SqlCommand("delete from stores where stor_id=" + stor_id, con)
        Dim result As Integer = cmd.ExecuteNonQuery()
        con.Close()
        If result = 1 Then
            loadStores()
            lblmsg.BackColor = Color.Red
            lblmsg.ForeColor = Color.White
            lblmsg.Text = stor_id + "      Deleted successfully.......    "
        End If
    End Sub
    Protected Sub gridView_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim stor_id As String = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "stor_id"))
            Dim lnkbtnresult As Button = DirectCast(e.Row.FindControl("ButtonDelete"), Button)
            If lnkbtnresult IsNot Nothing Then
                lnkbtnresult.Attributes.Add("onclick", (Convert.ToString("javascript:return deleteConfirm('") & stor_id) + "')")
            End If
        End If
    End Sub
    Protected Sub gridView_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
        If e.CommandName.Equals("AddNew") Then
            Dim instorid As TextBox = DirectCast(gridView.FooterRow.FindControl("instorid"), TextBox)
            Dim inname As TextBox = DirectCast(gridView.FooterRow.FindControl("inname"), TextBox)
            Dim inaddress As TextBox = DirectCast(gridView.FooterRow.FindControl("inaddress"), TextBox)
            Dim incity As TextBox = DirectCast(gridView.FooterRow.FindControl("incity"), TextBox)
            Dim instate As TextBox = DirectCast(gridView.FooterRow.FindControl("instate"), TextBox)
            Dim inzip As TextBox = DirectCast(gridView.FooterRow.FindControl("inzip"), TextBox)
            con.Open()
            Dim cmd As New SqlCommand("insert into stores(stor_id,stor_name,stor_address,city,state,zip) values('" + instorid.Text + "','" + inname.Text + "','" + inaddress.Text + "','" + incity.Text + "','" + instate.Text + "','" + inzip.Text + "')", con)
            Dim result As Integer = cmd.ExecuteNonQuery()
            con.Close()
            If result = 1 Then
                loadStores()
                lblmsg.BackColor = Color.Green
                lblmsg.ForeColor = Color.White
                lblmsg.Text = instorid.Text + "      Added successfully......    "
            Else
                lblmsg.BackColor = Color.Red
                lblmsg.ForeColor = Color.White
                lblmsg.Text = instorid.Text + " Error while adding row....."
            End If
        End If
    End Sub
End Class

Hope you have run the program successfully!!






net-informations.com (C) 2016    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.