I can't delete data from my database in Gridview

Question

I can't delete data from my database in Gridview

98 Views Asked by Shadowwalker At 19 October 2024 at 23:50

I am trying to do delete a column for my Gridview and also delete it from my database in the Server explorer when I select the checkbox and click on the delete button, but whenever I click on the delete button, the page just refresh and the data in my database is not being deleted. Not sure if this is the correct way to delete it from the database. There is no error message being display when I run my code.

This is my FAQ.aspx:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head runat="server">  
    <title></title>  
</head>  
<body>  
    <form id="form1" runat="server">  
    <div>  
      
        <table style="width:100%;">  
            <tr>  
                <td>  
                     </td>  
                <td>  
                     </td>  
                <td>  
                     </td>  
            </tr>  
            <tr>  
                <td>  
                     </td>  
                <td>  
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="468px">  
                        <Columns>  
                            <asp:TemplateField HeaderText="Type">  
                                <EditItemTemplate>  
                                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("type") %>'></asp:TextBox>  
                                </EditItemTemplate>  
                                <ItemTemplate>  
                                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("type") %>'></asp:Label>  
                                </ItemTemplate>  
                            </asp:TemplateField>  
                            <asp:TemplateField HeaderText="Description">  
                                <EditItemTemplate>  
                                    <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("description") %>'></asp:TextBox>  
                                </EditItemTemplate>  
                                <ItemTemplate>  
                                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("description") %>'></asp:Label>  
                                </ItemTemplate>  
                            </asp:TemplateField>  
                            <asp:TemplateField HeaderText="Email">  
                                <EditItemTemplate>  
                                    <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox>  
                                </EditItemTemplate>  
                                <ItemTemplate>  
                                    <asp:Label ID="Label3" runat="server" Text='<%# Bind("Email") %>'></asp:Label>  
                                </ItemTemplate>  
                            </asp:TemplateField>  
                            <asp:TemplateField HeaderText="Role">  
                                <EditItemTemplate>  
                                    <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("role") %>'></asp:TextBox>  
                                </EditItemTemplate>  
                                <ItemTemplate>  
                                    <asp:Label ID="Label4" runat="server" Text='<%# Bind("role") %>'></asp:Label>  
                                </ItemTemplate>  
                            </asp:TemplateField> 
                            <asp:TemplateField>  
                                <EditItemTemplate>  
                                    <asp:CheckBox ID="CheckBox1" runat="server" />  
                                </EditItemTemplate>  
                                <ItemTemplate>  
                                    <asp:CheckBox ID="CheckBox1" runat="server" />  
                                </ItemTemplate>  
                            </asp:TemplateField>  
                        </Columns>  
                    </asp:GridView>  
                </td>  
                <td>  
                     </td>  
            </tr>  
            <tr>  
                <td>  
                     </td>  
                <td>  
                    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Delete" />  
                </td>  
                <td>  
                     </td>  
            </tr>  
        </table>  
      
    </div>  
    </form>  
</body>  
</html>

This is my FAQ.aspx.cs:

        public void refreshdata()
        {


            SqlCommand cmd = new SqlCommand("select * from Enquiry", con);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();

        }

        protected void Button1_Click(object sender, EventArgs e)
        {

            foreach (GridViewRow gvrow in GridView1.Rows)
            {

                CheckBox chck = gvrow.FindControl("CheckBox1") as CheckBox;
                if (chck.Checked)
                {

                    var Label = gvrow.FindControl("Label1") as Label;


                    SqlCommand cmd = new SqlCommand("DELETE FROM Enquiry WHERE enquiryID=@id", con);
                    cmd.Parameters.AddWithValue("@id", int.Parse(Label.Text));


                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    refreshdata();
                    


                }
            }


        }
    }
}

Original Q&A

There are 2 best solutions below

**Rafalon** · Answer 1

I think you are not deleting anything because of this:

<asp:Label ID="Label1" runat="server" Text='<%# Bind("type") %>'></asp:Label>
// not sure this is what should be here --------------^^^^

// because you are using this value here ----------------------------vvv
SqlCommand cmd = new SqlCommand("DELETE FROM Enquiry WHERE enquiryID=@id", con);
cmd.Parameters.AddWithValue("@id", int.Parse(Label.Text));
// remember that Label is "Label1" ----------^^^^^^^^^^

In short, your Label.Text does not hold an enquiryID, but another value, taken from type column.

**Albert D. Kallal** · Answer 2

