dcsimg
Results 1 to 9 of 9

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

  1. #1

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    100

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

    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.
    Last edited by Christhemist; Dec 13th, 2019 at 01:41 PM.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,449

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

    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.

  3. #3

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    100

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

    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).

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,796

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    100

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

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

    Code:
     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

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,449

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

    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.

  7. #7

    Thread Starter
    Lively Member Christhemist's Avatar
    Join Date
    Sep 2016
    Location
    Nevada
    Posts
    100

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

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

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,449

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

    Which part are you having trouble with?

    How are you currently loading the controls?

  9. #9
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,449

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

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

    Code:
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width