remove row from dataset

Question

I would like to copy an excel data table into my datagridview but I want to start from a specific row where my table resides (row 9) (there are title, comments, etc.. before that that are not part of the table.) I am using the following code but it doesn't delete the row from the dataset.

    MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

    DtSet = New System.Data.DataSet
    MyCommand.Fill(DtSet)
    DtSet.Tables(0).Rows(3).Delete()
    DtSet.Tables(0).Rows(3).AcceptChanges()


    dataGridArray(selectedTab).DataSource = DtSet.Tables(0)

    'MsgBox("number of Row(s)   -   " & DtSet.Tables(0).Rows.Count)

    MyConnection.Close()

after delete() and acceptChanges, I can still see the title.

Does anyone can see where am I wrong? Thank you.

Answer 1

You don't want to delete the row(in excel) but you want to remove it from the table.

So instead of:

DtSet.Tables(0).Rows(3).Delete()

use:

DtSet.Tables(0).Rows.RemoveAt(3)

But since you don't want to remove a single row as your code suggests but to remove all rows before the 9th row, use:

For i As Int32 = 1 To 8
    DtSet.Tables(0).Rows.RemoveAt(0)
Next