Thursday, August 25, 2011

Grid View Insert,Update,Deleted in Asp.net

Table

CREATE TABLE [dbo].[one](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Age] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Gender] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


Design Part

 <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <script type="text/javascript">
     function SelectAll(id)
        {
            //get reference of GridView control
            var grid = document.getElementById("<%= GrdDisplay.ClientID %>");
            //variable to contain the cell of the grid
            var cell;
          
            if (grid.rows.length > 0)
            {
                //loop starts from 1. rows[0] points to the header.
                for (i=1; i<grid.rows.length; i++)
                {
                    //get the reference of Fifth column
                    cell = grid.rows[i].cells[5];
                  
                    if(cell!=null)
                    {
                        //loop according to the number of childNodes in the cell
                        for (j=0; j<cell.childNodes.length; j++)
                        {         
                            //if childNode type is CheckBox               
                            if (cell.childNodes[j].type == "checkbox" )
                            {
                                //assign the status of the Select All checkbox to the cell checkbox within the grid
                                cell.childNodes[j].checked = document.getElementById(id).checked;
                            }
                        }
                    }
                }
            }
        }
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblError" runat="server" ForeColor="Red"></asp:Label>
        <asp:Panel ID="PnlGrid" runat="server">
            <asp:GridView ID="GrdDisplay" runat="server" OnRowCommand="GrdDisplay_RowCommand"
                OnRowDataBound="GrdDisplay_RowDataBound" OnRowDeleting="GrdDisplay_RowDeleting"
                OnRowEditing="GrdDisplay_RowEditing" AllowPaging="true" AutoGenerateColumns="false">
                <Columns>
                    <asp:TemplateField ItemStyle-Width="10%">
                        <HeaderTemplate>
                            S.No
                        </HeaderTemplate>
                        <ItemTemplate>
                            <%# Container.DataItemIndex + 1 %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ItemStyle-Width="45%">
                        <HeaderTemplate>
                            Name
                        </HeaderTemplate>
                        <ItemTemplate>
                            <%#Eval("Name") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ItemStyle-Width="6%">
                        <HeaderTemplate>
                            Age
                        </HeaderTemplate>
                        <ItemTemplate>
                            <%#Eval("Age") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ItemStyle-Width="5%">
                        <HeaderTemplate>
                            Gender
                        </HeaderTemplate>
                        <ItemTemplate>
                            <%#Eval("Gender")%>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ItemStyle-Width="20%">
                        <HeaderTemplate>
                            Action
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:LinkButton ID="lnkView" runat="server" Font-Underline="false" CommandArgument='<%#Bind("Id") %>'
                                CommandName="Edit" Text="Edit"> </asp:LinkButton>
                            <asp:LinkButton ID="LinkButton1" runat="server" Font-Underline="false" CommandArgument='<%#Bind("Id") %>'
                                CommandName="New" Text="New"> </asp:LinkButton>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ItemStyle-Width="5%">
                        <HeaderTemplate>
                            <asp:CheckBox ID="ChkHeader" runat="server" />
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:HiddenField ID="hdn_Id" runat="server" Value='<%#Bind("Id") %>'></asp:HiddenField>
                            <asp:CheckBox ID="ChkSelect" runat="server"></asp:CheckBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
            <asp:Button ID="BtnGDelete" runat="server" Text="Delete" OnClick="BtnGDelete_Click" />
            <asp:HiddenField ID="hdnId" runat="server" />
        </asp:Panel>
        <asp:Panel ID="PnlEdit" runat="server" Visible="false">
             Name:
                     <asp:TextBox ID="TxtName" runat="server"></asp:TextBox>
             Age:
                     <asp:TextBox ID="TxtAge" runat="server"></asp:TextBox>
              Gender  <asp:DropDownList ID="DrpGender" runat="server">                            <asp:ListItem>male</asp:ListItem>
                            <asp:ListItem>Female</asp:ListItem>
                        </asp:DropDownList>
                  
                        <asp:Button ID="BtnSave" runat="server" Text="Save" OnClick="BtnSave_Click" />
                        &nbsp;<asp:Button ID="BtnUpdate" runat="server" Text="Update" OnClick="BtnUpdate_Click" />
                        &nbsp;<asp:Button ID="BtnCancel" runat="server" Text="Cancel" OnClick="BtnCancel_Click" />                   
        </asp:Panel>
    </div>
    </form>
</body>
</html>


