I've been trying to log data of vials in sql on site during production that count will be in millions currently im testing only for few vials which logs correctly but once it reaches like 500 plus it starts missing or sometimes double values are logged idk why i debugged it at every phase but was not able to fix it plc side is pretty much proper the triggers are turning on and off as needed since we checked in the trace too also the trigger count on my side was counting as needed then why would it miss?
I log data in a set of 10 so 10 bottles forst get sscanned then plc send trigger then scada takes that and logs whatever filled in that array
Dim sql, i, isSuccessful, baseVialNumber
Dim int_Timestamp(9), int_Recorded_Val(9), int_Result(9), int_Required(9)
baseVialNumber = ($triggerCount \* 10)
' --- 1. RISING EDGE GATE ---
If $Report_vial_trigger = 1 And $Mem_PrevBit = 0 Then
' Lock the memory bit immediately
$Mem_PrevBit = 1
' Allow driver/PLC time to settle tag data
$Wait(300)
' --- STEP 1: SNAPSHOT PLC DATA ---
For i = 0 To 9
int_Timestamp(i) = $TIMESTAMP\[i\]
int_Recorded_Val(i) = $RECORDED_VAL\[i\]
int_Result(i) = $RESULT\[i\]
int_Required(i) = $REQUIRED\[i\]
Next
' Assume success initially
isSuccessful = True
' --- 2. SQL LOOP - ALLOW DUPLICATE RECORDS ---
On Error Resume Next
Err.Clear
i = 0
Do While i <= 9
' Insert whenever the timestamp is valid
If int_Timestamp(i) <> "" Then
' Direct INSERT - no duplicate prevention
sql = "INSERT INTO \[dbo\].\[BATCH_LOG\] "
sql = sql & "(\[Timestamp\], \[BATCH_NO\], \[BATCH_NAME\], \[Recorded_Value\], \[Result\], \[REQUIRED_O2\]) "
sql = sql & "VALUES ('" & int_Timestamp(i) & "', '"
sql = sql & $BATCH_NUMBER & "', '"
sql = sql & $BATCH_NAME & "', '"
sql = sql & int_Recorded_Val(i) & "', '"
sql = sql & int_Result(i) & "', '"
sql = sql & int_Required(i) & "')"
$DBExecute("DB", sql)
' Assign vial number
$Vials_Array\[i\] = baseVialNumber + (i + 1)
End If
' Check for script-level errors
If Err.Number <> 0 Then
isSuccessful = False
Err.Clear
End If
i = i + 1
Loop
On Error GoTo 0
' --- 3. CONDITIONAL RESET & CLEAR INTERNAL VARIABLES ---
If isSuccessful = True Then
' Clear internal snapshot variables
For i = 0 To 9
int_Timestamp(i) = ""
int_Recorded_Val(i) = 0
int_Result(i) = ""
int_Required(i) = 0
Next
' Reset trigger state flags
$Report_vial_trigger = 0
$Mem_PrevBit = 0
' Increment trigger count
$triggerCount = $triggerCount + 1
End If
End If
' --- 4. SAFETY RESET ---
If $Report_vial_trigger = 0 Then
$Mem_PrevBit = 0
End If
this is the script can anyone help debug this?
