Solved

Delete row from GridView Control using ADO.NET

Posted on 2013-05-29
9
445 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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