Deleting records from GridView and DB using button

Question

I have a GridView with some data from my database.

However, when hovering the delete link, it links to the incorrect ID.

I want it to use the ID from the database.

This question is very much related to this one - but it never received an answer.

My GridView code looks like this:

<asp:GridView ID="GridView1" runat="server" OnRowDeleting="GridView1_RowDeleting1" AutoGenerateColumns="false" DataKeyNames="id">
    <Columns>
      <asp:BoundField HeaderText="Name" DataField="name" />
      <asp:BoundField HeaderText="Email" DataField="email" />
      <asp:BoundField HeaderText="Comment" DataField="comment" />
      <asp:CommandField ShowDeleteButton="True"  />
    </Columns>
</asp:GridView>

How do I make the DeleteButton link to the correct ID?

Update

Page-load code that puts the data in the datatable:

    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable commentsTable = null;
        commentsTable = new DataTable("Comments");
        using (SqlDataReader reader = studentManager.getCommentsFromDB())
        {
            commentsTable.Load(reader);
            GridView1.DataSource = commentsTable;
            GridView1.DataBind();
        }
    }

getCommentsFromDB:

    public SqlDataReader getCommentsFromDB()
    {
        SqlConnection conn = dal.connectDatabase();
        conn.Open();
        cmd = new SqlCommand(@"SELECT id, name, email, comment FROM GuestBook", conn);
        rdr = cmd.ExecuteReader();
        return rdr;
    }

Added the selecting one. The deleting one is yet to be made, as I wanted to solve the ID issue first. — Frederik
– Frederik, Commented Oct 25, 2012 at 20:02

Paul Fleming · Accepted Answer · 2012-11-01 18:41:42Z

3

There's a number of ways you can go about doing this. I tend to use an ASP.NET Button and set the CommandName property to "Delete".

<asp:Button ID="btnDelete" runat="server" CommandName="Delete" />

or

<asp:ImageButton ID="ibtnDelete" runat="server" ImageUrl="~/Images/Delete.gif" CommandName="Delete" />

or

<asp:CommandField ButtonType="Button" DeleteText="Delete" ShowDeleteButton="true" />

Then it's just a matter of wiring up the event that handles OnRowDeleting.

protected void GridView1_RowDeleting1(object sender, GridViewDeleteEventArgs e) {
    // Get the id
    string id = GridView1.DataKeys[e.RowIndex].Value.ToString();

    // Create the delete query
    string sql = @"delete from GuestBook where id = @id";

    using (SqlConnection conn = dal.connectDatabase()) {

        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@id", SqlDbType.Int);
        cmd.Parameters["@id"].Value = id;

        try {
            conn.Open();
            cmd.ExecuteScalar();
        }
        catch (Exception ex) {
            Console.WriteLine(ex.Message);
        }
    }

    // Refresh the GridView1
    Bind_GridView1();
}

... and the method to call each time that you want to refresh the GridView. It's much cleaner to have your GridView binding call in it's own method that you can call from anywhere in the page (which means that you can call it from your page load event as well).

public void Bind_GridView1() {
        DataTable commentsTable = null;
        commentsTable = new DataTable("Comments");
        using (SqlDataReader reader = studentManager.getCommentsFromDB())
        {
            commentsTable.Load(reader);
            GridView1.DataSource = commentsTable;
            GridView1.DataBind();
        }
}

edited Nov 1, 2012 at 18:41

Paul Fleming

24.6k8 gold badges79 silver badges113 bronze badges

answered Oct 25, 2012 at 20:21

Jagd

7,34623 gold badges77 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frederik Over a year ago

Thanks - this solved all my issues! Now. How do I make it reload the page after pressing the delete button?

Jagd Over a year ago

Revised my sql code. I'm a little rusty on sql. Much easier to just create an interface face and use it rather than muck in details of it every time.

Jagd Over a year ago

Reload the page? Ah... you mean, how do you make it reload the gridview so that you can see the recent changes? Simple, just rebind the gridview again. It's easier and cleaner to create a method that takes care of the gridview binding for you. I'll enhance my answer again.

Paul Fleming · Accepted Answer · 2012-11-01 18:42:15Z

0

Try adding <asp:BoundField DataField="id" ReadOnly="True" Visible="false" />

You may also have to explicitly define the DataTable you are using as the DataSource

Defining a data table

        DataTable dt = new DataTable();
        DataColumn col = new DataColumn()
        {
            ColumnName = "id"
        };
        dt.Columns.Add(col);
        col = new DataColumn()
        {
            ColumnName = "name"
        };
        dt.Columns.Add(col);

And continue for each column you need

edited Nov 1, 2012 at 18:42

Paul Fleming

24.6k8 gold badges79 silver badges113 bronze badges

answered Oct 25, 2012 at 20:12

AntLaC

1,23510 silver badges22 bronze badges

1 Comment

Frederik Over a year ago

That didn't make it ding. Could you explain the part about defining the datatable as datasource? I am still quite new in ASP.NET and C#.

Collectives™ on Stack Overflow

Deleting records from GridView and DB using button

Update

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related