Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete row from GridView Control using ADO.NET

Posted on 2013-05-29
9
Medium Priority
?
453 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Screencast - Getting to Know the Pipeline

596 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