Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Delete row from GridView Control using ADO.NET

Hello Experts,

I need help with the following GridView Control below. I need to be able to delete users based on their ID in my case the ID is called "empid". I'm not sure how to do this using ADO.NET with the GridView Control.

HTML GridView Control:

<asp:GridView ID="gv_CompletedAnnualPhysicalReport" runat="server" AutoGenerateColumns="False" OnRowDataBound="gv_CompletedAnnualPhysicalReport_RowDataBound" CellPadding="4" ForeColor="#474747" Width="600px" BorderColor="#E3E3E3" GridLines="None">
                    <AlternatingRowStyle BackColor="White" ForeColor="#474747" BorderColor="#E3E3E3" />
                    <Columns>
                        <asp:BoundField DataField="emp_firstname">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
                        <asp:BoundField DataField="emp_lastname">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
                        <asp:BoundField DataField="emp_email">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
                        <asp:HyperLinkField DataNavigateUrlFields="ap_id"
                            DataNavigateUrlFormatString="ap_pdf.aspx?ap_id={0}" 
                            DataTextField="ap_id" 
                            DataTextFormatString="download" HeaderText="PDF" >
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:HyperLinkField>
                        <asp:BoundField DataField="ap_section_complete" HeaderText="COMPLETE" >
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle HorizontalAlign="Center" />
                        </asp:BoundField>
                    </Columns>
                    <EditRowStyle BackColor="#999999" BorderColor="#E3E3E3" />
                    <FooterStyle BackColor="#0091c1" ForeColor="White" />
                    <HeaderStyle BackColor="#0091c1" ForeColor="White" BorderColor="#E3E3E3" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="#333333" BorderColor="#E3E3E3" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" BorderColor="#E3E3E3" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>

Open in new window



GridView CodeBehind for binding data:

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net.Mail;
using System.Net.NetworkInformation;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;

public partial class administrator_annualphysical : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        CompletedAnnualPhysicalReport();
    }

    protected void CompletedAnnualPhysicalReport()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessTracker"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "RetrieveCompletedAnnualPhysicalReport";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        DataTable dtCompletedAnnualPhysicalReport = new DataTable("Modify");

        SqlDataAdapter adp = new SqlDataAdapter();

        try
        {
            conn.Open();

            adp.SelectCommand = cmd;
            adp.Fill(dtCompletedAnnualPhysicalReport);

            if ((dtCompletedAnnualPhysicalReport != null))
            {
                DataRow data = dtCompletedAnnualPhysicalReport.Rows[0];

                gv_CompletedAnnualPhysicalReport.DataSource = dtCompletedAnnualPhysicalReport;
                gv_CompletedAnnualPhysicalReport.DataBind();
            }
        }

        catch (Exception ex)
        {
            ex.Message.ToString();
        }

        finally
        {
            conn.Close();
        }
    }

    protected void gv_CompletedAnnualPhysicalReport_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataRowView rowView = (DataRowView)e.Row.DataItem;

            string ap = rowView["ap_section_complete"].ToString();

            if (ap == "1")
            {
                e.Row.Cells[4].CssClass = "greenbgcolor";
            }
            else if (ap == "")
            {
                e.Row.Cells[4].CssClass = "redbgcolor";
            }
        }
    }
}

Open in new window

0
asp_net2
Asked:
asp_net2
  • 5
  • 4
1 Solution
 
Rajar AhmedConsultantCommented:
Try this ,

Step1 : Aspx Page
1.Add DataKeyNames attribute in gridview 
2.Add OnRowDeleting attribute in gridview
3.Add New a button  with commandname ="delete" in one column
 

Open in new window


<asp:GridView ID="gv_CompletedAnnualPhysicalReport" runat="server" AutoGenerateColumns="False" DataKeyNames="ap_id"  OnRowDeleting="gvData_RowDeleting"/>
                    <Columns>
                        <asp:BoundField DataField="emp_firstname">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
