Okay, I know some of you are looking at that title and thinking I'm quite daft because obviously I have to have a dataadapter if I have a bindingsource. And you're right, there is a dataadapter, but it's not exposed to me.

I've taken over a winforms project (attached to a SQL Server database) that needs to completed quickly so am trying to work with what the other developer has rather than rewriting it all. He has an admin form with a listview and a datagridview. The idea is to allow a user to edit the lookup tables for the database (customer type, product list, etc). When the form is loaded, the listview is populated (hard-coded) with a list of available tables. Clicking on a table name fills the datagridview with the contents of the table. What he left incomplete was the updating of the database if the user makes updates/inserts to a table.

Now, I know that normally you would just call the Update method of the dataadapter, but he passes all his database calls for the entire project through a helper class that will execute parameterized sql queries (for updates/inserts), or return a bindingsource object for displaying data. But this helper class does not expose the underlaying dataadapter.

To populate the datagridview, he uses the following code in the ListBox1_SelectedIndexChanged event:

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

        ' If this is the first time the list is rendered, it doens't seem to work properly
        ' This if...then circumvents errors associated with the initial population
        If Me.ListBox1.SelectedValue.ToString = "System.Data.DataRowView" Then
            Exit Sub
        End If

        ' Create array to get data
        Dim aryEditTable As New ArrayList

        ' Populate array
        aryEditTable.Add("@TableName")
        aryEditTable.Add(Me.ListBox1.SelectedValue)

        ' Create the binding source to fill the data grid
        Dim bsDataGrid As New BindingSource
        bsDataGrid = clsDB.GetRecsByParam("spFillGrid", aryEditTable)

        ' Bind the grid to the data
        Me.dgv1.DataSource = bsDataGrid

End Sub

clsDB is his helper class. As you can see, the datagridview gets populated through a bindingsource, but I am not given access to the underlying dataadapter. Now, I know I can access the underlying datatable though something like:

dim dtTemp as New DataTable = DirectCast(bsDataGrid.DataSource, DataTable)

But I don't know if I can get to the underlying adapter from there. The .GetRecsByParam method of his class is merely a wrapper that builds the parameterized query, opens a connection to the sql database, executes the query and generates/returns a bindingsource. The heart of his helper class is this function here:

        Public Shared Function GetBindingSource(ByVal cmd As SqlCommand) As BindingSource
            ' Declare the binding source
            Dim oBS As New BindingSource

            ' Create new data adapter based on the specified query
            Dim daGet As New SqlDataAdapter(cmd)

            ' Populate a new data table and bind it to the binding source
            Dim dtGet As New DataTable

            ' Set the timeout of the cmd object
            cmd.CommandTimeout = 240
            dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture

            Try
                ' Fill data table
                daGet.Fill(dtGet)
            Catch ex As Exception
                ' Check for errors
                MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error recieving data")
                Return Nothing
            End Try

            ' Set the data source for the bindingsource to the datatable
            oBS.DataSource = dtGet

            ' Return the bindignsource to the calling method/control
            Return oBS
        End Function

As you can see, the datatable and dataadapter are not persisted, instead being created each time the function is called.

The rest of the app deals with only one record at a time, so when an update is needed, he scrapes the data from the form, builds a parameter array and passes the array and the name of the stored proceedure that handles the update to a method of his helper class called .InsUpDel - which is simply another wrapper that builds a parameterized query, executes the passed stored proceedure and returns the success/fail state of update.

But, since this part of the app deals with an entire table rather than just one record, that method doesn't work. So, back to my question: How can I update the database when a change is made to datagridview when all I have is the bindingsource?

Hopefully I've been clear enough in my description, but I'd be happy to clarify or provide any more info if you feel I've jumbled the issue all up or left something out.

Hi,

Yikes! Always a nightmare taking over someone elses code especially if not documented.

You'll need to check what rows have changed and then update them individually.

So basically you'll need to compare each value that is there now with the original values then if it is different run an update query.

You could get round having to update the whole table line by line by disabling editing in the Datagrid, changing the selection to full row select, and putting an edit button under the grid.

In other words the user selects a row, clicks the edit button, you retireve the current values for that row. They edit the values in an edit panel which has been populated with the current values and a save and cancel button. If they hit cancel you hide the panel. If they save you perform run a SQL update query for that particular record. Then refresh your grid...

Not perfect, but it will save you trying to rewrite everything

You could get round having to update the whole table line by line by disabling editing in the Datagrid, changing the selection to full row select, and putting an edit button under the grid.

Now that is an excellent idea, and one I should have thought of. Got bogged down in thinking "his way" I guess. Thanks for the help, G Waddell!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.