Insert, update, delete data in gridview with validations controls in ASP.NET with C#- Example

 Introduction:

In this post I will explain how to insert, update and delete data from gridview using asp.net.
I have requirement that user can insert data from gridview at that time it will check for validations if data is valid than it is inserted otherwise not. There are two linkbuttons or imagebuttons in gridview from which user can edit and delete data. To provide this functionality I have use following methods of gridview.

1.OnRowEditing
2.OnRowCancelingEditing
3.OnRowDeleting
4.OnRowUpdating
5.OnRowCommand

OnRowEditing event is used to edit the selected row in gridview when this method is executed It will hide the all the template fields except EditeTemplete. This field contains the asp input controls like textbox or dropdownlist etc.

OnRowCancelingEditing event is use to hide the EditeTemplete and shows alternative templetefield

OnRowDeleting in this event I have written the code for deleting record

OnRowCommand event is raised when a button is clicked in the Gridview control. This enables you to provide an event handling method that performs a custom routine whenever this event occurs.


Table In Database



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




Source Code:-


 <html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
    <style type="text/css">
        .Gridview
        {
            font-family: Verdana;
            font-size: 10pt;
            font-weight: normal;
            color: black;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <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">
            <Columns>
                <asp:TemplateField>
                    <EditItemTemplate>
                        <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>
                    <ItemTemplate>
                        <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>
                    <FooterTemplate>
                        <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">
                    <EditItemTemplate>
                        <asp:Label ID="lbleditusr" runat="server" Text='<%#Eval("Username") %>' />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblitemUsr" runat="server" Text='<%#Eval("UserName") %>' />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtftrusrname" runat="server" />
                        <asp:RequiredFieldValidator ID="rfvusername" runat="server" ControlToValidate="txtftrusrname"
                            Text="*" ValidationGroup="validaiton" />
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="City">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtcity" runat="server" Text='<%#Eval("City") %>' />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblcity" runat="server" Text='<%#Eval("City") %>' />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:TextBox ID="txtftrcity" runat="server" />
                        <asp:RequiredFieldValidator ID="rfvcity" runat="server" ControlToValidate="txtftrcity"
                            Text="*" ValidationGroup="validaiton" />
                    </FooterTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Designation">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtDesg" runat="server" Text='<%#Eval("Designation") %>' />
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblDesg" runat="server" Text='<%#Eval("Designation") %>' />
                    </ItemTemplate>
                    <FooterTemplate>
                        <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>




Code Behind in C#:-


SqlConnection con = new SqlConnection("Data Source=AbhayData;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";
            }
        }
    }

1 comments:

how to fire edit event by using grideview

Reply

Post a Comment