......
....
<asp:TemplateField ShowHeader="False">
                    <ItemTemplate>
                        <asp:LinkButton ID="LinkButton1"
                        runat="server" CausesValidation="False"
                        CommandName="Delete"
                        OnClientClick='return confirm("Are you sure you want to delete this supplier?");'
                        Text="Delete" />
                    </ItemTemplate>
                </asp:TemplateField>

                </asp:GridView>

Open in new window



Step 2:Configuring RowDeleting event.
Row_deleting event will delete the row which has been clicked .

protected void gvData_RowDeleting(object sender, System.Web.UI.WebControls.GridViewDeleteEventArgs e)
{
	int rowIndex = gvData.Rows(e.RowIndex).RowIndex.ToString();
	int ap_id = Convert.ToInt32(gvData.DataKeys(rowIndex).Value);
	//Pass ap_id to your delete procedure   
}

Open in new window

0
 
asp_net2Author Commented:
Hi meeran03,

First of all that you for responding to this post. I was able to get your post working as needed except with one little issue. Is there a way to NOT show the Delete link if a certain value in my database does not exist?

Also I had to use the following code for the RowDeleting Event in or for this to work.

protected void gv_CompletedAnnualPhysicalReport_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessTracker"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "DeleteAnnualPhysicalReportByID";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        cmd.Parameters.Add("@ap_id", SqlDbType.Int).Value = Convert.ToInt32(gv_CompletedAnnualPhysicalReport.Rows[e.RowIndex].Cells[1].Text);

        try
        {
            conn.Open();

            cmd.ExecuteNonQuery();
        }

        catch (Exception ex)
        {
            ex.Message.ToString();
        }

        finally
        {
            conn.Close();
            CompletedAnnualPhysicalReport();
        }
    }

Open in new window

0
 
Rajar AhmedConsultantCommented:
"Not to show link button"
On your existing Rowdatabund,

 protected void gv_CompletedAnnualPhysicalReport_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataRowView rowView = (DataRowView)e.Row.DataItem;

            string ap = rowView["ap_section_complete"].ToString();

            if (ap == "1")
            {
                e.Row.Cells[4].CssClass = "greenbgcolor";
            }
            else if (ap == "")
            {
                e.Row.Cells[4].CssClass = "redbgcolor";
            }
           

            //If it meets your database condition to hide then 
            // Find delete button on your gridview Like below
            //For Eg: 
           string ap1 = rowView["DeleteCondtion"].ToString();
            if (ap1 =="HIDEDELETE"){
            LinkButton deleteBtn = (LinkButton)e.Row.FindControl("YourDeleteButtonID");
            deleteBtn.Visible = False;
             }
  

        }
    }

Open in new window


FYI:There may be some syntax error as i just used notepad for the above code

Meeran03
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
asp_net2Author Commented:
Hi meeran03,

Can you please explain that a little clearier to me :( I need help filling in the pieces below. Not sure what to put in for "DeleteCondition", "HIDEDELETE", and "YourDeleteButtonID".

The code that I have for the gv_CompletedAnnualPhysicalReport_RowDataBound Event works as the following. If the value in my DB for field = 1 then highlight green if value in DB for field is "" then highlight red. But if value in DB for field = "" then I need to hide the Delete Button.

So far I was able to accomplish this issue by doing the following below but not sure if this is the correct way.

protected void gv_CompletedPhysicalActivityReport_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataRowView rowView = (DataRowView)e.Row.DataItem;

            string ap = rowView["phy_section_complete"].ToString();

            if (ap == "1")
            {
                e.Row.Cells[6].CssClass = "greenbgcolor";
            }
            else if (ap == "")
            {
                e.Row.Cells[6].CssClass = "redbgcolor";
               
                // The following below hides the delete linkbutton if the value in my DB for ap = "".
                e.Row.Cells[0].Text = "";
            }
        }
    }

