?
Solved

Delete row from GridView Control using ADO.NET

Posted on 2013-05-29
9
Medium Priority
?
448 Views
Last Modified: 2013-05-31
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
Comment
Question by:asp_net2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 18

Expert Comment

by:Rajar Ahmed
ID: 39205329
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
 
LVL 4

Author Comment

by:asp_net2
ID: 39205410
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
 
LVL 18

Expert Comment

by:Rajar Ahmed
ID: 39206612
"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
Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

 
LVL 4

Author Comment

by:asp_net2
ID: 39207964
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
 
LVL 18

Expert Comment

by:Rajar Ahmed
ID: 39208634
 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
 
LVL 4

Author Comment

by:asp_net2
ID: 39208718
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
 
LVL 18

Accepted Solution

by:
Rajar Ahmed earned 2000 total points
ID: 39209803
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
 
LVL 4

Author Comment

by:asp_net2
ID: 39210944
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
 
LVL 18

Expert Comment

by:Rajar Ahmed
ID: 39210971
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

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question