Asp.net insert, Edit, update, delete data in gridview

By: Suresh Dasari Feb 9, 2011

Introduction:

In this article I will explain how to insert, edit, update and delete data in gridview using asp.net.

Description:

I have one gridview I need to write code to insert data into gridview after that I need to edit that gridview data and update it and if I want to delete the record in grdview we need to delete record simply by click on delete button of particular row to achieve these functionalities I have used some of gridview events those are

1 1) Onrowcancelingedit

2 2) Onrowediting

3 3) Onrowupdating

4 4) Onrowcancelingedit

5 5) Onrowdeleting

By Using above griview events we can insert, edit, update and delete the data in gridview. My Question is how we can use these events in our coding before to see those details first design table in database and give name Employee_Details

ColumnName	DataType
UserId	Int(set identity property=true)
UserName	varchar(50)
City	varchar(50)
Designation	varchar(50)

After completion table creation design aspx page like this

<title>Untitled Page</title>

.Gridview

{

font-family:Verdana;

font-size:10pt;

font-weight:normal;

color:black;

}

</style>

</head>

<body>

<div>

<asp:GridView ID="gvDetails" DataKeyNames="UserId,UserName" runat="server"

AutoGenerateColumns="false" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"

ShowFooter="true" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White"

onrowcancelingedit="gvDetails_RowCancelingEdit"

onrowdeleting="gvDetails_RowDeleting" onrowediting="gvDetails_RowEditing"

onrowupdating="gvDetails_RowUpdating"

onrowcommand="gvDetails_RowCommand">

<asp:TemplateField>

<asp:ImageButton ID="imgbtnUpdate" CommandName="Update" runat="server" ImageUrl="~/Images/update.jpg" ToolTip="Update" Height="20px" Width="20px" />

<asp:ImageButton ID="imgbtnCancel" runat="server" CommandName="Cancel" ImageUrl="~/Images/Cancel.jpg" ToolTip="Cancel" Height="20px" Width="20px" />

</EditItemTemplate>

<asp:ImageButton ID="imgbtnEdit" CommandName="Edit" runat="server" ImageUrl="~/Images/Edit.jpg" ToolTip="Edit" Height="20px" Width="20px" />

<asp:ImageButton ID="imgbtnDelete" CommandName="Delete" Text="Edit" runat="server" ImageUrl="~/Images/delete.jpg" ToolTip="Delete" Height="20px" Width="20px" />

</ItemTemplate>

<asp:ImageButton ID="imgbtnAdd" runat="server" ImageUrl="~/Images/AddNewitem.jpg" CommandName="AddNew" Width="30px" Height="30px" ToolTip="Add new User" ValidationGroup="validaiton" />

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="UserName">

<asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>'/>

</EditItemTemplate>

<asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("UserName") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrusrname" runat="server"/>

<asp:RequiredFieldValidator ID="rfvusername" runat="server" ControlToValidate="txtftrusrname" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="City">

<asp:TextBox ID="txtcity" runat="server" Text='<%#Eval("City") %>'/>

</EditItemTemplate>

<asp:Label ID="lblcity" runat="server" Text='<%#Eval("City") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrcity" runat="server"/>

<asp:RequiredFieldValidator ID="rfvcity" runat="server" ControlToValidate="txtftrcity" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Designation">

<asp:TextBox ID="txtDesg" runat="server" Text='<%#Eval("Designation") %>'/>

</EditItemTemplate>

<asp:Label ID="lblDesg" runat="server" Text='<%#Eval("Designation") %>'/>

</ItemTemplate>

<asp:TextBox ID="txtftrDesignation" runat="server"/>

<asp:RequiredFieldValidator ID="rfvdesignation" runat="server" ControlToValidate="txtftrDesignation" Text="*" ValidationGroup="validaiton"/>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

<div>

<asp:Label ID="lblresult" runat="server"></asp:Label>

</div>

</form>

</body>

</html>

Now add the following namespaces in codebehind

using System;

using System.Data;

using System.Data.SqlClient;

using System.Drawing

After that write the following code

SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB");

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindEmployeeDetails();

}

protected void BindEmployeeDetails()

{

con.Open();

SqlCommand cmd = new SqlCommand("Select * from Employee_Details", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

if (ds.Tables[0].Rows.Count > 0)

{

gvDetails.DataSource = ds;

gvDetails.DataBind();

}

else

{

ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());

gvDetails.DataSource = ds;

gvDetails.DataBind();

int columncount = gvDetails.Rows[0].Cells.Count;

gvDetails.Rows[0].Cells.Clear();

gvDetails.Rows[0].Cells.Add(new TableCell());

gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;

gvDetails.Rows[0].Cells[0].Text = "No Records Found";

}

protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)

{

gvDetails.EditIndex = e.NewEditIndex;

BindEmployeeDetails();

}

protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());

string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();