//If it meets your database condition to hide then
            // Find delete button on your gridview Like below
            //For Eg:
           string ap1 = rowView["DeleteCondtion"].ToString();
            if (ap1 =="HIDEDELETE"){
            LinkButton deleteBtn = (LinkButton)e.Row.FindControl("YourDeleteButtonID");
            deleteBtn.Visible = False;
             }
0
 
Rajar AhmedConsultantCommented:
 else if (ap == "")
            {
                e.Row.Cells[6].CssClass = "redbgcolor";
               
                // The following below hides the delete linkbutton if the value in my DB for ap = "".
//                e.Row.Cells[0].Text = "";
//I hope You have some button in gridview to delete a row , 
//You need to find that button using the ID and then hide it 
   LinkButton deleteBtn = (LinkButton)e.Row.FindControl("LinkButton1");
  deleteBtn.Visible = False;
            }

Open in new window


Please post your gridview design code
0
 
asp_net2Author Commented:
I'm going to attach GridView design code and the codebehind for it. thanks again for your help...

Design Code:
<asp:GridView ID="gv_CompletedGHAPTwoReport" runat="server" OnRowDataBound="gv_CompletedGHAPTwoReport_RowDataBound" OnRowDeleting="gv_CompletedGHAPTwoReport_RowDeleting" AutoGenerateColumns="False" CellPadding="4" ForeColor="#474747" Width="600px" BorderColor="#E3E3E3" GridLines="None" DataKeyNames="ghptwo_id">
                    <AlternatingRowStyle BackColor="White" ForeColor="#474747" BorderColor="#E3E3E3" />
                    <Columns>
                        <asp:TemplateField ShowHeader="False">
                            <ItemTemplate>
                                <asp:LinkButton ID="LinkButton1"
                                    runat="server" CausesValidation="False"
                                    CommandName="Delete"
                                    OnClientClick='return confirm("Are you sure you want to reset this employee?");'
                                    Text="reset" />
                            </ItemTemplate>
                            <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                            <ItemStyle Font-Size="0.875em" />
                        </asp:TemplateField>

                        <asp:BoundField DataField="ghptwo_id">
                            <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                            <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
                        <asp:BoundField DataField="emp_firstname">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
                        <asp:BoundField DataField="emp_lastname">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
                        <asp:BoundField DataField="emp_email">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" />
                        </asp:BoundField>
                        <asp:BoundField DataField="ghptwo_date" DataFormatString="{0:d}" HeaderText="DATE">
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle Font-Size="0.875em" ForeColor="#474747" HorizontalAlign="Left" />
                        </asp:BoundField>
                        <asp:BoundField DataField="ghptwo_section_complete" HeaderText="COMPLETE" >
                        <HeaderStyle Font-Size="0.875em" HorizontalAlign="Left" />
                        <ItemStyle HorizontalAlign="Center" />
                        </asp:BoundField>
                    </Columns>
                    <EditRowStyle BackColor="#999999" BorderColor="#E3E3E3" />
                    <FooterStyle BackColor="#0091c1" ForeColor="White" />
                    <HeaderStyle BackColor="#0091c1" ForeColor="White" BorderColor="#E3E3E3" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="#333333" BorderColor="#E3E3E3" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" BorderColor="#E3E3E3" />
                    <SortedAscendingCellStyle BackColor="#E9E7E2" />
                    <SortedAscendingHeaderStyle BackColor="#506C8C" />
                    <SortedDescendingCellStyle BackColor="#FFFDF8" />
                    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                </asp:GridView>

Open in new window


CodeBehind:
using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net.Mail;
using System.Net.NetworkInformation;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Security;

