FIROZ DOT NET: Q. How to do crud operation using gridview?

Step 1 . Create table Pubs.

CREATE TABLE [dbo].[stores](
[stor_id] [char](4) NOT NULL,
[stor_name] [varchar](40) NULL,
[stor_address] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[zip] [char](5) NULL,
CONSTRAINT [UPK_storeid] PRIMARY KEY CLUSTERED
(
[stor_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 2. Now create your aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default5.aspx.cs" Inherits="Default5" %>

<!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 />
    
<asp:Label ID="lblmsg" runat="server"></asp:Label>
</div>
</form>
</body>

</html>

Step3. Now write here your 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 Default5 : System.Web.UI.Page

{

private SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=pubs;User ID=sa;Password=123");

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.....";

}

Now output will come like that

Note:- Here we are using Required field validation using asp control it will give Jquery Error.So solve this problem use this code inside web .config.

</appSettings>

FIROZ DOT NET

ASP DOT NET

Saturday, June 23, 2018

Q. How to do crud operation using gridview?

No comments:

Post a Comment

How to to select duplicate rows from sql server?

Search This Blog