Solved

Delete row from GridView Control using ADO.NET

Posted on 2013-05-29
9
439 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
  • 5
  • 4
9 Comments
 
LVL 18

Expert Comment

by:Rajar Ahmed
Comment Utility
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
Comment Utility
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
Comment Utility
"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
 
LVL 4

Author Comment

by:asp_net2
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:Rajar Ahmed
Comment Utility
 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now