1. Create Table
Create Table TBL_AppendGrid
(
Stu_ID Int identity,
Stu_Name varchar(50),
Stu_Location varchar(50),
Stu_ContactNo varchar(20)
)
2. Create Procedure
CREATE procedure PR_InsertTo_AppendGrid
(
@Name varchar(30),
@Location varchar(50),
@Mobile varchar(11)
)
as
begin
if not exists(select Stu_Name, Stu_Location, Stu_ContactNo from TBL_AppendGrid where Stu_Name=@Name and Stu_Location=@Location and Stu_ContactNo=@Mobile)
begin
insert into TBL_AppendGrid(Stu_Name, Stu_Location, Stu_ContactNo) values(@Name, @Location, @Mobile)
select '1'
end
else
begin
update TBL_AppendGrid set Stu_Name=@Name, Stu_Location=@location, Stu_ContactNo=@Mobile where Stu_Name=@Name and Stu_Location=@Location and Stu_ContactNo=@Mobile
select '0'
end
end
3. <appSettings>
<add key="ConnectionString" value="Server=Your_Server; Database=Your_Data; uid=sa; pwd=saa;" />
</appSettings>
4. Open Visual Studio 2008 or else..
and paste this code
................................................................................................................................
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AppendableGridView.aspx.cs" Inherits="AppendableGridView" EnableEventValidation ="false" %>
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<table align="center" >
<tr><td><center ><b><asp:Label ID="lblres" runat="server" /></b></center>
</td></tr>
<tr><td>
<asp:UpdatePanel ID ="UpdatePanel1" runat ="server" >
<ContentTemplate >
<asp:GridView ID="AppendGrid" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px"
CellPadding="4" GridLines="Horizontal" Height="141px"
OnRowCommand ="AppendGrid_RowCommand" >
<RowStyle BackColor="White" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="RowNumber" HeaderText="SNo" />
<asp:TemplateField HeaderText="Name">
<ItemTemplate >
<asp:TextBox ID="txtIName" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtIName"
ErrorMessage="*" SetFocusOnError="True"></asp:RequiredFieldValidator>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Location">
<ItemTemplate >
<asp:TextBox ID="txtIlocation" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtIlocation"
ErrorMessage="*" SetFocusOnError="True"></asp:RequiredFieldValidator>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Contact No">
<ItemTemplate >
<asp:TextBox ID="txtIMob" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtIMob"
ErrorMessage="*" SetFocusOnError="True"></asp:RequiredFieldValidator>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#333333" />
<PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#FFFFCC" />
</asp:GridView></ContentTemplate></asp:UpdatePanel>
</td></tr>
<tr><td><center ><asp:Button ID="hidden" Text="Append Row" runat ="server" Visible="true"
onclick="hidden_Click" />
<asp:Button ID="btnSave" runat ="server" Text="Save" onclick="btnSave_Click" />
</center></td></tr>
</table>
</div>
</form>
</body>
</html>
5. Code Behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
public partial class AppendableGridView : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
FirstGridViewRow();
}
}
private void FirstGridViewRow()
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));
dt.Columns.Add(new DataColumn("Col1", typeof(string)));
dt.Columns.Add(new DataColumn("Col2", typeof(string)));
dt.Columns.Add(new DataColumn("Col3", typeof(string)));
dr = dt.NewRow();
dr["RowNumber"] = 1;
dr["Col1"] = string.Empty;
dr["Col2"] = string.Empty;
dr["Col3"] = string.Empty;
dt.Rows.Add(dr);
ViewState["CurrentTable"] = dt;
AppendGrid.DataSource = dt;
AppendGrid.DataBind();
}
private void appendNewRow()
{
int rowIndex = 0;
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
DataRow drCurrentRow = null;
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
TextBox txtName = (TextBox)AppendGrid.Rows[rowIndex].Cells[1].FindControl("txtIName");
TextBox txtLocation = (TextBox)AppendGrid.Rows[rowIndex].Cells[2].FindControl("txtIlocation");
TextBox txtMob = (TextBox)AppendGrid.Rows[rowIndex].Cells[3].FindControl("txtIMob");
drCurrentRow = dtCurrentTable.NewRow();
drCurrentRow["RowNumber"] = i + 1;
dtCurrentTable.Rows[i - 1]["Col1"] = txtName.Text;
dtCurrentTable.Rows[i - 1]["Col2"] = txtLocation.Text;
dtCurrentTable.Rows[i - 1]["Col3"] = txtMob.Text;
rowIndex++;
}
dtCurrentTable.Rows.Add(drCurrentRow);
ViewState["CurrentTable"] = dtCurrentTable;
AppendGrid.DataSource = dtCurrentTable;
AppendGrid.DataBind();
TextBox txn = (TextBox)AppendGrid.Rows[AppendGrid .Rows .Count-1 ].Cells[1].FindControl("txtIName");
txn.Focus();
// Focus on last row.
}
}
else
{
Response.Write("ViewState Null Hai");
}
storeExistingValue();
}
private void storeExistingValue()
{
int rowIndex = 0;
if (ViewState["CurrentTable"] != null)
{
DataTable dt = (DataTable)ViewState["CurrentTable"];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
TextBox txtName = (TextBox)AppendGrid.Rows[rowIndex].Cells[1].FindControl("txtIName");
TextBox txtLocation = (TextBox)AppendGrid.Rows[rowIndex].Cells[2].FindControl("txtIlocation");
TextBox txtMob = (TextBox)AppendGrid.Rows[rowIndex].Cells[3].FindControl("txtIMob");
txtName.Text = dt.Rows[i]["Col1"].ToString();
txtLocation.Text = dt.Rows[i]["Col2"].ToString();
txtMob.Text = dt.Rows[i]["Col3"].ToString();
rowIndex++;
}
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
foreach (GridViewRow row in this.AppendGrid .Rows )
{
try
{
TextBox txtName = (TextBox)row.FindControl("txtIName");
TextBox txtLocation = (TextBox)row.FindControl("txtIlocation");
TextBox txtMob = (TextBox)row.FindControl("txtIMob");
con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("PR_InsertTo_AppendGrid", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@Location", txtLocation.Text.ToString());
cmd.Parameters.AddWithValue("Mobile", txtMob.Text.ToString());
con.Open();
int res = cmd.ExecuteNonQuery();
if (res.Equals("1"))
{
lblres.Text = "Saved";
}
else
{ lblres.Text = "Updated"; }
}
catch (Exception ex)
{
lblres.Text = ex.Message;
}
finally
{
con.Close();
con.Dispose();
}
}
ViewState["CurrentTable"] = null;
Page.Response.Redirect(Request.Url.ToString());
}
protected void AppendGrid_RowCommand(object sender, GridViewCommandEventArgs e)
{
}
protected void hidden_Click(object sender, EventArgs e)
{
appendNewRow();
}
}
All The Best!!!
Create Table TBL_AppendGrid
(
Stu_ID Int identity,
Stu_Name varchar(50),
Stu_Location varchar(50),
Stu_ContactNo varchar(20)
)
2. Create Procedure
CREATE procedure PR_InsertTo_AppendGrid
(
@Name varchar(30),
@Location varchar(50),
@Mobile varchar(11)
)
as
begin
if not exists(select Stu_Name, Stu_Location, Stu_ContactNo from TBL_AppendGrid where Stu_Name=@Name and Stu_Location=@Location and Stu_ContactNo=@Mobile)
begin
insert into TBL_AppendGrid(Stu_Name, Stu_Location, Stu_ContactNo) values(@Name, @Location, @Mobile)
select '1'
end
else
begin
update TBL_AppendGrid set Stu_Name=@Name, Stu_Location=@location, Stu_ContactNo=@Mobile where Stu_Name=@Name and Stu_Location=@Location and Stu_ContactNo=@Mobile
select '0'
end
end
3. <appSettings>
<add key="ConnectionString" value="Server=Your_Server; Database=Your_Data; uid=sa; pwd=saa;" />
</appSettings>
4. Open Visual Studio 2008 or else..
and paste this code
................................................................................................................................
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AppendableGridView.aspx.cs" Inherits="AppendableGridView" EnableEventValidation ="false" %>
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<table align="center" >
<tr><td><center ><b><asp:Label ID="lblres" runat="server" /></b></center>
</td></tr>
<tr><td>
<asp:UpdatePanel ID ="UpdatePanel1" runat ="server" >
<ContentTemplate >
<asp:GridView ID="AppendGrid" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#336666" BorderStyle="Double" BorderWidth="3px"
CellPadding="4" GridLines="Horizontal" Height="141px"
OnRowCommand ="AppendGrid_RowCommand" >
<RowStyle BackColor="White" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="RowNumber" HeaderText="SNo" />
<asp:TemplateField HeaderText="Name">
<ItemTemplate >
<asp:TextBox ID="txtIName" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtIName"
ErrorMessage="*" SetFocusOnError="True"></asp:RequiredFieldValidator>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Location">
<ItemTemplate >
<asp:TextBox ID="txtIlocation" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtIlocation"
ErrorMessage="*" SetFocusOnError="True"></asp:RequiredFieldValidator>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Contact No">
<ItemTemplate >
<asp:TextBox ID="txtIMob" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtIMob"
ErrorMessage="*" SetFocusOnError="True"></asp:RequiredFieldValidator>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="White" ForeColor="#333333" />
<PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#FFFFCC" />
</asp:GridView></ContentTemplate></asp:UpdatePanel>
</td></tr>
<tr><td><center ><asp:Button ID="hidden" Text="Append Row" runat ="server" Visible="true"
onclick="hidden_Click" />
<asp:Button ID="btnSave" runat ="server" Text="Save" onclick="btnSave_Click" />
</center></td></tr>
</table>
</div>
</form>
</body>
</html>
5. Code Behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
public partial class AppendableGridView : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
FirstGridViewRow();
}
}
private void FirstGridViewRow()
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));
dt.Columns.Add(new DataColumn("Col1", typeof(string)));
dt.Columns.Add(new DataColumn("Col2", typeof(string)));
dt.Columns.Add(new DataColumn("Col3", typeof(string)));
dr = dt.NewRow();
dr["RowNumber"] = 1;
dr["Col1"] = string.Empty;
dr["Col2"] = string.Empty;
dr["Col3"] = string.Empty;
dt.Rows.Add(dr);
ViewState["CurrentTable"] = dt;
AppendGrid.DataSource = dt;
AppendGrid.DataBind();
}
private void appendNewRow()
{
int rowIndex = 0;
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
DataRow drCurrentRow = null;
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
TextBox txtName = (TextBox)AppendGrid.Rows[rowIndex].Cells[1].FindControl("txtIName");
TextBox txtLocation = (TextBox)AppendGrid.Rows[rowIndex].Cells[2].FindControl("txtIlocation");
TextBox txtMob = (TextBox)AppendGrid.Rows[rowIndex].Cells[3].FindControl("txtIMob");
drCurrentRow = dtCurrentTable.NewRow();
drCurrentRow["RowNumber"] = i + 1;
dtCurrentTable.Rows[i - 1]["Col1"] = txtName.Text;
dtCurrentTable.Rows[i - 1]["Col2"] = txtLocation.Text;
dtCurrentTable.Rows[i - 1]["Col3"] = txtMob.Text;
rowIndex++;
}
dtCurrentTable.Rows.Add(drCurrentRow);
ViewState["CurrentTable"] = dtCurrentTable;
AppendGrid.DataSource = dtCurrentTable;
AppendGrid.DataBind();
TextBox txn = (TextBox)AppendGrid.Rows[AppendGrid .Rows .Count-1 ].Cells[1].FindControl("txtIName");
txn.Focus();
// Focus on last row.
}
}
else
{
Response.Write("ViewState Null Hai");
}
storeExistingValue();
}
private void storeExistingValue()
{
int rowIndex = 0;
if (ViewState["CurrentTable"] != null)
{
DataTable dt = (DataTable)ViewState["CurrentTable"];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
TextBox txtName = (TextBox)AppendGrid.Rows[rowIndex].Cells[1].FindControl("txtIName");
TextBox txtLocation = (TextBox)AppendGrid.Rows[rowIndex].Cells[2].FindControl("txtIlocation");
TextBox txtMob = (TextBox)AppendGrid.Rows[rowIndex].Cells[3].FindControl("txtIMob");
txtName.Text = dt.Rows[i]["Col1"].ToString();
txtLocation.Text = dt.Rows[i]["Col2"].ToString();
txtMob.Text = dt.Rows[i]["Col3"].ToString();
rowIndex++;
}
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
foreach (GridViewRow row in this.AppendGrid .Rows )
{
try
{
TextBox txtName = (TextBox)row.FindControl("txtIName");
TextBox txtLocation = (TextBox)row.FindControl("txtIlocation");
TextBox txtMob = (TextBox)row.FindControl("txtIMob");
con = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
SqlCommand cmd = new SqlCommand("PR_InsertTo_AppendGrid", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@Location", txtLocation.Text.ToString());
cmd.Parameters.AddWithValue("Mobile", txtMob.Text.ToString());
con.Open();
int res = cmd.ExecuteNonQuery();
if (res.Equals("1"))
{
lblres.Text = "Saved";
}
else
{ lblres.Text = "Updated"; }
}
catch (Exception ex)
{
lblres.Text = ex.Message;
}
finally
{
con.Close();
con.Dispose();
}
}
ViewState["CurrentTable"] = null;
Page.Response.Redirect(Request.Url.ToString());
}
protected void AppendGrid_RowCommand(object sender, GridViewCommandEventArgs e)
{
}
protected void hidden_Click(object sender, EventArgs e)
{
appendNewRow();
}
}
All The Best!!!
No comments:
Post a Comment