Hi I have used the following code to populate a DataGrid based on the contents of a textbox. the users searches for a name which is populated into a combobox based on the name in the combobox several textboxes are populated with data based on the data in one of these text boxes

Private bindingSource1 As New BindingSource



Private Sub BindData()

        Dim connString As String = My.Settings.strConn
        Dim conn As New SqlConnection(connString)
        Try
            conn.Open()
            myDA = New SqlDataAdapter("SELECT t_id, firstName, lastName, rentPaid, datePaid, propRef, dayDue, rentDue FROM payments WHERE t_id = '" & TextBoxTenantIdSearchExist.Text & "'", connString)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(myDA)
            myDA.Fill(mydataset, "payments")
            bindingSource1.DataSource = mydataset.Tables("payments")
        Catch ex As Exception
            My.Computer.Audio.Play(My.Resources.femaleerror, AudioPlayMode.WaitToComplete)
            DesktopAlert1.Show()
            DesktopAlert1.CaptionText = "Sorry!"
            DesktopAlert1.ContentText = "Error: " + ex.Source + ": " + ex.Message
            bindingSource1.DataSource = Nothing
        Finally
            conn.Close()
        End Try
    End Sub

DataGridViewSearchExist.DataSource = bindingSource1


 Private Sub TextBoxTenantIdSearchExist_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBoxTenantIdSearchExist.TextChanged
        BindData()
        Label75.Text = "Total Rent Paid: " & bindingSource1.DataSource.Compute("SUM(rentPaid)", String.Empty)

    End Sub

it all works fine except if I alternate between the names it just keeps adding to the gridview is there a way I can clear the gridview before it gets populated?

see this image

[IMG]http://img189.imageshack.us/img189/3161/screenwxu.png[/IMG]

I have tried

bindingSource1.DataSource = Nothing
        DataGridViewSearchExist.DataBindings.Clear()
        DataGridViewSearchExist.Refresh()

none of those cleared the contents first I placed them here

Private Sub BindData()
        bindingSource1.DataSource = Nothing
        DataGridViewSearchExist.DataBindings.Clear()
        DataGridViewSearchExist.Refresh()

any ideas thanks

M

You are making the bindingSource1.DataSource = Nothing but not assigning to grid as source so the items are repeating. Either make the datasorce nothing and bind again, or clear the rows of grid before binding.

I have tried
VB.NET Syntax (Toggle Plain Text)

bindingSource1.DataSource = Nothing
    DataGridViewSearchExist.DataBindings.Clear()
    DataGridViewSearchExist.Refresh()

Where did you implement all these?
Apparently, you stated in your catch....block that the binding source should be set to nothing and this would only happen if the program encounters an error.

Hi I am using this to bind the data

Private Sub BindData()

        Dim connString As String = My.Settings.strConn
        Dim conn As New SqlConnection(connString)
        Try
            conn.Open()
            myDA = New SqlDataAdapter("SELECT t_id, firstName, lastName, rentPaid, datePaid, propRef, dayDue, rentDue FROM payments WHERE t_id = '" & TextBoxTenantIdSearchExist.Text & "'", connString)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(myDA)
            myDA.Fill(mydataset, "payments")

            bindingSource1.DataSource = mydataset.Tables("payments")
        Catch ex As Exception
            My.Computer.Audio.Play(My.Resources.femaleerror, AudioPlayMode.WaitToComplete)
            DesktopAlert1.Show()
            DesktopAlert1.CaptionText = "Sorry!"
            DesktopAlert1.ContentText = "Error: " + ex.Source + ": " + ex.Message
            bindingSource1.DataSource = Nothing
        Finally
            conn.Close()
        End Try
    End Sub

I am using the following to populate the datagridview

Private Sub TextBoxTenantIdSearchExist_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBoxTenantIdSearchExist.TextChanged

        BindData()
        RadLabel75.Text = "Total Rent Paid: " & bindingSource1.DataSource.Compute("SUM(rentPaid)", String.Empty)

    End Sub

in the combobox.textchanged I have

Private Sub ComboBoxSearchExist_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBoxSearchExist.SelectedIndexChanged
        bindingSource1.DataSource = Nothing
        bindingSource1.DataSource.Clear()

    End Sub

which works as long as the text is changed I tried adding the same to the combobox.selected index but it doesn't trigger when I change selection?

where am I going wrong

thanks

M

I clear the table, fill the table and let the bindingsource and binding unchanged, like that

' Clear the grid
mydataset.Tables("payments").Clear
' Fill the grid
myDA.Fill(mydataset, "payments")

Thanks for the reply I tried

mydataset.Tables("payments").Clear()
            myDA.Fill(mydataset, "payments")

            bindingSource1.DataSource = mydataset.Tables("payments")

and got this error

Object reference not set to an instance of an object.

Pgmer already gave you the answer for your problem.

Where you are doing this :

Private Sub ComboBoxSearchExist_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBoxSearchExist.SelectedIndexChanged
        bindingSource1.DataSource = Nothing
        bindingSource1.DataSource.Clear()
 
    End Sub

you are just clearing the binding source, but not rebinding the datagridview to the cleared binding source. So to correct your problem, just do this :

DataGridViewSearchExist.DataSource = Nothing

Before you call the BindData sub.

Thanks for the reply I have tried

Private Sub TextBoxTenantIdSearchExist_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBoxTenantIdSearchExist.TextChanged

        DataGridViewSearchExist.DataSource = Nothing
        BindData()

        Label75.Text = "Total Rent Paid: " & bindingSource1.DataSource.Compute("SUM(rentPaid)", String.Empty)

    End Sub

but now I don't see any data in the datagrid??

Looks like in the BindData Sub you need to add the .datamember value for the datagridview

DataGridViewSearchExist.DataSource = bindingSource1
DataGridViewSearchExist.DataMember= "payments"

That should fix the issue

This article has been dead for over six months. Start a new discussion instead.