Click here to Skip to main content
15,066,326 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
ivé been trying to delete a row from a table using dataset.tables.rows.remove(); specifying the row to be deleted in the remove function.what i did is exactly like this.

SqlConnection con = new SqlConnection();
con.ConnectionString = "initial catalog=employee;data source=;user id=sa;password=*********";
SqlDataAdapter da = new SqlDataAdapter("select *from dbo.employeeage", con);
SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(da);
DataSet ds = new DataSet("employee");
da.Fill(ds, "employee");
DataRow row = ds.Tables["employee"].Rows[2];
da.Update(ds, "employee");

nothing gets changed in the database.i have like 30 records in the employeeage table.
i'm an absolute newbie to plzz correct me if i am wrong somewhere

[Edit]Added code block. [/Edit]
Updated 17-Jan-13 0:23am

Instead of line

just use

The logic behind is that DataTable is just an off-line in-memory object representing data from your real DB table. You filled real DB employee table into your off-line DataSet ds object by calling
da.Fill(ds, "employee");

Then you reference the filled table, stored in ds DataSet, named "employee", by the code fragment

This returns the object of type DataTable. Consequently, DataTable.Rows Property is just an off-line in-memory collection of DataRow objects filled from your real DB. You can manipulate each DataRow object by changing its value. Changes are tracked by DataRow.RowState Property and various versions of field values are accessible through DataRow.Item Property (String, DataRowVersion). Nothing reflects in real DB so far. Until you call
da.Update(ds, "employee");

Performing DataAdapter.Update Method, data adapter walks through your off-line DataTable.Rows collection of "employee" table. For each row (of DataRow type), row.RowState is one of DataRowState Enum. If adapter finds row.RowState = DataRowState.Added then it tries to call Insert command on real DB, filling in values from this off-line row("current" version of each corresponding field). If row.RowState = DataRowState.Modified, data adapter tries to call Update command on real DB, matching the row.PrimaryKey with the real primary key of the real DB table. If row.RowState = DataRowState.Deleted, the data adapter tries to call Delete command on real DB, matching the row.PrimaryKey with the real primary key of the real DB table. If row.RowState = DataRowState.Unchanged or the row was removed from the Rows collection, then data adapter does nothing in real DB for matching (or missing) row..

So this is it. As you completely removed your off-line representation of your real DB row by calling

then the data adapter da has no reference to that real DB row anymore.
On the other side, if you find your off-line DataRow row object and call

then the row.RowState is set to DataRowState.Deleted and the consequent
da.Update(ds, "employee");

finds the row marked Deleted and calls the Delete command on real DB.

Hope this helps,
Saleel Ahsan 11-Dec-20 5:06am
Thanks a ton for the detailed explanation.....!!!
Saleel Ahsan 11-Dec-20 5:07am
Thanks a ton for the detailed explanation...!!
You need to set the DeleteCommand property for the adapter.
rtz87 17-Jan-13 10:05am
thanx for the link but i'm still not clear on how to do this can u plz give me an example.
lw@zi 18-Jan-13 1:37am
Check out MSDN.
As Danish suggest you can use DeleteCommand of SQLDataAdapter
review this link it might helps you...

rtz87 17-Jan-13 10:07am
thanx for the the link but i'm still not clear on how to do this can u plz give me an example.
Tejas Vaishnav 18-Jan-13 2:10am
There was and example below that article. you can go throw that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900