Code Part:
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["mycon1"]);

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            lblError.Text = "";
            this.BindGrid();
            BtnGDelete.Attributes.Add("onclick", "return confirm('Note:This will Clear all the data related to this User.Are you sure you want to delete?')");

        }
    }

    protected void BindGrid()
    {
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from One", con);
        SqlDataAdapter ada = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        ada.Fill(ds);
        GrdDisplay.DataSource = ds;
        GrdDisplay.DataBind();
        con.Close();
    }

    protected void FillUserDetails(string Id)
    {
        try
        {
            BtnSave.Visible = false;
            BtnUpdate.Visible = true;

            con.Open();
            SqlCommand cmd = new SqlCommand("Select * from One WHERE Id='" + Id + "'", con);
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                PnlEdit.Visible = true;
                PnlGrid.Visible = false;
                DataRow dr = ds.Tables[0].Rows[0];
                TxtName.Text = dr["Name"].ToString();
                TxtAge.Text = dr["Age"].ToString();
                DrpGender.SelectedValue = dr["Gender"].ToString();

            }
            con.Close();

        }
        catch (Exception)
        {
            throw;
        }
    }

    protected void Clear(Control ctl)
    {

        foreach (Control c in ctl.Controls)
        {
            if (c.GetType() == typeof(TextBox))
            {
                ((TextBox)(c)).Text = "";
            }
        }
    }

    protected void GrdDisplay_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        try
        {
            if (e.CommandName == "Edit")
            {
                hdnId.Value = e.CommandArgument.ToString();
                this.FillUserDetails(hdnId.Value);
            }
            if (e.CommandName == "New")
            {
                PnlEdit.Visible = true;
                PnlGrid.Visible = false;
                BtnUpdate.Visible = false;
                BtnSave.Visible = true;
                TxtAge.Text = "";
                TxtName.Text = "";
            }
        }
        catch (Exception)
        {

            throw;
        }

    }

    protected void GrdDisplay_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        try
        {
            if (e.Row.RowType == DataControlRowType.Header)
            {
                //Find the checkbox control in header and add an attribute

                ((CheckBox)e.Row.FindControl("chkHeader")).Attributes.Add("onclick", "javascript:SelectAll('" + ((CheckBox)e.Row.FindControl("chkHeader")).ClientID + "')");
            }
        }
        catch (Exception)
        {

            throw;
        }
    }

    protected void GrdDisplay_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        try
        {

        }
        catch (Exception)
        {

            throw;
        }
    }

    protected void GrdDisplay_RowEditing(object sender, GridViewEditEventArgs e)
    {
        try
        {

        }
        catch (Exception)
        {

            throw;
        }
    }

    protected void BtnSave_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid)
            {
                this.Clear(this.Page);
                con.Open();
                string Query = "insert into one(name,age,gender) values('" + TxtName.Text.Trim() + "','" + TxtAge.Text.Trim() + "','" + DrpGender.SelectedValue + "')";
                SqlCommand cmd = new SqlCommand(Query, con);
                bool Result = cmd.ExecuteNonQuery() > 0 ? true : false;
                con.Close();
                if (Result)
                {
                    this.Clear(this.Page);
                    PnlEdit.Visible = false;
                    PnlGrid.Visible = true;
                    this.BindGrid();
                    lblError.Text = "Inserted Successfully";
                }
                else
                {
                    PnlEdit.Visible = false;
                    PnlGrid.Visible = true;
                    lblError.Text = "Error has been Occured";
                }
            }
        }
        catch (Exception)
        {

            throw;
        }
    }

    protected void BtnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid)
            {
                con.Open();
                string Query = "Update  one SET name='" + TxtName.Text.Trim() + "',age='" + TxtAge.Text.Trim() + "',gender='" + DrpGender.SelectedValue + "' WHERE ID='" + hdnId.Value + "'";
                SqlCommand cmd = new SqlCommand(Query, con);
                bool Result = cmd.ExecuteNonQuery() > 0 ? true : false;
                con.Close();
                if (Result)
                {
                    this.Clear(this.Page);
                    PnlEdit.Visible = false;
                    PnlGrid.Visible = true;
                    this.BindGrid();
                    lblError.Text = "Updated Successfully";
                }
                else
                {
                    PnlEdit.Visible = false;
                    PnlGrid.Visible = true;
                    lblError.Text = "Error has been Occured";
                }
            }
        }
        catch (Exception)
        {

            throw;
        }

    }

    protected void BtnCancel_Click(object sender, EventArgs e)
    {
        PnlEdit.Visible = false;
        PnlGrid.Visible = true;
    }

    protected void BtnGDelete_Click(object sender, EventArgs e)
    {
        try
        {
            if (Page.IsValid)
            {
                bool Status = false;

                foreach (GridViewRow row in GrdDisplay.Rows)
                {

                    HiddenField hdn_Id = row.FindControl("hdn_Id") as HiddenField;
                    CheckBox chkselect = row.FindControl("ChkSelect") as CheckBox;
                    if (chkselect.Checked)
                    {
                        Status = true;
                        con.Open();
                        string Query = "DELETE FROM one  WHERE ID='" + hdn_Id.Value + "'";
                        SqlCommand cmd = new SqlCommand(Query, con);
                        bool Result = cmd.ExecuteNonQuery() > 0 ? true : false;
                        con.Close();
                        if (Result)
                        {
                            PnlEdit.Visible = false;
                            PnlGrid.Visible = true;
                            this.BindGrid();
                            lblError.Text = "Deleted Successfully";
                        }
                        else
                        {
                            PnlEdit.Visible = false;
                            PnlGrid.Visible = true;
                            lblError.Text = "Error has been Occured";
                        }
                    }

                }
                if (!Status)
                {
                    lblError.Text = "Please select the record to delete.";
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
    }

Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)