TextBox txtcity = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtcity");

TextBox txtDesignation = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtDesg");

con.Open();

SqlCommand cmd = new SqlCommand("update Employee_Details set City='" + txtcity.Text + "',Designation='" + txtDesignation.Text + "' where UserId=" + userid, con);

cmd.ExecuteNonQuery();

con.Close();

lblresult.ForeColor = Color.Green;

lblresult.Text = username + " Details Updated successfully";

gvDetails.EditIndex = -1;

BindEmployeeDetails();

}

protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

gvDetails.EditIndex = -1;

BindEmployeeDetails();

}

protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["UserId"].ToString());

string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();

con.Open();

SqlCommand cmd = new SqlCommand("delete from Employee_Details where UserId=" + userid, con);

int result = cmd.ExecuteNonQuery();

con.Close();

if (result == 1)

{

BindEmployeeDetails();

lblresult.ForeColor = Color.Red;

lblresult.Text = username + " details deleted successfully";

}

protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)

{

if(e.CommandName.Equals("AddNew"))

{

TextBox txtUsrname = (TextBox)gvDetails.FooterRow.FindControl("txtftrusrname");

TextBox txtCity = (TextBox)gvDetails.FooterRow.FindControl("txtftrcity");

TextBox txtDesgnation = (TextBox) gvDetails.FooterRow.FindControl("txtftrDesignation");

con.Open();

SqlCommand cmd =

new SqlCommand(

"insert into Employee_Details(UserName,City,Designation) values('" + txtUsrname.Text + "','" +

txtCity.Text + "','" + txtDesgnation.Text + "')", con);

int result= cmd.ExecuteNonQuery();

con.Close();

if(result==1)

{

BindEmployeeDetails();

lblresult.ForeColor = Color.Green;

lblresult.Text = txtUsrname.Text + " Details inserted successfully";

}

else

{

lblresult.ForeColor = Color.Red;

lblresult.Text = txtUsrname.Text + " Details not inserted";

}

Demo

Download sample code attached

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS

Subscribe by Email

250 comments :

«Oldest ‹Older 201 – 250 of 250 Newer› Newest»

sesha said...: thank a lot; January 23, 2014 at 9:33 AM
Anonymous said...: how to set identity property for user id.. please give me a syntax..; February 1, 2014 at 7:59 AM
Unknown said...: sir, i followed above code(. i have file error like(File '~/Images/delete.jpg' was not found,File '~/Images/Cancel.jpg' was not found,File '~/Images/AddNewitem.jpg' was not found). what can i do. please help me.; February 4, 2014 at 6:08 AM
Unknown said...: Sir ,
I have some errors in gridview concepts. like errors of(Files ~/Images/delete.jpg is not found also like some erors). what can i do . please help me.; February 4, 2014 at 6:23 AM
Unknown said...: protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int Code = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
string Prccode = gvDetails.DataKeys[e.RowIndex].Values["PRCCODE"].ToString();
TextBox txtPRCCODE = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrPRCCODE");
TextBox txtscalecode = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrscalecode");
TextBox txtbasic1 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic1");
TextBox txtinc1 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrinc1");
TextBox txtbasic2 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic2");
TextBox txtinc2 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrinc2");
.
.
.
TextBox txtinc15 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrinc15");
TextBox txtbasic16 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic16");
TextBox txtinc16 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrinc16");
TextBox txtbasic17 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic17");

con.Open();
SqlCommand cmd = new SqlCommand("update SCALE_MASTAR set PRCCODE='" + txtPRCCODE.Text + "',scalecode='" + txtscalecode.Text + "',basic1='" + txtbasic1.Text + "',inc1='" + txtinc1.Text + "',basic2='" + txtbasic2.Text + "',inc2='" + txtinc2.Text + "',basic3='" + txtbasic3.Text + "',inc3='" + txtinc3.Text + "',basic4='" + txtbasic4.Text + "',inc4='" + txtinc4.Text + "',basic5='" + txtbasic5.Text + "',inc5='" + txtinc5.Text + "',basic6='" + txtbasic6.Text + "',inc6='" + txtinc6.Text + "',basic7='" + txtbasic7.Text + "',inc7='" + txtinc7.Text + "',basic8='" + txtbasic8.Text + "',inc8='" + txtinc8.Text + "',basic9='" + txtbasic9.Text + "',inc9='" + txtinc9.Text + "',basic10='" + txtbasic10.Text + "',inc10='" + txtinc10.Text + "',basic11='" + txtbasic11.Text + "',inc11='" + txtinc11.Text + "',basic12='" + txtbasic12.Text + "',inc12='" + txtinc12.Text + "',basic13='" + txtbasic13.Text + "',inc13='" + txtinc13.Text + "',basic14='" + txtbasic14.Text + "',inc14='" + txtinc14.Text + "',basic15='" + txtbasic15.Text + "',inc15='" + txtinc15.Text + "',basic16='" + txtbasic16.Text + "',inc16='" + txtinc16.Text + "',basic17='" + txtbasic17.Text + "' where PRCCODE=" + Code, con);
con.Close();
lblresult.Text = Prccode + "Records Updated successfully";
gvDetails.EditIndex = -1;
scaler();
}; February 6, 2014 at 2:15 AM
Unknown said...: protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int Code = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
string Prccode = gvDetails.DataKeys[e.RowIndex].Values["PRCCODE"].ToString();
TextBox txtPRCCODE = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrPRCCODE");
TextBox txtscalecode = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrscalecode");
TextBox txtbasic1 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic1");
TextBox txtinc1 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrinc1");
TextBox txtbasic2 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic2");
TextBox txtinc2 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrinc2");
...........
............
...............
TextBox txtbasic16 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic16");
TextBox txtinc16 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrinc16");
TextBox txtbasic17 = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtftrbasic17");

con.Open();
//SqlCommand cmd = new SqlCommand("update SCALE_MASTAR set PRCCODE='" + txtPRCCODE.Text + "',scalecode='" + txtscalecode.Text + "',basic1='" + txtbasic1.Text + "',inc1='" + txtinc1.Text + "',basic2='" + txtbasic2.Text + "',inc2='" + txtinc2.Text + "',basic3='" + txtbasic3.Text + "',inc3='" + txtinc3.Text + "',basic4='" + txtbasic4.Text + "',inc4='" + txtinc4.Text + "',basic5='" + txtbasic5.Text + "',inc5='" + txtinc5.Text + "',basic6='" + txtbasic6.Text + "',inc6='" + txtinc6.Text + "',basic7='" + txtbasic7.Text + "',inc7='" + txtinc7.Text + "',basic8='" + txtbasic8.Text + "',inc8='" + txtinc8.Text + "',basic9='" + txtbasic9.Text + "',inc9='" + txtinc9.Text + "',basic10='" + txtbasic10.Text + "',inc10='" + txtinc10.Text + "',basic11='" + txtbasic11.Text + "',inc11='" + txtinc11.Text + "',basic12='" + txtbasic12.Text + "',inc12='" + txtinc12.Text + "',basic13='" + txtbasic13.Text + "',inc13='" + txtinc13.Text + "',basic14='" + txtbasic14.Text + "',inc14='" + txtinc14.Text + "',basic15='" + txtbasic15.Text + "',inc15='" + txtinc15.Text + "',basic16='" + txtbasic16.Text + "',inc16='" + txtinc16.Text + "',basic17='" + txtbasic17.Text + "' where PRCCODE=" + Code, con);
SqlCommand cmd = new SqlCommand("update SCALE_MASTAR set (PRCCODE ='"+txtPRCCODE.Text+"',scalecode='"+txtscalecode.Text+"',basic1='"+txtbasic1.Text+"') where PRCCODE=12,con);
cmd.ExecuteNonQuery();
con.Close();
lblresult.Text = Prccode + "Records Updated successfully";
gvDetails.EditIndex = -1;
scaler();
}; February 6, 2014 at 2:17 AM
Unknown said...: Hi... sir this sreeyogi

sir this above code was not working properly please help me to solve out the problem; February 6, 2014 at 2:20 AM
Sameer said...: Hello
I am using a gridview to update data in sql Table and its running fine but I want to apply more security as anyone can update data of other users.how can I do this.
Please Help; April 28, 2014 at 6:22 AM
Unknown said...: Hai thanks...because of u i got a job...; May 8, 2014 at 5:53 AM
Unknown said...: hellow sir,

i used above code but there is problem in update query there is an error to set an object reference so can can u help to solve that problem; May 10, 2014 at 12:36 AM
Unknown said...: sir how can generete dynamic menu in asp.net.please mahesh; May 12, 2014 at 9:17 PM
Bhavesh Bhuva said...: Thank You...; May 23, 2014 at 8:44 PM
Bhavesh Bhuva said...: Thank you.......; May 23, 2014 at 8:46 PM
Bhavesh Bhuva said...: hi nice examples; May 23, 2014 at 8:50 PM
Anonymous said...: how are proof ? this is language java and C# .Net which are pure Oops Concept; May 30, 2014 at 4:30 AM
Unknown said...: thank u....it helped alot.......thanks..man!!!; June 13, 2014 at 3:26 AM
Prashant Srivastava said...: i have a problem in my project for bill in grid view if you can help me please mail me on shine1509@gmail.com; June 13, 2014 at 5:49 AM
beginner said...: Object reference not set to an instance of an object.
check this error please; June 18, 2014 at 9:33 PM
Unknown said...: hi,
i have a question, when i edit the row in the database the values should go into the text box and after editing the by clicking the save button the row should be changed can u give me the c # code for this.
thanks in advance; June 23, 2014 at 12:37 AM
Anonymous said...: can you do this in vb.net also as you do earlier; July 3, 2014 at 11:32 AM
Anonymous said...: Thank you men !!!; July 15, 2014 at 1:18 PM
Anonymous said...: how to pass textbox values to crystal report directly pass values in asp.net c#?; July 21, 2014 at 12:17 AM
Anonymous said...: i have a problm in my code

public partial class _Default : System.Web.UI.Page
{
private SqlConnection con;
public _Default()
{
con = new SqlConnection("server=.;database=ebl;integrated security=true");
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmployeeDetails();
}

}
protected void BindEmployeeDetails()
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * from emp", con);
SqlDataReader rd = cmd.ExecuteReader();

gvDetails.DataSource = rd;
gvDetails.DataBind();

}

protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindEmployeeDetails();
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["txtid"].ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["name"].ToString();
TextBox txtsalary = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("salary");
TextBox txtcity = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("city");
con.Open();
SqlCommand cmd = new SqlCommand("update emp set name='" + username + "' City='" + txtcity.Text + "',salary='" + Convert.ToInt32(txtsalary.Text) + "' where id=" + userid + "", con);
cmd.ExecuteNonQuery();
con.Close();
lblresult.ForeColor = Color.Green;
lblresult.Text = username + " Details Updated successfully";
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindEmployeeDetails();
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int userid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["txtid"].ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["Name"].ToString();
con.Open();
SqlCommand cmd = new SqlCommand("delete from emp where Id=" + userid, con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Red;
lblresult.Text = username + " details deleted successfully";
}
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{

TextBox txtftrid = (TextBox)gvDetails.FooterRow.FindControl("txtftrid");
TextBox txtftrusrname = (TextBox)gvDetails.FooterRow.FindControl("txtftrusrname");
TextBox txtftrsalry = (TextBox)gvDetails.FooterRow.FindControl("txtftrsalry");
TextBox txtftrcity = (TextBox)gvDetails.FooterRow.FindControl("txtftrcity");
con.Open();
SqlCommand cmd = new SqlCommand("insert into emp (id,name,salary,city) values('" + Convert.ToInt32(txtftrid.Text) + "'," + txtftrusrname.Text + ",'" + Convert.ToInt32(txtftrsalry.Text) + "'," + txtftrcity + ")", con);
int result = cmd.ExecuteNonQuery();
con.Close();
if (result == 1)
{
BindEmployeeDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = txtftrusrname.Text + " Details inserted successfully";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = txtftrusrname.Text + " Details not inserted";
}
}
}

