How to clear a recordset

In my project I have a typed dataset and a binding source to go with my bound controlls on a form. I have enteties in a separate project and a WCF-service to handle the data.

The Dataset is calld dsCustomer.

When I load the form I do this:

And the OK-button have this:
CustomerID = proxy.SaveNewCustomer(DSCustomer)

The customer is saved and everything is fine. Next time I open the form the old record is still in the dataset and I can't remove it. with dsCustomer.Clear and CustomerBindingSource.Clear throws an exception.

I guess I'm doing something wrong here. I'd be greatful for some help. (I run .NET Framework 4.0)

Best Regards

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Actually you can easily remove all records in a table in a Dataset. Just call this function:


Provided it is a dataset you are using.

Then bind the dataset to the form again to update it.

In the OK_button event, add a line to re-instantiate the dataset, like this:

CustomerID = proxy.SaveNewCustomer(DSCustomer)
DSCustomer = New DataSet


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ekenmanAuthor Commented:
Thanks! I've tried the above solutions without success. Perhaps it is the bindingsource that is causing the problem. The exception is index out of range.
When I open the form the second time the Identity column is -2 and not -1.
Thanks in advance
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.


The identity column, and the array indexes are two different tings. You should investigate your code to see if you are using a direct index into the row array somewhere. If the array length is 0 and you try to access the first row, it will give you that exception.
Dim strSQL As String = "DELETE FROM " & tableName
Dim cmd As New SqlCommand(strSQL, conn)
cmd.ExecuteNonQuery() 'Delete all records from table
cmd.CommandText = "DBCC CHECKIDENT (" & tableName & ", RESEED, 0)"
cmd.ExecuteNonQuery() 'reseed autoincrement to 0 preserving FK

To Ark: I dont think that he want to delete all records from his database table. It is clearing the Dataset in the form that is the question.

He still want to preserve the information in the database.
ekenmanAuthor Commented:
Yes, it is not in the database the problem lies :). Below is the form code. In the designer I've draged a details-view from my data source window which creates the dataset and bindingsource.

Public Class frmNewCustomer
    Public CustomerID As Integer
    Private Sub frmNewCustomer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub

    Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
            Dim proxy As New LS_Service.LSServiceClient
            CustomerID = proxy.SaveNewCustomer(DsCustomer)

            Me.DialogResult = Windows.Forms.DialogResult.OK
        Catch ex As Exception
            MsgBox(ex.Message & ex.StackTrace)
        End Try
    End Sub

End Class

ekenmanAuthor Commented:
OK. I figured it out eventually. I had to rebind the bindingsource after emptying the dataset.
CustomerBindingsource.DataSource = DsCustomer
CustomerBindingSource.DataMember = "Customer"
Thanks for all help.
ekenmanAuthor Commented:
It would be nice to be able to reward points to others but choose your own answer as solution. Then it is easier to find in the knowledge database.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.