public partial class administrator_ghaptwo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        RetrieveCompletedGHAPTwoReport();
    }

    protected void RetrieveCompletedGHAPTwoReport()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessTracker"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "RetrieveCompletedGHAPTwoReport";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        DataTable dtCompletedGHAPTwoReport = new DataTable("Modify");

        SqlDataAdapter adp = new SqlDataAdapter();

        try
        {
            conn.Open();

            adp.SelectCommand = cmd;
            adp.Fill(dtCompletedGHAPTwoReport);

            if ((dtCompletedGHAPTwoReport != null))
            {
                DataRow data = dtCompletedGHAPTwoReport.Rows[0];

                gv_CompletedGHAPTwoReport.DataSource = dtCompletedGHAPTwoReport;
                gv_CompletedGHAPTwoReport.DataBind();
            }
        }

        catch (Exception ex)
        {
            ex.Message.ToString();
        }

        finally
        {
            conn.Close();
        }
    }

    protected void gv_CompletedGHAPTwoReport_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataRowView rowView = (DataRowView)e.Row.DataItem;

            string ap = rowView["ghptwo_section_complete"].ToString();

            if (ap == "1")
            {
                e.Row.Cells[6].CssClass = "greenbgcolor";
            }
            else if (ap == "")
            {
                e.Row.Cells[6].CssClass = "redbgcolor";
                e.Row.Cells[0].Text = "";    
            }
        }
    }

    protected void gv_CompletedGHAPTwoReport_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessTracker"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "DeleteGHAPTwoReportByID";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        cmd.Parameters.Add("@ghptwo_id", SqlDbType.Int).Value = Convert.ToInt32(gv_CompletedGHAPTwoReport.Rows[e.RowIndex].Cells[1].Text);

        try
        {
            conn.Open();

            cmd.ExecuteNonQuery();
        }

        catch (Exception ex)
        {
            ex.Message.ToString();
        }

        finally
        {
            conn.Close();
            RetrieveCompletedGHAPTwoReport();
        }
    }
}

Open in new window

0
 
Rajar AhmedConsultantCommented:
Try these changes :

1.Check postback here on pageload :
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            RetrieveCompletedGHAPTwoReport(); 
        }
    }

Open in new window

2.throw new Exception(ex.Message.ToString()); //Change to this line on catch
 protected void gv_CompletedGHAPTwoReport_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessTracker"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "DeleteGHAPTwoReportByID";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        cmd.Parameters.Add("@ghptwo_id", SqlDbType.Int).Value = Convert.ToInt32(gv_CompletedGHAPTwoReport.Rows[e.RowIndex].Cells[1].Text);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }

        catch (Exception ex)
        {
            //ex.Message.ToString()
            throw new Exception(ex.Message.ToString()); //Change to this line
        } 

        finally
        {
            conn.Close();
            RetrieveCompletedGHAPTwoReport();
        }
    }
}

Open in new window


3.On rowdatabound:
 protected void gv_CompletedGHAPTwoReport_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
DataRowView rowView = (DataRowView)e.Row.DataItem;

            string ap = rowView["ghptwo_section_complete"].ToString();
            ap = ap.Trim();
            if (ap == "1")
            {
                e.Row.Cells[6].CssClass = "greenbgcolor";
            }
            else if (string.IsNullOrEmpty(ap))
            {
                e.Row.Cells[6].CssClass = "redbgcolor";
                e.Row.Cells[0].Text = "";
            }
}
}

Open in new window


Other things looks fine , revert me if any issues found .
0
 
asp_net2Author Commented:
Ok, so I don't need to worry about implementing the following below then?

//I hope You have some button in gridview to delete a row ,
//You need to find that button using the ID and then hide it
   LinkButton deleteBtn = (LinkButton)e.Row.FindControl("LinkButton1");
  deleteBtn.Visible = False;
0
 
Rajar AhmedConsultantCommented:
yes,
This does the same work,
e.Row.Cells[0].Text = "";

No need for
 LinkButton deleteBtn = (LinkButton)e.Row.FindControl("LinkButton1");
  deleteBtn.Visible = False;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now