g properly
}; October 7, 2014 at 3:07 AM
Anonymous said...: my above code is not working properly pls help me; October 7, 2014 at 3:09 AM
RAJESH KUMAR SAHOO said...: Dear Suresh Plz Help me.
I am facing problem in update or delete button,it shows an error Only insert can work.
Mali-rajesh.sahoo14@gmail.com; August 2, 2015 at 11:01 PM
RAJESH KUMAR SAHOO said...: Dear Suresh
Whenever i create the database ,i got a error like that incorrect syntax near the keyword 'set'
create table Employee_Details
(
UserId Int(set identity property=true),
Username varchar(100),
City varchar(50),
Designation varchar(100)
)
Please solve this and mail me -rajesh.sahoo14@gmail.com; August 2, 2015 at 11:10 PM
Technologies for Web Development said...: in your program in the row updating coding what is the "txtcity"? where can set that property in gridview..; October 16, 2015 at 8:59 PM
Technologies for Web Development said...: in your program in the row updating coding what is the "txtcity"? where can set that property in gridview..; October 16, 2015 at 8:59 PM
Radhika said...: Thanx for your code...it helped..!!!; November 5, 2015 at 12:57 AM
Chetan_Winter said...: hi..; November 28, 2015 at 11:29 PM
Chetan_Winter said...: if (FileUpload1.HasFile)
{
string Extension = Path.GetExtension(FileUpload1.FileName);
if (Extension.ToLower() != ".gif" && Extension.ToLower() != ".png" && Extension.ToLower() != ".jpg" && Extension.ToLower() != ".jpeg")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Invalid image format');", true);
}
int filesize = FileUpload1.PostedFile.ContentLength;
if (filesize > 1048576)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Maximum file size 1mb');", true);
}
else
{
string Pathname = "uploadimages/" + Path.GetFileName(FileUpload1.PostedFile.FileName);
//string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
//SqlCommand cmd = new SqlCommand("insert into tbl_images(image_Name,image_path)values('" + filename + "','" + Pathname + "')", con);
if (RadioButtonList1.Text == "K1" && CheckBoxList1.Text=="R1")
{
con.Open();

SqlCommand cmd = new SqlCommand("insert into book_details(Book_Name,Author,Published_Date,Type,Cover_Picture,Kind,Rating)values(@Book_Name,@Author,@Published_Date,@Type,@Cover_Picture,@Kind,@Rating)", con);
cmd.Parameters.AddWithValue("@Book_Name", TextBox2.Text);
cmd.Parameters.AddWithValue("@Author", TextBox1.Text);
cmd.Parameters.AddWithValue("@Published_Date", txtdate.Text);
cmd.Parameters.AddWithValue("@Type", DropDownList1.SelectedItem.ToString());
//cmd.Parameters.AddWithValue("@Rating", txtCity.Text);
cmd.Parameters.AddWithValue("@Cover_Picture", Pathname);
cmd.Parameters.AddWithValue("@Kind", RadioButtonList1.Text);
cmd.Parameters.AddWithValue("@Rating", CheckBoxList1.Text);
int result = cmd.ExecuteNonQuery();
foreach (ListItem item in RadioButtonList1.Items)
{
item.Selected = false;
}
foreach (ListItem item in CheckBoxList1.Items)
{
item.Selected = false;
}
con.Close();

FileUpload1.SaveAs(Server.MapPath("~/uploadimages/" + FileUpload1.FileName));
if (result == 1)
{
BindBookDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = TextBox2.Text + "record inserted";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = TextBox2.Text + "record not inserted";
}
}; November 28, 2015 at 11:38 PM
Chetan_Winter said...: if (FileUpload1.HasFile)
{
string Extension = Path.GetExtension(FileUpload1.FileName);
if (Extension.ToLower() != ".gif" && Extension.ToLower() != ".png" && Extension.ToLower() != ".jpg" && Extension.ToLower() != ".jpeg")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Invalid image format');", true);
}
int filesize = FileUpload1.PostedFile.ContentLength;
if (filesize > 1048576)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Maximum file size 1mb');", true);
}
else
{
string Pathname = "uploadimages/" + Path.GetFileName(FileUpload1.PostedFile.FileName);
//string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
//SqlCommand cmd = new SqlCommand("insert into tbl_images(image_Name,image_path)values('" + filename + "','" + Pathname + "')", con);
if (RadioButtonList1.Text == "K1" && CheckBoxList1.Text=="R1")
{
con.Open();

SqlCommand cmd = new SqlCommand("insert into book_details(Book_Name,Author,Published_Date,Type,Cover_Picture,Kind,Rating)values(@Book_Name,@Author,@Published_Date,@Type,@Cover_Picture,@Kind,@Rating)", con);
cmd.Parameters.AddWithValue("@Book_Name", TextBox2.Text);
cmd.Parameters.AddWithValue("@Author", TextBox1.Text);
cmd.Parameters.AddWithValue("@Published_Date", txtdate.Text);
cmd.Parameters.AddWithValue("@Type", DropDownList1.SelectedItem.ToString());
//cmd.Parameters.AddWithValue("@Rating", txtCity.Text);
cmd.Parameters.AddWithValue("@Cover_Picture", Pathname);
cmd.Parameters.AddWithValue("@Kind", RadioButtonList1.Text);
cmd.Parameters.AddWithValue("@Rating", CheckBoxList1.Text);
int result = cmd.ExecuteNonQuery();
foreach (ListItem item in RadioButtonList1.Items)
{
item.Selected = false;
}
foreach (ListItem item in CheckBoxList1.Items)
{
item.Selected = false;
}
con.Close();

FileUpload1.SaveAs(Server.MapPath("~/uploadimages/" + FileUpload1.FileName));
if (result == 1)
{
BindBookDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = TextBox2.Text + "record inserted";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = TextBox2.Text + "record not inserted";
}
}; November 28, 2015 at 11:38 PM
Chetan_Winter said...: else if (RadioButtonList1.Text == "K2" && CheckBoxList1.Text == "R2")
{
con.Open();

SqlCommand cmd = new SqlCommand("insert into book_details(Book_Name,Author,Published_Date,Type,Cover_Picture,Kind,Rating)values(@Book_Name,@Author,@Published_Date,@Type,@Cover_Picture,@Kind,@Rating)", con);
cmd.Parameters.AddWithValue("@Book_Name", TextBox2.Text);
cmd.Parameters.AddWithValue("@Author", TextBox1.Text);
cmd.Parameters.AddWithValue("@Published_Date", txtdate.Text);
cmd.Parameters.AddWithValue("@Type", DropDownList1.SelectedItem.ToString());
//cmd.Parameters.AddWithValue("@Rating", txtCity.Text);
cmd.Parameters.AddWithValue("@Cover_Picture", Pathname);
cmd.Parameters.AddWithValue("@Kind", RadioButtonList1.Text);
cmd.Parameters.AddWithValue("@Rating", CheckBoxList1.Text);
int result = cmd.ExecuteNonQuery();
foreach (ListItem item in RadioButtonList1.Items)
{
item.Selected = false;
}
foreach (ListItem item in CheckBoxList1.Items)
{
item.Selected = false;
}
con.Close();

FileUpload1.SaveAs(Server.MapPath("~/uploadimages/" + FileUpload1.FileName));
if (result == 1)
{
BindBookDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = TextBox2.Text + "record inserted";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = TextBox2.Text + "record not inserted";
}
}; November 28, 2015 at 11:47 PM
Chetan_Winter said...: else
{
con.Open();

SqlCommand cmd = new SqlCommand("insert into book_details(Book_Name,Author,Published_Date,Type,Cover_Picture,Kind,Rating)values(@Book_Name,@Author,@Published_Date,@Type,@Cover_Picture,@Kind,@Rating)", con);
cmd.Parameters.AddWithValue("@Book_Name", TextBox2.Text);
cmd.Parameters.AddWithValue("@Author", TextBox1.Text);
cmd.Parameters.AddWithValue("@Published_Date", txtdate.Text);
cmd.Parameters.AddWithValue("@Type", DropDownList1.SelectedItem.ToString());
//cmd.Parameters.AddWithValue("@Rating", txtCity.Text);
cmd.Parameters.AddWithValue("@Cover_Picture", Pathname);
cmd.Parameters.AddWithValue("@Kind", RadioButtonList1.Text);
cmd.Parameters.AddWithValue("@Rating", CheckBoxList1.Text);
int result = cmd.ExecuteNonQuery();
foreach (ListItem item in RadioButtonList1.Items)
{
item.Selected = false;
}
foreach (ListItem item in CheckBoxList1.Items)
{
item.Selected = false;
}
con.Close();

FileUpload1.SaveAs(Server.MapPath("~/uploadimages/" + FileUpload1.FileName));
if (result == 1)
{
BindBookDetails();
lblresult.ForeColor = Color.Green;
lblresult.Text = TextBox2.Text + "record inserted";
}
else
{
lblresult.ForeColor = Color.Red;
lblresult.Text = TextBox2.Text + "record not inserted";
}
}

}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('please select a file to upload');", true);
}
clear();; November 28, 2015 at 11:52 PM
Chetan_Winter said...: hey guys..try the above code for insertion...; November 28, 2015 at 11:56 PM
Unknown said...: hello sir getting an exceptional error while delete or update any row "input string was not in correct format" in line string username = gvDetails.DataKeys[e.RowIndex].Values["UserName"].ToString();

