How do I auto save individual form control changes to a SQL database?

[Christhemist]

I have a form that exists on a tab control and I want to automatically save any changes "TextChanged" back to the original SQL database. But I just want to save the content of that particular control and not the whole form. I need to do this for all controls on the form, whether it be a checkbox, combo box, text box, etc.

Is there a way to listen for what control is being changed, then updating the SQL database for that particular control?

Note: All controls on the form represent some field in the SQL database. Again, I only want to update the field that corresponds with the control upon any changes.

[wes4dbt]

You can use the controls "Leave" event to check if the value of the control has changed and then update the database with the new value. But I don't understand why you would want to do it. It would cause lots of unnecessary database traffic.

[Christhemist]

The request came because users were forgetting to hit the save button and losing their work.... I know right?!

I can do it the way you described, but I'm more so looking for something universal to save myself from having to script one of these functions for each control (there's a lot).

[techgnome]

You can loop through the controls collection of hte form, and through the controls collection of each container on the form (and so on - You can make a recursive function to do this) and use AddHandler to dynamicaly add a hander to each control that you want to do this to (don't forget to also do the same and RemoveHandler when you're done too).

-tg

[Christhemist]

I tried this, I believe this is what you were thinking of. However it's not recognizing any changes to the controls...

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyTab.TextChanged
        'Iterate through all controls and handle them according to their type
        For Each c As Control In Me.Controls
            If TypeOf (c) Is CheckBox Then
                AddHandler CType(c, CheckBox).CheckedChanged, AddressOf SomethingChanged
            ElseIf TypeOf (c) Is RadioButton Then
                AddHandler CType(c, RadioButton).CheckedChanged, AddressOf SomethingChanged
            ElseIf TypeOf (c) Is TextBox Then
                AddHandler CType(c, TextBox).TextChanged, AddressOf SomethingChanged
            ElseIf TypeOf (c) Is RichTextBox Then

            End If
        Next
    End Sub

[wes4dbt]

Really no need to update the database everytime a control is modified. You can save the changes when the forms leave or closing event is triggered. Sometimes I'll autosave the data each time the operator moves to a different record or Adds/Deletes a record.

I'd load the data into a DataTable, use the Datatable as the Datasource for a BindingSource, bind the controls to the Bindingsource. The bindinsource has all the methods you would need to know when any Add/Deletes/Datarow Changes happen.

[Christhemist]

I also tried that and could not get it to work... got any references I can use?

[wes4dbt]

Which part are you having trouble with?

How are you currently loading the controls?

[wes4dbt]

I got to go but here is something you could start with,

Imports System.ComponentModel
Imports System.Data.SqlClient

Public Class Form5
    Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
    Private da As New SqlDataAdapter("Select BookId, BookName From Books", con)
    Private dt As New DataTable
    Private cmdbldr As New SqlCommandBuilder(da)

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

        da.Fill(dt)

        Me.BindingSource1.DataSource = dt

        Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "BookId")
        Me.TextBox2.DataBindings.Add("Text", Me.BindingSource1, "BookName")
    End Sub

    Private Sub SaveData()
        Me.BindingSource1.EndEdit()
        da.Update(dt)
    End Sub

    Private Sub Form5_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
        SaveData()
    End Sub
End Class