# Visual Basic > Visual Basic .NET > VS 2012 Clear DataSet

## PJ17limited

Hi all, I'm new to this forum. My problem is, I'm trying to clear a dataset and reload the dataset and repopulate a datagridview. For some reason when I delete a row in my dataset and try to repopulate the datagridview the deleted item is still being added to the datagrid even though the row is no longer in my database. Could someone please look at my code and see what I am doing wrong? Thanks...


```
For i = 0 To MaxRows1 - 1
                If ds1.Tables("EListInfo").Rows(i).Item("ExerciseItem") = cmbNewEx.Text Then
                    ds1.Tables("EListInfo").Rows(i).Delete()

                    da1.Update(ds1, "EListInfo")
                End If
            Next i
            
            ds1.Tables("EListInfo").Clear()

            sql1 = "Select * FROM EListtbl"                                         '********* Exercise List *****************************
            da1 = New OleDb.OleDbDataAdapter(sql1, con)
            da1.Fill(ds1, "EListInfo")
            MaxRows1 = ds1.Tables("EListInfo").Rows.Count

            With dgvMasList
                For i = 0 To .Rows.Count - 1
                    .Rows(i).Cells(0).Value = Nothing
                Next
                For i = 0 To MaxRows1 - 1
                    If ds1.Tables("EListInfo").Rows(i).Item("BP") = lbBodyPart2.Text Then
                        .Rows(x).Cells(0).Value = (ds1.Tables("EListInfo").Rows(i).Item("ExerciseItem"))
                        x = x + 1
                    End If
                Next
            End With
```

----------


## jmcilhinney

Firstly, don't use a DataSet if you're only using one DataTable. Just use a DataTable.

As for the question, there's no need to clear and repopulate the DataTable in the first place. Call Fill on your data adapter to populate your DataTable and then bind that to your DataGridView, preferably via a BindingSource. The grid will then show what's in the DataTable, whatever that may be. When you delete a row in the DataTable, it will disappear from the grid. You can make multiple changes - add edit and delete - to the DataTable and the grid will stay in sync, which is the whole point of databinding. You can then save all the changes with a single call to Update on your data adapter.

----------


## PJ17limited

Ok, but won't that populate the datagridview with all data in the datatable? I only want to populate the datagridview with data that matches cmbNewEx.Text.

----------


## jmcilhinney

Firstly, do you need all the data in the DataTable in the first place if you're only going to display some of it? If not, just retrieve the data you want to display in the first place. If so, you can still filter the bound data via a BindingSource or a DataView.

----------


## PJ17limited

Yeah kind of do I guess, it's that or have a bunch of tables in my database. This table is holding 11 different sources of data displayed from the use's choice in cmbNewEx.Text. I already have 10 Tables in my database.

----------


## jmcilhinney

The number of tables in your database is not something you should concern yourself with. You should design your database properly and then the number of tables you end up with will be the correct number of tables, whatever that may be. I don't know exactly what this means:



> This table is holding 11 different sources of data


If it's the same type of data for 11 different categories then that's not a problem. If it's 11 different types of data then that's just wrong. It's impossible to tell what you actually mean from your description.

It's hard to tell what the best course of action is from the information you have provided. Lets assume you mean that you have one table containing the same type of data for 11 different categories and the user selects a category using a ComboBox and you then display the data for that category. You could execute a filtered query each time and retrieve just the data for that category. Alternatively, you could retrieve all the data and bind the DataTable via a BindingSource, then set the Filter property of the BindingSource based on the ComboBox selection to control what data is displayed. If the total amount of data is very large, I'd go with the first option and the second option for a smaller data set.

----------


## PJ17limited

Sorry for not giving you enough information. In the database table I have exercises for 11 different body parts. I only want to load the datagridview with exercises for the selected body part from a ComboBox.

----------


## PJ17limited

As you can tell I'm fairly new to databases. What little I've worked with databases I've never used a binding source.

----------


## jmcilhinney

> Sorry for not giving you enough information. In the database table I have exercises for 11 different body parts. I only want to load the datagridview with exercises for the selected body part from a ComboBox.


Then the body part is the category so the choice specified in post #6 applies. If the total number or records is small, I would retrieve all the data and bind it to the grid via a BindingSource and then filter the BindingSource by body part.

----------


## PJ17limited

So, it's the filter part I'm not sure of after binding. Plus line 6 is not right because the datagrid is empty after the sub finishes.


```
For i = 0 To MaxRows1 - 1
                If ds1.Tables("EListInfo").Rows(i).Item("ExerciseItem") = cmbNewEx.Text Then
                    ds1.Tables("EListInfo").Rows(i).Delete()

                    da1.Update(ds1, "EListInfo")
                End If
            Next i

            dgvMasList.DataSource = ds1.Tables("EListInfo")
```

----------


## PJ17limited

Sorry, line 7

----------


## Shaggy Hiker

You shouldn't need line 7. If you bind the DGV to the datatable, with or without using a bindingsource, then changes to the table are reflected in the DGV without any need to re-bind. That's rather the point of binding...the two are bound together.

However, the more I look at the code, the more I think it's not what you want to be doing. What that loop would do (inefficiently, by the way) would be to iterate through the table, find every row that matched the exercise, then delete it from the database. Yeah, it would delete it from the table, but it would also delete it form the database. That doesn't sound like what you've been talking about, as you've been talking about filtering things and now you're wiping them out of the DB entirely. Seems kind of drastic.

