deleting rows with SQLCommandBuilder

[David]

I have a task in an ASP.Net page that I think ought to work, but doesn't.
It's fairly straightforward. It's an event scheduling site, and I want a
user to be able to delete an event. When he clicks the delete button, it
posts back to the page with the event id for the deleted event in the query
page, and then tries to delete the rwo with an SQLCommandBuilder object. The
code looks like this:



string qstring = "Select * from Schedule WHERE EventNumber = " +
Request.QueryString["EventID"];
System.Data.SqlClient.SqlDataAdapter da = new
System.Data.SqlClient.SqlDataAdapter(qstring, MyConnectionString);
System.Data.DataSet ds = new DataSet();
da.Fill(ds);

if (ds.Tables[0].Rows.Count == 1)//Verify that it returned exactly
one row.
{
DataRow dr = ds.Tables[0].Rows[0];//Select the only row.
System.Data.SqlClient.SqlCommandBuilder bldr = new
System.Data.SqlClient.SqlCommandBuilder(da);
ds.Tables[0].Rows.Remove(dr);//Remove it
da.Update(ds);//Update the data set.
Response.Redirect("~/allevents.aspx");
}


I can verify that the correct dataset is being loaded. It has one table,
and that one table has one row. When I call "remove", I can verify that the
row leaves the dataset, so that ds now has one table with zero rows. I call
Update, and I expect to delete the row from the database, but it doesn't.

How do I use the CommandBuilder object to do the delete?

 

[Kerry Moorman]

David,

You must delete the row from the datatable, not remove it.

Deleting it marks it as deleted so the dataadapter's update method will know
to delete it from the database.

If you remove it then there will not be a row marked for deletion so the
dataadapter will not know to delete it from the database.

Kerry Moorman

 

[David]

Thank you.