Delete a row by condition from DataTable

Question

I created DataTable in my project.Fill it with table in database.

Dim myDataTable As New DataTable(Table_Name)

Dim myLocalConnection As New SqlConnection(_ConnectionString)
myLocalConnection.Open()

Dim mySqlDataAdapter As New SqlDataAdapter()
myCommand.Connection = myLocalConnection
mySqlDataAdapter.SelectCommand = myCommand
mySqlDataAdapter.Fill(myDataTable)

Now i want to delete a specific row from tableby condition. And update the table on database. with SqlDataAdapter.Update() method.

mySqlDataAdapter.Update(myDataTable)

How could i do this? help...

There is a similar question asked on the forum [question][1] [1]: http://stackoverflow.com/questions/5648339/deleting-specific-rows-from-datatable — AsitK, Oct 30 '13 at 11:13
possible duplicate of [DataTable, How to conditionally delete rows](http://stackoverflow.com/questions/1591771/datatable-how-to-conditionally-delete-rows) — huMpty duMpty, Oct 30 '13 at 11:19

score 0 · Answer 1 · answered Oct 30 '13 at 11:15

0

Use something like

foreach (DataRow row in myDataTable.Rows)
    if (row ...) // implement your condition here
        myDataTable.Rows.Remove(row);

C# code here because you ask for C# in your title.

answered Oct 30 '13 at 11:15

Raidri

17,258
9
62
65

score 0 · Answer 2 · answered Oct 30 '13 at 11:17

You have to provide the DeleteCommand of the DataAdapter. Then you can use DataRow.Delete to change it's row-state to RowState.Deleted which causes the dataadapter to delete it from database when you use DataAdapter.Update(myDataTable).

To find the rows that you want to delete you can use Linq-To-DataSet:

Dim deleteCommand = new SqlCommand("DELETE FROM ...", myLocalConnection)
mySqlDataAdapter.DeleteCommand = deleteCommand 

Dim query = From row In myDataTable
            Where row.Field(Of String)("ColumnName") = "Delete-Condition"
For Each row As DataRow in query 
    row.Delete()
Next

mySqlDataAdapter.Update(myDataTable)

Delete a row by condition from DataTable

2 Answers2