What Update does is iterate through all the rows in the datatable looking at the RowState. If the RowState is Deleted, then it executes the DeleteCommand against the DB. If the RowState is Added, then it executes the InsertCommand against the DB. If the RowState is Modified, it executes the UpdateCommand against the DB. For ANY of that to work, there must be Delete, Update, and Insert commands, which you probably already have, or else the code would have crashed anyways. Still, Update is going through ALL the rows in the datatable and looking at the RowState of each row in turn. What you are doing is deleting one row, which sets the RowState of that row to Deleted, then you call Update, so it looks through all the rows, all but one of which will be Unchanged. For that one, it will call the DeleteCommand. You then delete another row...and do it all over again.

Even if you want to delete from the DB, you need to move that Update call outside of the loop once all the records that need to be deleted have been set to RowState Deleted. Then the Update command will be efficient, as it can delete all of them at once. In practice, it likely will mean relatively little, in this case, but it would be more efficient.

Also, as JMC noted, you don't need a dataset. All a dataset is, is a collection of datatables. It's like you are using an array of DataTables, but you only have one item in the array. It doesn't hurt to use the array, but you don't gain a thing, and you have to specify which item in the array you are using each time. Just using a datatable will be easier, though only because you have to type fewer characters.

----------


## PJ17limited

Thanks Shaggy Hiker, I didn't realize the dataset was an array of datatables. I changed the dataset to a datatabale and everything is working perfectly. Oh, and yes, my aim is to delete the selected item from the database so the user can customize the list and they doesn't have to wade through a long list of items, just the ones they want in the list.

----------


## Shaggy Hiker

I was thinking that isn't a good reason to delete something, but perhaps it is. I guess I don't know what you are trying to do, and how many people this is for. Perhaps it does matter. Once it's gone from the DB, it's gone. You might consider other options. After all, how did it get in there in the first place, and why? Seems a bit drastic.

----------


## jmcilhinney

You're not explaining yourself very clearly so it's hard for us to know whether what you're doing makes sense. If you just want the user to some of the data in the database then you should just be filtering the data, either with the original query or in the application. If you delete data the way you describe then you're not just removing it from the local list but also from the database, so that data is gone forever and the user will never be able to see it again. Is that what you actually intend to happen?

----------


## schoemr

I also had this issue. To fix it I called AcceptChanges after the update was called on the dataadapter

----------


## wes4dbt

> I also had this issue. To fix it I called AcceptChanges after the update was called on the dataadapter


If you call Update on the dataadapter then all the changes have already been accepted so calling AcceptChanges doesn't do anything.

----------


## schoemr

Hi Wes, nice to see you again!  :Smilie: 

In my cause, that is really how I fixed it.... i suspected at the time that the changes was still pending.... But you are right, update don't usually requires acceptChanges


Something else I can think of is he say:


> For some reason when I delete a row in my dataset and try to repopulate the datagridview the deleted item is still being added to the datagrid even though the row is no longer in my database.


If it not in the database.... 

Then it is impossible...unless.... the DataSet is being filled with data from a different database than the one he deleting the row from. He must confirm the connection string for the dataAdapter

----------


## vbdotnut

Its not clear to me if your intension is to remove the rows from the database table or not. If you really want to delete the data then remove the rows prior to calling the adapter update. Maybe this bit of sample can help you understand better.


```
Public Class FormDT
    Dim DTable As New DataTable
    Dim Cmbox As New ComboBox
    Private Sub FormDT_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'add some sample data============================
        With DTable
            .Columns.Add(New DataColumn With {
                         .ColumnName = "Col1",
                         .DataType = GetType(String)})
            For i As Integer = 0 To 1000
                .Rows.Add("Dont find me")
            Next
            For i As Integer = 0 To 1000
                .Rows.Add("find me")
            Next
        End With

        'bind table to a datagridview====================
        DataGridView1.DataSource = DTable
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'remove rows (do this before update if you want to delete the rows from the database table
        Dim DelDTRows As DataRow() = DTable.Select(String.Format("Col1='{0}'", Cmbox.Text))
        For Each DelDTRow As DataRow In DelDTRows
            DTable.Rows.Remove(DelDTRow)
        Next

        'Filter (Doesnt remove rows, just hides them)
        DTable.DefaultView.RowFilter = String.Format("Col1='{0}'", Cmbox.Text)

    End Sub


End Class
```

----------


## wes4dbt

> In my cause, that is really how I fixed it.... i suspected at the time that the changes was still pending.... But you are right, update don't usually requires acceptChanges


The DataAdapter does have a property "AcceptChangesDuringUpdate" that by default is set to True.  I don't know what problems you were having but unless you set that property to False, calling AcceptChanges after calling Update doesn't do anything.

btw - Nice to see you again too.

----------


## jmcilhinney

> The DataAdapter does have a property "AcceptChangesDuringUpdate" that by default is set to True.  I don't know what problems you were having but unless you set that property to False, calling AcceptChanges after calling Update doesn't do anything.


The other reason you might have to explicitly call AcceptChanges is if you were to call GetChanges on a DataTable and then pass the result of that to the Update method. GetChanges returns a completely new DataTable so the data adapter will call AcceptChanges on that, which means that you have to call AcceptChanges on the original DataTable yourself. Neither of those two things is happening in this case so there should be no need to call AcceptChanges explicitly. Doing so could only be a hack as opposed to addressing the root cause of the issue, if it did anything at all.

----------

