Delete records from GridView and DB using the button

Question

I have a gridview with some data from my database.

However, when you point to a delete link, the link refers to an invalid identifier.

I want it to use the identifier from the database.

This question is very much related to this - 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 to link DeleteButton link with the correct ID?

Update

The code for loading a page that puts data in a 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; } 

Answer 1

There are several ways to do this. I usually use the 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 attaching an event that 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 a call method every time you want to update a GridView . It is much cleaner to have your GridView call bind its own method to it, which you can call from anywhere on the page (which means you can also call it from the page load event).

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

Answer 2

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

You may also need to explicitly specify the DataTable that you are using as a DataSource

Data Table Definition

  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.