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