Results 1 to 5 of 5

Thread: Textbox connected to SQL Server database does not update when the value is changed

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Textbox connected to SQL Server database does not update when the value is changed

    Hi,

    I have a textbox connected to a database in SQL Server 2014 using the following code:
    Code:
    Set frmMain.TxtCustomer.DataSource = CurJobRecord
    frmMain.TxtCustomer.DataField = "Customer"
    If the user changes the text in TxtCustomer and then the user presses a command button which runs a CurJobRecord.update command, the value in the database is updated fine. However when the value in TxtCustomer is updated from code and the recordset is updated from code, the value fails to update in the database.
    Code:
    TxtCustomer.Text = "Test"
    CurJobRecord.Update
    This is the code which originally opens the CurJobRecord Recordset
    Code:
    Set TempRS = New ADODB.Recordset
    
        TempRS.Open SQL, ConJobData, adOpenDynamic, adLockOptimistic
        If TempRS.EOF = False Then
            If CurJobRecord.State = adStateOpen Then
                CurJobRecord.Update
                CurJobRecord.Close
            End If
        
            Set CurJobRecord = TempRS
    If anybody has any solutions as to why this is happening it would be appreciated.

    Thanks for your time,

    Hustey

  2. #2
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,068

    Re: Textbox connected to SQL Server database does not update when the value is change

    IMO, using data control bindings is a bad habit, which makes things hard to control. However, Olaf and dilettante wrote some very good examples of data control binding.

    http://www.vbforums.com/showthread.p...nly-RowPicker)

    http://www.vbforums.com/showthread.p...=1#post5214535

    http://www.vbforums.com/showthread.p...=1#post5214841

    http://www.vbforums.com/showthread.p...out-closing-it

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    24

    Re: Textbox connected to SQL Server database does not update when the value is change

    When you say "data control bindings is a bad habit", how would you suggest doing it instead? Sorry I'm fairly new to database programming with vb6.


    Hustey

  4. #4
    PowerPoster
    Join Date
    Sep 2012
    Posts
    2,068

    Re: Textbox connected to SQL Server database does not update when the value is change

    Form1(with TxtID, TxtName and CmdSave):

    Code:
    Option Explicit
    
    Private m_bDataChanged As Boolean
    
    Private m_sCustomerID As String     '--- Primary Key ---
    
    Private m_Cnn As ADODB.Connection
    
    Private Sub Form_Load()
    
        OpenDBConnection
        
        LoadDataFromDB
        
    End Sub
    
    Private Sub LoadDataFromDB()
        Dim sSQL As String:     Dim adoRs As ADODB.Recordset
        
        On Error GoTo ErrLoad
        
        Call ClearData
        
        sSQL = "Select * From YourTable Where CustomerID = '" & m_sCustomerID & "'"
        
        Set adoRs = New ADODB.Recordset
        adoRs.Open sSQL, m_Cnn, adOpenStatic, adLockReadOnly
        
        With adoRs
            If .EOF = False Then
                TxtID.Text = .Fields("CustomerID")
                TxtName.Text = .Fields("CustomerName") & vbNullString       '--- vbNullString("") can eliminate field null errors
            End If
            .Close
        End With
        
        m_bDataChanged = False          '--- Note: This line is very important
        Exit Sub
        
    ErrLoad:
        MsgBox "Loading data from database failed !" & vbCrLf & vbCrLf & Error
        m_bDataChanged = False          '--- Note: This line is very important
    End Sub
    
    Private Sub OpenDBConnection()
        '--- Open your SQLServer database connection ---
        
        'Set m_Cnn = ...
        
    End Sub
    
    Private Sub ClearData()
        TxtID.Text = ""
        TxtName.Text = ""
    End Sub
    
    Private Sub TxtID_Change()
        m_bDataChanged = True
    End Sub
    
    Private Sub TxtName_Change()
        m_bDataChanged = True
    End Sub
    
    Private Sub CmdSave_Click()
        SaveDataToDB
    End Sub
    
    Private Sub SaveDataToDB()
        If m_bDataChanged = False Then Exit Sub
        If ValidCheck() = False Then Exit Sub
        
        Dim sSQL As String:     Dim adoRs As ADODB.Recordset
        
        On Error GoTo ErrSave
        
        sSQL = "Select * From YourTable Where CustomerID = '" & m_sCustomerID & "'"
        
        Set adoRs = New ADODB.Recordset
        adoRs.Open sSQL, m_Cnn, adOpenKeyset, adLockOptimistic
        
        With adoRs
            If .EOF = True Then
                .AddNew
            End If
            
            .Fields("CustomerID") = TxtID.Text
            .Fields("CustomerName") = TxtName.Text
            
            .Update
            .Close
        End With
        
        m_bDataChanged = False          '--- Note: This line is very important
        Exit Sub
        
    ErrSave:
        MsgBox "Saving data to database failed !" & vbCrLf & vbCrLf & Error
    End Sub
    
    Private Function ValidCheck() As Boolean
    
        '--- Validation chek for the TextBox-Fields ---
        
        TxtID.Text = Trim(TxtID.Text)
        TxtName.Text = Trim(TxtName.Text)
        
        If InStr(TxtID.Text, "'") <> 0 Then
            MsgBox "Invalid character in Customer-ID field !"
            Exit Function
        End If
        
        If InStr(TxtName.Text, "'") <> 0 Then
            MsgBox "Invalid character in Customer-Name field !"
            Exit Function
        End If
        
        '--- Other validation check ---
        
        ValidCheck = True
        
    End Function
    Note: The above code hasn't been tested, it is just an example.

    Edit:

    A little bit of optimization to the original code
    Last edited by dreammanor; Apr 4th, 2018 at 11:57 PM.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    21,038

    Re: Textbox connected to SQL Server database does not update when the value is change

    If you are going to do this sort of thing you need to disconnect the control and then reconnect it afterward.

    Here is a demo that uses a Jet MDB (easy for anyone to test). It will create a new database if one isn't present.


    In this demo I added a button to "jam in" a Month = 13 for the current record.


    Name:  sshot1.png
Views: 113
Size:  5.7 KB

    Before Jamming


    Name:  sshot2.png
Views: 113
Size:  5.8 KB

    After


    Code:
    Private Sub cmdJamItIn_Click()
        Bindings = False
        With RSData
            ![Month].Value = 13
            .Update
        End With
        Bindings = True
    End Sub
    Attached Files Attached Files

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
  •  



Click Here to Expand Forum to Full Width