Grid view edit delete and update in asp.net

Database-SQL

USE [sqlcon]
GO
/****** Object: Table [dbo].[myTable] Script Date: 12/08/2012 10:08:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[myTable](
[AutoID] [int] IDENTITY(1,1) NOT NULL,
[PageName] [varchar](50) NULL,
[PageDescription] [varchar](500) NULL,
[Active] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Stored Procedure :

USE [sqlcon]
GO
/****** Object: StoredProcedure [dbo].[spUpdateData] Script Date: 12/08/2012 10:09:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpdateData]
@AutoID [int],
@PageName [varchar](50),
@PageDescription [varchar](500),
@Active [Bit]
AS
UPDATE myTable
SET PageName=@PageName,
PageDescription=@PageDescription ,
Active=@Active
WHERE AutoID=@AutoID

IN ASPX-Page :

<form id="form1" runat="server">
<div>
<asp:Label ID="lblMessage" runat="Server" ForeColor="Red"></asp:Label>

<asp:GridView ID="GridView1" runat="Server" AutoGenerateColumns="False" BorderWidth="1"

DataKeyNames="AutoID" AutoGenerateEditButton="True" OnRowEditing="EditRecord"

OnRowCancelingEdit="CancelRecord" OnRowUpdating="UpdateRecord" CellPadding="4"

HeaderStyle-HorizontalAlign="left" OnRowDeleting="DeleteRecord" RowStyle-VerticalAlign="Top"

ForeColor="#333333" GridLines="None">

<Columns>

<asp:BoundField DataField="AutoID" HeaderText="AutoID" ReadOnly="True" />

<asp:TemplateField HeaderText="Page Name">

<ItemTemplate>

<%# Eval("PageName") %>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtPageName" runat="Server" Text='<%# Eval("PageName") %>' Columns="30"></asp:TextBox>

<asp:RequiredFieldValidator ID="req1" runat="Server" Text="*" ControlToValidate="txtPageName"></asp:RequiredFieldValidator>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Page Description">

<ItemTemplate>

<%# Eval("PageDescription") %>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtPageDesc" runat="Server" TextMode="MultiLine" Rows="10" Columns="50"

Text='<%# Eval("PageDescription") %>'></asp:TextBox>

<asp:RequiredFieldValidator ID="req2" runat="Server" Text="*" ControlToValidate="txtPageDesc"></asp:RequiredFieldValidator>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Active">

<ItemTemplate>

<%# Eval("Active") %>

</ItemTemplate>

<EditItemTemplate>

<asp:DropDownList ID="dropActive" runat="server" SelectedValue='<%# Eval("Active").ToString().ToLower().Equals("true") ? "True" : "False" %>'>

<asp:ListItem Text="Yes" Value="True"></asp:ListItem>

<asp:ListItem Text="No" Value="False"></asp:ListItem>

</asp:DropDownList>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Delete">

<ItemTemplate>

<span onclick="return confirm('Are you sure to Delete the record?')">

<asp:LinkButton ID="lnkB" runat="Server" Text="Delete" CommandName="Delete"></asp:LinkButton>

</span>

</ItemTemplate>

</asp:TemplateField>

</Columns>

<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

<RowStyle BackColor="#FFFBD6" ForeColor="#333333" VerticalAlign="Top" />

<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />

<AlternatingRowStyle BackColor="White" />

</asp:GridView>

</div>
</form>

IN CS page:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class samplegrid : System.Web.UI.Page
{

static string connStr = ConfigurationManager.ConnectionStrings["reg"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{

BindData();

}

}

private void BindData()
{

SqlDataAdapter dAd = new SqlDataAdapter("select * from myTable", conn);

DataSet dSet = new DataSet();

try
{

dAd.Fill(dSet, "PagesData");

GridView1.DataSource = dSet.Tables["PagesData"].DefaultView;

GridView1.DataBind();

}

catch (Exception ee)
{

lblMessage.Text = ee.Message.ToString();

}

finally
{

dSet.Dispose();

dAd.Dispose();

conn.Close();

conn.Dispose();

}

}

protected void EditRecord(object sender, GridViewEditEventArgs e)
{

GridView1.EditIndex = e.NewEditIndex;

BindData();

}

protected void CancelRecord(object sender, GridViewCancelEditEventArgs e)
{

GridView1.EditIndex = -1;

BindData();

}

protected void UpdateRecord(object sender, GridViewUpdateEventArgs e)
{

GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];

int autoid = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());

TextBox tPageName = (TextBox)row.FindControl("txtPageName");

TextBox tPageDesc = (TextBox)row.FindControl("txtPageDesc");

DropDownList dActive = (DropDownList)row.FindControl("dropActive");

//SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());

SqlCommand dCmd = new SqlCommand();

try
{

conn.Open();

dCmd.CommandText = "spUpdateData";

dCmd.CommandType = CommandType.StoredProcedure;

dCmd.Parameters.Add("@AutoID", SqlDbType.Int).Value = autoid;

dCmd.Parameters.Add("@PageName", SqlDbType.VarChar, 50).Value = tPageName.Text.Trim();

dCmd.Parameters.Add("@PageDescription", SqlDbType.VarChar, 500).Value = tPageDesc.Text.Trim();

dCmd.Parameters.Add("@Active", SqlDbType.Bit).Value = bool.Parse(dActive.SelectedValue);

dCmd.Connection = conn;

dCmd.ExecuteNonQuery();

//conn.Open();
//SqlCommand cmd = new SqlCommand("update myTable SET PageName='"+tPageName.Text+"',PageDescription='"+tPageDesc.Text+"',Active='"+dActive.SelectedValue+"' where AutoID='"+autoid+"'", conn);
//int result = cmd.ExecuteNonQuery();
//conn.Close();
//if (result == 1)
//{
// BindData();

//}

//lblMessage.Text = "Record Updated successfully.";

// Refresh the data

GridView1.EditIndex = -1;

BindData();

}

catch (SqlException ee)
{

lblMessage.Text = ee.Message;

}

finally
{

//dCmd.Dispose();

conn.Close();

conn.Dispose();

}

}

protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
{

string autoid = GridView1.DataKeys[e.RowIndex].Value.ToString();

//SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());

SqlCommand dCmd = new SqlCommand();

try
{

LinkButton lnkbtn = sender as LinkButton;
//getting particular row linkbutton
// GridViewRow gvrow = lnkbtn.NamingContainer as GridViewRow;
//getting userid of particular row
// int userid = Convert.ToInt32(GridView1.DataKeys[gvrow.RowIndex].Value.ToString());
// string username = gvrow.Cells[0].Text;
conn.Open();
SqlCommand cmd = new SqlCommand("delete from myTable where AutoID=" + autoid, conn);
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result == 1)
{
BindData();
//Displaying alert message after successfully deletion of user
//ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert('" + PackageName + " details deleted successfully')", true);
//GridView1.DataSourceID = null;
//GridView1.DataSourceID = "SqlDataSource1";
}

//dCmd.CommandText = "spDeleteData";
//dCmd.CommandType = CommandType.StoredProcedure;
//dCmd.Parameters.Add("@AutoID", SqlDbType.Int).Value = Int32.Parse(autoid);
//dCmd.Connection = conn;
//dCmd.ExecuteNonQuery();
//lblMessage.Text = "Record Deleted successfully.";
// Refresh the data

BindData();

}

catch (SqlException ee)
{

lblMessage.Text = ee.Message;

}

finally
{

dCmd.Dispose();

conn.Close();

conn.Dispose();

}

}

}

Friday 7 December 2012

Grid view edit delete and update in asp.net

No comments:

Post a Comment