ASP.net - 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 .
Database
In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.
Here we connect the Stores table of Pubs database for these operations. You can see the structure of the table here.
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 id="Head1" 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="gridView1" 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 />
<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.....";
}
}
}
}
Hope you have run the program successfully!!
Comments
Post a Comment