is it because of i didnt declare any primary key in table and all defined allow null?

please specify !!; December 8, 2015 at 11:47 AM
Chetan_Winter said...: try this..
string username=Convert.ToString(gvDetails.DataKeys[e.RowIndex].Values["UserName"]);; December 12, 2015 at 8:22 PM
Unknown said...: from the multiple textboxes(name, city, state) i like to search the data from the same row...
in my code it gives the data from the other rows as well in the gridview..
please help; February 20, 2016 at 4:01 AM
Unknown said...: from the multiple textboxes(name, city, state) i like to search the data from the same row...
in my code it gives the data from the other rows as well in the gridview..
please help; February 20, 2016 at 4:01 AM
Unknown said...: Hello sir,really its very easy to learn through your code Thank you; April 6, 2016 at 3:42 AM
Unknown said...: Sir this tutorial is very helpful.Sir can you help me to create database for online exam.I need questionbank database.Please help me; March 4, 2017 at 10:13 PM
Unknown said...: Sir this tutorial is very helpful for me. Can I have questionbank database for online exam; March 4, 2017 at 10:16 PM
Anonymous said...: using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

///
/// Summary description for clsDataLayer
///
public class clsDataLayer
{
private string conn = ConfigurationManager.ConnectionStrings["mythreetier"].ToString();
public void InsertUpdateDeleteSQLString(string sqlstring)
{
SqlConnection objsqlconn = new SqlConnection(conn);
objsqlconn.Open();
SqlCommand objcmd = new SqlCommand(sqlstring, objsqlconn);
objcmd.ExecuteNonQuery();
}

public object ExecuteSqlString(string sqlstring)
{
SqlConnection objsqlconn = new SqlConnection(conn);
objsqlconn.Open();
DataSet ds = new DataSet();
SqlCommand objcmd = new SqlCommand(sqlstring, objsqlconn);
SqlDataAdapter objAdp = new SqlDataAdapter(objcmd);
objAdp.Fill(ds);
return ds;
}

//public void AddNewCustomerDB(string empname, string empdesig, string empdept, string empsalary)
//{

// DataSet ds = new DataSet();
// string sql = "INSERT into Employee (Empname,Empdesig,Deptid,Empsalary) VALUES ('" + empname + "','" + empdesig + "','" + empdept + "','" + empsalary + "')";
// InsertUpdateDeleteSQLString(sql);

//}

public void AddNewCustomerDB(UserBO ObjBO)
{

DataSet ds = new DataSet();
string sql = "INSERT into Employee (Empname,Empdesig,Deptid,Empsalary) VALUES ('" + ObjBO.empname + "','" + ObjBO.empdesig + "','" + ObjBO.empdept + "','" + ObjBO.empsalary + "')";
InsertUpdateDeleteSQLString(sql);

}

//public void UpdateCustomerDB(int custid, string custname, string custaddr, string custcountry, string custcity, string custincode)
//{
// DataSet ds = new DataSet();
// string sql = "Update Customer set customer_name='" + custname + "',customer_address = '" + custaddr + "',customer_country= '" + custcountry + "',customer_city = '" + custcity + "',customer_pincode = '" + custincode + "' Where customer_id = '" + custid + "'";
// InsertUpdateDeleteSQLString(sql);
//}

public void UpdateCustomerDB(UserBO ObjBO)
{
DataSet ds = new DataSet();
string sql = "Update Employee set Empname='" + ObjBO.empname + "',Empdesig = '" + ObjBO.empdesig + "',Deptid= '" + ObjBO.empdept + "',Empsalary = '" + ObjBO.empsalary + "' Where Empid = '" + ObjBO.Empid + "'";
InsertUpdateDeleteSQLString(sql);
}

public void DeleteCustomerDB(UserBO ObjBO)
{
DataSet ds = new DataSet();
string sql = "Delete From Employee Where Empid = '" + ObjBO.Empid + "' ";
InsertUpdateDeleteSQLString(sql);
}

public object LoadCustomerDB()
{
DataSet ds = new DataSet();
string sql = "SELECT * from Employee e inner join Department d on e.Deptid=d.Deptid";
ds = (DataSet)ExecuteSqlString(sql);
return ds;
}
}; August 3, 2017 at 11:35 PM
Anonymous said...: using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

