CODEWITHSUNDEEP
c#sql
Matt
Matt

Reputation: 482

How to delete from dataset

How do you delete a row from a dataset and then from the database itself? Is it needed to specify a SQLCommand for this or the database should be auto-updated? Here's my code that I thought will work but no delete is done.

private void button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();

SqlDataAdapter daf = new SqlDataAdapter();
ds.Tables["Film"].Constraints.Add("PK_Film", ds.Tables["Film"].Columns["id"], true);

int ind = listBoxchildren.SelectedIndex;
listBoxchildren.DataSource = null;

ds.Tables["Film"].Rows[ind].Delete();
        ds.Tables["Film"].AcceptChanges();

daf.Update(ds,"Film");
listBoxchildren.DataSource = ds;
listBoxchildren.DisplayMember = "Director.fk_FilmDir.title";}

Upvotes: 0

Views: 271

Answers (2)

CrazyDeveloper
CrazyDeveloper

Reputation: 105

this is it: ds.Tables["TABLE NAME"].Rows[position].Delete (); then your dataadapter update: daf.update(ds.Tables["TABLE NAME"])

Upvotes: 0

Steve
Steve

Reputation: 216293

Usually a dataadaper initializes its DeleteCommand, UpdateCommand and InsertCommand when you pass a SELECT statement to fill a dataset or datatable. In your code, you create this daf variable without giving any Select statement and thus it is not able to create a Delete command for your deleted row.

Moreover you call AcceptChanges that has the effect to remove the deleted row from the in memory data of the Dataset thus removing any possibility for the Update method to find the row that should be removed from the database.

You need to add two things and remove one line

Add a Select statement to your SqlDataAdapter when you build it

SqlDataAdapter daf = new SqlDataAdapter("select * from film", yourConnection);
SqlCommandBuilder builder = new SqlCommandBuilder(daf);
daf.DeleteCommand = builder.GetDeleteCommand();

and then remove this line

ds.Tables["Film"].AcceptChanges();

When you call Delete the row is only marked as deleted but it is still in the dataset table. The deleted row is needed by the Update command to get the info required to perfom the delete in the database table.

Upvotes: 1

Related Questions