troubleshooting Question

How to stop duplicates being written from VB6 code to SQL Table

Avatar of Latzi_Marian
Latzi_MarianFlag for Australia asked on
Visual Basic Classic
9 Comments1 Solution442 ViewsLast Modified:
I have a project where I have to write to an SQL database some values (32 values) .Here's the routine which does the job:

Private Sub Write_To_SQL()
    Dim Var
    Dim sDate As String
    Dim Connection, RS
    Dim rsField, iFieldCount
    Dim SQLStmt As String
    Dim sstring As String
    sDate = Format(DateTime.Now, vbGeneralDate)
    Set Connection = CreateObject("ADODB.Connection")
    Set RS = New ADODB.Recordset
    Connection.Open "driver=SQL Server;server=(local);uid=;pwd=;database=DATA_LOG;"

    SQLStmt = "INSERT INTO    Recipes_West_Table   " & _
    "Values( '" & Label4.Caption & "' ," & Form1.txtVal1.Text & "," & Label3.Caption & ")"
    Set RS = Connection.Execute(SQLStmt)
    Var = 1
End Sub

No problems it works if I trigger the subroutine from a button.Like
Private Sub Command1_Click()
End Sub
But unfortunately I have to trigger the Write_To_SQL subroutine from a textbox values.Namely when
IF Form1.Text1.Text = "1" then
Write to SQL
End If
The problem is that the Text.Text stays 1 for various lenghts of time.So To avoid triggering twice the Wrtite_To_SQL routine I have to put some conditions like if the Write_To_SQL is executed and Form1.Text1.Text hasn't done a TRANSITION from Text1.Text = " 1"  to Text1.Text = "0" and then AGAIN to Text1.Text = "1" (This is the point I have to write to SQL again) ,then don't trigger the routine.I've tried all sorts of tricks trying to build some "flip/flop" to detect transitions of Text1.Text from 1 to 0 to 1 --> write.I just don't know what are the best practices to do this kind of thing.Please help if someone got an ideea.Thanx
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros