Remove Row through DataAdapter

Question

I've initialized a dataAdapter :

string sql = "SELECT * From localitati";
da1 = new System.Data.SqlClient.SqlDataAdapter(sql, con);
da1.Fill(ds1, "localitati");

And this works just fine. The problem is when i try to delete a record and update the database. I remove a record from the dataset as such :

ds1.Tables["localitati"].Rows.Remove(dRow);

And this works just fine as well(verified).

The problem is when i update the DataAdapter, the DataBase doesn't get modified :

con.Open()
da1.Update(ds1, "localitati");
con.Close();

What could be the problem ?

score 14 · Answer 1 · answered Aug 21 '12 at 00:18

14

What fixed it for me was to call the Delete method on the DataRow instead of the Remove method on the DataTable.

ds.Tables["localitati"].Rows.Find(primaryKeyValue).Delete();

or just simply

dr.Delete();

answered Aug 21 '12 at 00:18

Rami A.

10,302
4
44
87

1

This should be the accepted answer. The .Delete() method will alert the parent DataTable that the row has been deleted. Simply removing the row from the table is not enough. – oscilatingcretin May 25 '16 at 13:48
In my code, I was trying to do Table.Clear(). It was not updating the database after the Update call. However, using Delete did. I agree this should be the accepted answer. – Maxter Jun 29 '18 at 18:02

score 1 · Accepted Answer · answered Nov 29 '10 at 08:58

1

You need to make sure you've set the da1.DeleteCommand - this is the command that will be fired for each row in the DataTable that has been deleted. See this MSDN reference for example.

answered Nov 29 '10 at 08:58

AdaTheDev

142,592
28
206
200

score 0 · Answer 3 · edited Oct 15 '16 at 22:31

0

DataRow dr = datatable.Rows.Find(key);      
int Index = datatable.Rows.IndexOf(dr);

BindingContext[DataTable].RemoveAt(Index);     
BindingContext[DataTable].EndCurrentEdit(); 

dataAdapter.Update(DataTable);    
DataTable.AcceptChanges();

edited Oct 15 '16 at 22:31

Gilad Green

36,708
7
61
95

answered Oct 15 '16 at 22:09

Ahmed Fayez

1
1

2

Some explanation could be nice – Petter Friberg Oct 15 '16 at 22:11

score 0 · Answer 4 · answered Nov 29 '10 at 08:43

0

try below code assuming that Database is not throwing any exception.

con.Open() 
da1.Update(ds1.Tables["localitati"].GetChanges()); 
con.Close();

answered Nov 29 '10 at 08:43

TalentTuner

17,262
5
38
63

andrei m · Answer 5 · 2010-11-29T09:20:56.100

In the code that you have posted only the SelectCommand is set for the DataAdapter. You could use the following code to generate the Insert, Update and Delete commands for da1.

string sql = "SELECT * From localitati";
da1 = new SqlDataAdapter(sql, con);
SqlCommandBuilder builder = new SqlCommandBuilder(da1);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
da1.Fill(ds1, "localitati");

The CommandBuilder however should be used only for relatively simple scenarios (details). For he rest is recommended to write your own commands that rely on custom command texts/stored procedures.

If it still doesn't work, you could start a SQL Server Profiler on the server to trace what command gets to the database when you execute the Update method.

Remove Row through DataAdapter

5 Answers5

Linked