Update of SQL Database for Data Table

[Signalman]

I currently have the code below that when executed, reads the contents fo a SQL database and displays it in a data table. This can be the edited.
Once edited, the save button is clicked, with the intention that the change is written back to the SQL database.

Public Class Test
    Dim SQLConnection As New SqlCeConnection(My.Settings.CurrentDatasource)
    Public dslog As New DataSet()
    Dim da As New SqlCeDataAdapter()
    Dim BindingSource1 As New BindingSource()

Private Sub EditOutput_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim SQLCommand As String = "SELECT * FROM ControlRecords"

        Me.WindowState = FormWindowState.Maximized
        Try
            da = New SqlCeDataAdapter(SQLCommand, SQLConnection)
            da.Fill(dslog, "Log")

        Catch sqlExc As Exception
            MessageBox.Show(sqlExc.ToString, "SQL Exception Error!",
                MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        BindingSource1.datasource = dslog.Tables("Log")
        Me.DataGridView1.DataSource = BindingSource1

        With Me.DataGridView1
            .Visible = True
            .AutoGenerateColumns = True
            .AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
            .BackColor = Color.WhiteSmoke
            .ForeColor = Color.MidnightBlue
            '.CellBorderStyle = DataGridViewCellBorderStyle.None
            .ColumnHeadersDefaultCellStyle.Font = New Font("Tahoma", 8.0!, FontStyle.Bold)
            .ColumnHeadersDefaultCellStyle.BackColor = Color.MidnightBlue
            .ColumnHeadersDefaultCellStyle.ForeColor = Color.WhiteSmoke
            .DefaultCellStyle.BackColor = Color.WhiteSmoke
            .ReadOnly = False
            .RowHeadersVisible = False
            .AllowUserToAddRows = False
            .AllowUserToDeleteRows = False
            .AllowUserToOrderColumns = True
            .AllowUserToResizeColumns = True
        End With
    End Sub

the save button executes the following

Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click

        da.Update(dslog, "Log")
end Sub

However when save is clicked an error is geenrated :-
System.InvalidOperationException: 'Update requires a valid UpdateCommand when passed DataRow Collection with modified rows.'

[wes4dbt]

A DataAdapter doesn't create Add/Update/Delete commands. You need to either manually create the objects or use a CommandBuilder, if your table has a primary key.

Here is a simple example,

Imports System.Data.SqlClient

Public Class DataAdapterDemo
    'I always store my connectionstrings in the project Setting
    Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
    Private da As New SqlDataAdapter("Select * From Books ORDER BY BookName", con)
    Private dt As New DataTable

    'I prefer using a BindingSource with the DataGridView because it has many built in function for working/manipulating the data
    Private bs As New BindingSource

    'If your only working with one table then you can use a CommandBuilder to create the SQL Add/Update/Delete commands
    Private cmdBuilder As New SqlCommandBuilder(da)

    Private Sub DataAdapterDemo_Load(sender As Object, e As EventArgs) Handles Me.Load

        Try
            con.Open()

            da.Fill(dt)
            bs.DataSource = dt
            Me.DataGridView1.DataSource = bs

            'Bind a Textbox to the bs Bindingsource
            Me.BookIdTextBox.DataBindings.Add("Text", bs, "BookId")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        Try
            bs.EndEdit()
            da.Update(dt)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub
End Class

Couple other things. If your usind a Bindingsource then you need to call Endedit method before calling the DataAdapter Update method.

"SQL database and displays it in a data table" No, the datatable is the datasource for the bindingsource, the Datagridview is what displays the data.

[Signalman]

That's really helpful. One thing that I don't understand is what adding the binding source should do.

What it appears to do is make the data grid look more like a MS Access table, when additional records can be added.

And why is it defined as a text box ? As whilst I added a text box to the form, the effect was on the data grid not the text box.

[techgnome]

Think of the BindingSource as a conductor of sorts... with the Grid and Textbox and other data elements members of the orchestra.... individually, they do their own thing... he conductor keeps them all working togtehre. The BS does the samehting. It makes sure they are all drawing all from the same data together. And no, it doesn't get defined as a textbox... a textbox was created and BOUND to it... the same way the grid was also bound to it. It was as an example to show that by changing the data in the textbox, it also updated the grid at the same time, because they are both bound to the same BindingSource. When you change the textbox, it activates an event in the BS that sends out a message to all bound object of that BS that says "Hey, I have new data, you need to refresh yourself and update it."
Otherwise, it's something you have to coordinate yourself.

-tg

[wes4dbt]

The Textbox is just there to show how to bind a textbox to a bindingsource. If you just want to just use a dgv then remove that code.

As far as I know, using a bindingsource has no effect on the "look" of the dgv.

Easy to test, change,

bs.DataSource = dt
            Me.DataGridView1.DataSource = bs

to

'bs.DataSource = dt
            Me.DataGridView1.DataSource = dt

[Signalman]

Looks like the way i was coding it was having undesired effect on the datagrid view.

In the example above, is there any advantage of using a binding source (as I can't see one)

[wes4dbt]

In the example above, is there any advantage of using a binding source (as I can't see one)

That example is as simple as it gets, it doesn't do anything but load a datatable into a dgv. If that all you want to do then a bindingsource doesn't add anything. If your going to want to do any sorting, filtering, validating..... then I'd use a bindingsource.

https://docs.microsoft.com/en-us/dot...B)%26rd%3Dtrue

[jmcilhinney]

The point of a BindingSource is to provide one point of contact for the bound data. It aggregates functionality that was spread across a number of places previously, primarily in the DefaultView of the bound DataTable and the CurrencyManager associated with the binding. If you need to access the bound data in code then I would suggest using a BindingSource and accessing the data through it. If you don't need to access the data at all in code then, as suggested, it doesn't really add anything. Personally, I always use one regardless, for the sake of consistency. Doing so doesn't hurt at all and it is there if you ever do need to access the data in code for any reason later on. BindingSources also make parent/child binding easy, e.g. if you want to select a parent record in one control and have the child data in another control filtered to only related records automatically.