It not at all clear why (what) Bind("type") here is doing.

I would change this:

 <asp:TemplateField>
     <EditItemTemplate>  
        <asp:CheckBox ID="CheckBox1" runat="server" />  
          </EditItemTemplate>  
       <ItemTemplate>  
          <asp:CheckBox ID="CheckBox1" runat="server" />  
       </ItemTemplate>  
 </asp:TemplateField>

To:

 <asp:TemplateField>
    <ItemTemplate>  
      <asp:CheckBox ID="CheckBox1" runat="server"
       MyPKID = '<%# Eval("enqueirID") %>' />  
       </ItemTemplate>  
 </asp:TemplateField>

Edit: Ok, it DOES look like you are actually using the edit template (can you confirm that ? - again its important that answer these questions. If you REALLY are using the edit template, then I suppose you could/would then have this:

  <asp:TemplateField>
    <ItemTemplate>  
      <asp:CheckBox ID="CheckBox1" runat="server"
       MyPKID = '<%# Eval("enqueirID") %>' />  
       </ItemTemplate>  

    <EditItemTemplate>  
      <asp:CheckBox ID="CheckBox1" runat="server"
       MyPKID = '<%# Eval("enqueirID") %>' />  
       </EditItemTemplate>  

 </asp:TemplateField>

Now in this code, you can get/grab/have the PK value:

CheckBox chck = gvrow.FindControl("CheckBox1") as CheckBox; if (chck.Checked) {

               int MyPK = chck.Attributes.Item("MyPKID");
               debug.print(MyPK);

              SqlCommand cmd = new SqlCommand("DELETE FROM Enquiry WHERE enquiryID=@id", con);
                cmd.Parameters.Add("@id", sqlDbType.Int).Value = MyPK


                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

In other words, since we do NOT have a column in the grid that has/shows/contains/holds the PK value we need/want? Then just add the PK column as a attribute to the check box control.

But we have TWO people in the comments asking about "Type". We just don't' think that a column name "Type" has anything at all to do with the actual database PK id of the row and a column called enquiryID (I suppose it MIGHT be and you get to toss some nice egg on everyone's face here is THAT REALLY IS THE CASE!!!).

Right now, we have two people here in comments that are hamming away and asking multiple times if in fact the value of "Type" from the database is REALLY the SAME value as enquiryID. (I suppose it might be, but that is ONE HUGE whopper of a confusing here).

We all just asking for clarification here.

I would suggest that you Just use Eval("name of the database column you simple want goes here") and you should be fine.

As noted, often we don't have a column or don't want to display/show the PK ID from the database in the gridview. So either you can HIDE that column for display, or better yet, just add a custom made up attribute for the check box control, and pluck/grab/use/have the PK id contained in the check box control. Since we do a find control on the check box, and we do NOT have a separate row control holding the pk id, then we just might as well setup a custom attribute on the check box that ALSO contains/has/holds the PK id value of enquiryID in that control.

I don't see any control in the grid row that has/holds/contains the ALL VERY important PK id that we obviously will need and ARE going to use to delete the ONE row from the database.

Edit: ------------------------------------

Ok, so the REALLY big deal here is the fact that a edit template is being used (I don't see a Show Edit button = true in your posted markup. I note this issue about the EditTemplate, since that blows up darn near everything that would be VERY simple.

If you using a EditTemplate, then do you ONLY want the check box to be changed WHEN the user hits edit? You have to answer and decide this question.

The REASON why the edit template is a HUGE deal? Well, just dropping a un-bound checkbox is simple - easy. But if you REALLY do have a edit template? Well, if you hit the check box, and THEN hit "edit" to edit the row, then the value of the checkbox is NOT transferred from the template to the edit template. (reason: the check box is NOT bound to any data).

If you are in a hurry - and JUST HAD to get this working? Then I would add a column to the database, and being the check box to the new column. That way you could get/grab the value from the database, and delete based on that.

I simply cannot STRESS how huge, large, big of a deal having the un-bound check box AND ALSO a edit template in operation!!! - really big deal, and really big challenge.

So, first up: Since you have a edit template, then the checkbox in the normal template (itemTempalte) should be disabled. After all, if you forcing the user to use "edit" and a "edit template", then values should only be changed.

As noted, with just a template - you can check the boxes. And your delete button can loop the gv rows and you can do a find control and you WILL get the checked value.

But, if a edit template is required? Well then we have to PERSIT the checked values, since the change from template to edit template (and then update and exit of edit template) means we MUST hold, keep and persist the check box values (else they will be lost!!!).

And it would have been SPECTACULAR if you shared the fact that when you check the box and flip from Edit mode to normal that the check box was being lost!!!

Ok, so again, if we REALLY do need and are going to use the edit template? Then we have to wire up some code to hold and persist the check box values. We can use either row id, but MUCH better is to use row PK value.

So, now lets pile in some code to persist the check box values. There are a good number, but here is what I used in the past:

{
private Dictionary<int, bool> MyDict;

protected void Page_Load(object sender, System.EventArgs e)
{
    if (System.Web.UI.Page.IsPostBack == false)
    {
        MyDict = new Dictionary<int, bool>();
        System.Web.UI.Page.Session["MyChecked"] = MyDict;
    }
    else
        MyDict = System.Web.UI.Page.Session["MyChecked"];
}
}

So note how I created a MyDict at the form "class" level. I also of course SHOVE the MyDict into session() and/or if not the first page load, then I restore the MyDict from session(). You can also well use ViewState if you not running session(), and ViewState is PROBABLY much better of a choice here.

So, when you check a box (or un-check) we simply save the value into our handy dandy MyDict.

So, in the markup, add this:

       <ItemTemplate>
          <asp:CheckBox ID="CheckBox1" runat="server" 
              OnCheckedChanged="CheckBox1_CheckedChanged"
              AutoPostBack="true" 
              Checked='<%# GetCheck(Eval("ID")) %>'
              MyPKID = '<%# Eval("ID") %>'
           />
       </ItemTemplate>

So, as noted, I am STILL using a custom PKID attribute for the check box control. (just saves some code and having to try and mess with row indexing in the grid view).

Note that we added several things like autopostback=true. And we added MyPKID that gets the PK row ID. I used "ID" but you will of course use whatever your PK row ID is - (enquiryID).

Note that we wired up a event for the check box. When we check (or un-check) we going to SAVE the checked box value into MyDict.

So the code stub for the check box event is this:

protected void CheckBox1_CheckedChanged(object sender, EventArgs e)
{
CheckBox chk = sender;

MyDict(chk.Attributes.Item("MyPKID")) = chk.Checked;
}

So note a lot of code. It simply adds (or sets) the MyDict collection with the PK ID and the setting. I REALLY like a "dictionary" over a collection, since a setting will add a new dict pair if not existing, or simple overwrite if it does exist.

So the above persisted "MyDict" is thus maintained by simply check on a check box.

Now the ONLY part left is to BIND the Check box to a function that returns the check box setting.

The code for that is again REALLY simple. We just pass the function the PK, and return true or false.

public bool GetCheck(int MyPKID)
{

// check for check box in this row
if (MyDict.ContainsKey(MyPKID))
{
    Debug.Print("key found = " + MyPKID);
    return MyDict(MyPKID);
}
else
    return false;
}

So now we have the ability to "return" the value of the check box based on PK id.

So note this expression in the check box:

<asp:CheckBox ID="CheckBox1" runat="server" 
      OnCheckedChanged="CheckBox1_CheckedChanged"
      AutoPostBack="true" Checked='<%# GetCheck(Eval("ID")) %>'
      MyPKID = '<%# Eval("ID") %>'
 />

Note how in above, we SIMPLE call + use our custom "public" function from the web page code behind. We call "GetCheck()" but pass it the PK ID (as noted, I'm using "ID" - you will use your PK id.

So the above extra code? It is ONLY required becuase of TWO big reasons:

You are using both Edit template and item template.
AND ALSO that the check box is NOT bound, so you have to manage
the state in code.

As noted, if this was just a grid - and without edit template? Then you can/could JUST simple drop in the un-bound check box, and you off to the races.

It is the interaction between edit template and template that messes this up.

Also, I don't see your edit button, and how you been or are to flip between normal and edit mode - but this VERY much suggests that the check box should be disabled for normal and ONLY enabled for "edit" mode. But the code and ideas above posted "as is" should work fine if you want or allow to check the box without having to first use edit mode. if you don't care, then you don't need to repeat the one check box for both templates - it will work fine as per above.

I can't delete data from my database in Gridview

There are 2 best solutions below

Related Questions in C#

Related Questions in ASP.NET

Related Questions in DATABASE

Related Questions in GRIDVIEW

Related Questions in SERVER-EXPLORER

Trending Questions

Popular # Hahtags

Popular Questions