///
/// Summary description for clsBusinessLayer
///
public class clsBusinessLayer
{
public clsDataLayer objDataLayer = new clsDataLayer();
public object LoadCustomer()
{
return objDataLayer.LoadCustomerDB();
}
//public void AddNewCustomer(string empname, string empdesig, string empdept, string empsalary)
//{
// objDataLayer.AddNewCustomerDB(empname, empdesig, empdept, empsalary);

//}
public void AddNewCustomer(UserBO ObjBO)
{
objDataLayer.AddNewCustomerDB(ObjBO);

}

//public void UpdateCustomer(int custid, string custname, string custaddr, string custcountry, string custcity, string custincode)
//{
// objDataLayer.UpdateCustomerDB(custid, custname, custaddr, custcountry, custcity, custincode);
//}

public void UpdateCustomer(UserBO ObjBO)
{

objDataLayer.UpdateCustomerDB(ObjBO);
}

public void DeleteCustomer(UserBO ObjBO)
{
objDataLayer.DeleteCustomerDB(ObjBO);
}
}; August 3, 2017 at 11:49 PM
Anonymous said...: protected void Button1_Click(object sender, EventArgs e)
{
//Add
bo.empname = TextBox1.Text;
bo.empdesig = ddldesig.SelectedValue;
bo.empdept = ddlemployee.SelectedValue;
bo.empsalary = TextBox4.Text;
if (HiddenField1.Value == "")
{
obj.AddNewCustomer(bo);
}
//else
// {
// obj.UpdateCustomer(Convert.ToInt16(HiddenField1.Value), TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text);
// }
ClearAll();
Response.Redirect("Default.aspx");

}
protected void Button2_Click(object sender, EventArgs e)
{
//Search
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mythreetier"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select * from Employee E inner join Department D on E.Deptid=D.Deptid where Empname like '" + TextBox5.Text + "' or EmpSalary >'" + TextBox5.Text + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}; August 3, 2017 at 11:53 PM
Anonymous said...: protected void BindDepartmentdropdown()
{
//conenction path for database
SqlConnection con = new SqlConnection(@"Data Source=MAYANK-PC\MANKU;Initial Catalog=Career;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("select Deptid,Deptname from Department", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
ddlemployee.DataSource = ds;
ddlemployee.DataTextField = "Deptname";
ddlemployee.DataValueField = "Deptid";
ddlemployee.DataBind();
ddlemployee.Items.Insert(0, new ListItem("--Select--", "0"));

}

protected void BindDesignationdropdown()
{
SqlConnection con = new SqlConnection(@"Data Source=MAYANK-PC\MANKU;Initial Catalog=Career;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("select distinct Empdesig from Employee", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
ddldesig.DataSource = ds;
ddldesig.DataTextField = "Empdesig";
//ddldesig.DataValueField = "Empid";
ddldesig.DataBind();
ddldesig.Items.Insert(0, new ListItem("--Select--", "0"));
}

[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List GetEmployeeNames(string prefixText)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mythreetier"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select * from Employee where Empname like @Name+'%'", con);
cmd.Parameters.AddWithValue("@Name", prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List EmployeeNames = new List();
for (int i = 0; i < dt.Rows.Count; i++)
{
EmployeeNames.Add(dt.Rows[i][1].ToString());
}
return EmployeeNames;
}; August 3, 2017 at 11:56 PM
Anonymous said...: protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
obj = new clsBusinessLayer();
Label custid = GridView1.Rows[e.RowIndex].FindControl("Label1") as Label;
//int index = Convert.ToInt32(e.RowIndex);
//int custid = Convert.ToInt16(gvcustomerdetails.Rows[index].Cells[2].Text);
//int custid = Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Values["customer_id"].ToString());
bo.Empid = Convert.ToInt16(custid.Text);
obj.DeleteCustomer(bo);
GridView1.DataSource = obj.LoadCustomer();
GridView1.DataBind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
Label custid = GridView1.Rows[e.RowIndex].FindControl("Label1") as Label;//working
TextBox txtname_ = GridView1.Rows[e.RowIndex].FindControl("TextBox2") as TextBox;
DropDownList ddldesig = GridView1.Rows[e.RowIndex].FindControl("ddldesigg") as DropDownList;
DropDownList ddldept = GridView1.Rows[e.RowIndex].FindControl("ddldept") as DropDownList;
TextBox txtsalary = GridView1.Rows[e.RowIndex].FindControl("TextBox5") as TextBox;
//TextBox txtpincode = GridView1.Rows[e.RowIndex].FindControl("txtpin") as TextBox;
//string name = txtname.Text;
//int custid = Convert.ToInt16(gvcustomerdetails.Rows[e.RowIndex].FindControl("txtid") as TextBox);
bo.Empid = Convert.ToInt16(custid.Text);
bo.empname = txtname_.Text;
bo.empdesig = ddldesig.SelectedValue;
bo.empdept = ddldept.SelectedValue;
bo.empsalary = txtsalary.Text;
obj.UpdateCustomer(bo);
GridView1.EditIndex = -1;
GridView1.DataSource = obj.LoadCustomer();
GridView1.DataBind();
}; August 4, 2017 at 12:00 AM
Anonymous said...: protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GridView1.DataBind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
//GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if ((e.Row.RowState & DataControlRowState.Edit) > 0)
{
DropDownList ddldept = (DropDownList)e.Row.FindControl("ddldept");
DataTable dt;
String SQL = "SELECT * FROM Department";

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mythreetier"].ToString());
// using (SqlConnection con = new SqlConnection(sConstr))
//{
SqlCommand comm = new SqlCommand(SQL, con);

con.Open();
SqlDataAdapter da = new SqlDataAdapter(comm);

dt = new DataTable("tbl");
da.Fill(dt);
ddldept.DataSource = dt;
ddldept.DataTextField = "Deptname";
ddldept.DataValueField = "Deptid";
ddldept.DataBind();
ddldept.SelectedValue = ((DataRowView)e.Row.DataItem)["Deptid"].ToString();

//Employee
DropDownList ddldesig = (DropDownList)e.Row.FindControl("ddldesigg");
DataTable dt1;
String SQL1 = "SELECT * FROM Employee";

SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["mythreetier"].ToString());
// using (SqlConnection con = new SqlConnection(sConstr))
//{
SqlCommand comm1 = new SqlCommand(SQL1, con1);

con1.Open();
SqlDataAdapter da1 = new SqlDataAdapter(comm1);

dt1 = new DataTable("tbl");
da1.Fill(dt1);
ddldesig.DataSource = dt1;
ddldesig.DataTextField = "Empdesig";
//ddldesig.DataValueField = "Deptid";
ddldesig.DataBind();
ddldesig.SelectedValue = ((DataRowView)e.Row.DataItem)["Empdesig"].ToString();

}
}

}; August 4, 2017 at 12:02 AM
Anonymous said...: public partial class _Default : System.Web.UI.Page
{
clsBusinessLayer obj;
UserBO bo = new UserBO();
protected void Page_Load(object sender, EventArgs e)
{

obj = new clsBusinessLayer();
// if (!IsPostBack)
// {
GridView1.DataSource = obj.LoadCustomer();
GridView1.DataBind();
if (!IsPostBack)
{
BindDepartmentdropdown();
BindDesignationdropdown();
}
}

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static List GetCountryName(string pre)
{
List allCompanyName = new List();
using (CareerEntities dc = new CareerEntities())
{
allCompanyName = (from a in dc.Employees where a.Empname.StartsWith(pre) select a.Empname).ToList();
}
return allCompanyName;
}; September 11, 2017 at 3:56 AM
Anonymous said...: ...; May 30, 2018 at 3:00 AM