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

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

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

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

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.

Attachment 157967

Before Jamming


Attachment 157969

After

Code:

---

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

---