asked on

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:
CustomerBindingSource.AddNew()

And the OK-button have this:
CustomerBindingSource.EndEdit()
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

Tomas

kjetilroe

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

dsCustomer.Tables(0).Rows.Clear()

Provided it is a dataset you are using.

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

ASKER CERTIFIED SOLUTION

Zhaolai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ekenman

ASKER

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

kjetilroe

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.

Ark

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

kjetilroe

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.

ekenman

ASKER

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

CustomerBindingSource.AddNew()

End Sub

Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
Try
CustomerBindingSource.EndEdit()
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
Me.Close()
End Sub

End Class

ekenman

ASKER

OK. I figured it out eventually. I had to rebind the bindingsource after emptying the dataset.
DsCustomer.Clear()
CustomerBindingsource.DataSource = DsCustomer
CustomerBindingSource.DataMember = "Customer"
CustomerBindingSource.AddNew()
Thanks for all help.
/Tomas

ekenman

ASKER

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.