CODEWITHSUNDEEP
vb.net
John Walker
John Walker

Reputation: 1121

VB.NET delete empty datarow

  For Each dr In ds.Tables(0).Rows
                        If String.IsNullOrEmpty(dr("BIL")) Then
                            dr.Delete() //how to delete this row?
                        End If
                    Next

first,will loop all data then check which row in BIL column are empty,if the row in BIL column are empty,then delete the row from dataset,how to delete this empty datarow?

Upvotes: 1

Views: 4519

Answers (4)

Jake Pangan
Jake Pangan

Reputation: 33

Assuming you have 2 columns in table tbl: ColumnA and ColumnB

Dim dv as new DataView
dv = new DataView(tbl)
dv.RowFilter = "ColumnA <> '' AND ColumnB <> ''"
tbl = dv.ToTable()

tbl should no longer have empty rows. Hope this helps.

Upvotes: 0

ajakblackgoat
ajakblackgoat

Reputation: 2149

Try this:

For i As Integer = dt.Rows.Count - 1 To 0 Step -1
  If String.IsNullOrEmpty(dt.Rows(i)("BIL")) Then
    dt.Rows.RemoveAt(i)
  End If
Next

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460138

Do you want to delete it in your database or do you want to remove it from the DataTable? Btw, use dr.IsNull("BIL") instead. Your code compiles only because you've set OPTION STRICT off because dr("BIL") returns object instead of string.

Dataset are getting data from EXCEL,so,dont have any identity column.BTW i just want remove from datatable, not database

Then you have to use DataRowCollection.Remove instead of DataRow.Delete. With Delete wthe row will change it's RowState to Deleted. If you then use a DataAdapter to update the DataSet/DataTable or DataRow it will be deleted in the database.

But you can also use Linq-To-DataSet to filter the table and use DataRow.Field extension method which is strongly typed and supports nullable types:

Dim notNullBilRows = From row In ds.Tables(0)
                     Where Not String.IsNullOrEmpty(row.Field(Of String)("BIL"))

Now you can use CopyToDataTable to create a new DataTable with only rows where BIL is not null, which seems to be the actual requirement.

Dim tblNotNullBilRows  = notNullBilRows.CopyToDataTable()

Here's the non-Linq approach with Remove, you have to create an intermediate collection since you cannot remove elements from a collection during enumeration:

Dim removeList = New List(Of DataRow)
For Each dr As DataRow In ds.Tables(0).Rows
    If String.IsNullOrEmpty(dr.Field(Of String)("BIL")) Then
        removeList.Add(dr)
    End If
Next
For Each dr As DataRow In removeList
    ds.Tables(0).Rows.Remove(dr)
Next

Upvotes: 2

Cubsoft
Cubsoft

Reputation: 1157

You will want to put the index of the rows you wish to delete into an array, then iterate through the array deleting each rows from the datatable using the indexes. You will not need an 'identity' column to do this, the rows will automatically be asigned indexes.

Upvotes: 0

Related Questions