ranjithirup
Member
Hello everyone,
I'm having difficulty insert data to SQL in VBA.. I'm new to VBA and FT View SE, and any help would be greatly appreciated. The problem lies in SQL insert function when tag is 1.
The code I am working with:
Dim WithEvents Ogroup As TagGroup
Dim TLC1_BatchNo, TLC1_Truck, TLC1_Start, TLC1_Stop, TLC1_StartTime, TLC1_StopTime, TLC1_Pump, TLC1_Tank, TLC1_Product, TLC1_SP, TLC1_FT, TLC1_Name As Tag
Dim TLC2_BatchNo, TLC2_Truck, TLC2_Start, TLC2_Stop, TLC2_StartTime, TLC2_StopTime, TLC2_Pump, TLC2_Tank, TLC2_Product, TLC2_SP, TLC2_FT, TLC2_Name As Tag
Dim TLC3_BatchNo, TLC3_Truck, TLC_Start, TLC3_Stop, TLC3_StartTime, TLC3_StopTime, TLC3_Pump, TLC3_Tank, TLC3_Product, TLC3_SP, TLC3_FT, TLC3_Name As Tag
Dim TLC4_BatchNo, TLC4_Truck, TLC4_Start, TLC4_Stop, TLC4_StartTime, TLC4_StopTime, TLC4_Pump, TLC4_Tank, TLC4_Product, TLC4_SP, TLC4_FT, TLC4_Name As Tag
Dim TLC5_BatchNo, TLC5_Truck, TLC5_Start, TLC5_Stop, TLC5_StartTime, TLC5_StopTime, TLC5_Pump, TLC5_Tank, TLC5_Product, TLC5_SP, TLC5_FT, TLC5_Name As Tag
Private Sub Display_AnimationStart()
On Error Resume Next
Err.Clear
If Ogroup Is Nothing Then
Set Ogroup = Application.CreateTagGroup(Me.AreaName, 1000)
If Err.Number Then
LogDiagnosticsMessage "Error creating TagGroup. Error: " & Err.Description, ftDiagSeverityError
Exit Sub
End If
'Add PLC tags to taggroup
Ogroup.Add "[PLC1]TLC1.Batch_No"
Ogroup.Add "[PLC1]TLC1.TLC"
Ogroup.Add "[PLC1]TLC1.Product"
Ogroup.Add "[PLC1]TLC1.SetPoint"
Ogroup.Add "[PLC1]TLC1.Start"
Ogroup.Add "[PLC1]TLC1.Stop"
Ogroup.Add "[PLC1]TLC1.FT"
Ogroup.Add "[PLC1]TLC1.Pump"
Ogroup.Add "[PLC1]TLC1.Tank"
Ogroup.Add "[PLC1]TLC1.Truck_No"
Ogroup.Add "[PLC1]TLC2.Batch_No"
Ogroup.Add "[PLC1]TLC2.TLC"
Ogroup.Add "[PLC1]TLC2.Product"
Ogroup.Add "[PLC1]TLC2.SetPoint"
Ogroup.Add "[PLC1]TLC2.Start"
Ogroup.Add "[PLC1]TLC2.Stop"
Ogroup.Add "[PLC1]TLC2.FT"
Ogroup.Add "[PLC1]TLC2.Pump"
Ogroup.Add "[PLC1]TLC2.Tank"
Ogroup.Add "[PLC1]TLC2.Truck_No"
Ogroup.Add "[PLC1]TLC3.Batch_No"
Ogroup.Add "[PLC1]TLC3.TLC"
Ogroup.Add "[PLC1]TLC3.Product"
Ogroup.Add "[PLC1]TLC3.SetPoint"
Ogroup.Add "[PLC1]TLC3.Start"
Ogroup.Add "[PLC1]TLC3.Stop"
Ogroup.Add "[PLC1]TLC3.FT"
Ogroup.Add "[PLC1]TLC3.Pump"
Ogroup.Add "[PLC1]TLC3.Tank"
Ogroup.Add "[PLC1]TLC3.Truck_No"
Ogroup.Add "[PLC1]TLC4.Batch_No"
Ogroup.Add "[PLC1]TLC4.TLC"
Ogroup.Add "[PLC1]TLC4.Product"
Ogroup.Add "[PLC1]TLC4.SetPoint"
Ogroup.Add "[PLC1]TLC4.Start"
Ogroup.Add "[PLC1]TLC4.Stop"
Ogroup.Add "[PLC1]TLC4.FT"
Ogroup.Add "[PLC1]TLC4.Pump"
Ogroup.Add "[PLC1]TLC4.Tank"
Ogroup.Add "[PLC1]TLC4.Truck_No"
Ogroup.Add "[PLC1]TLC5.Batch_No"
Ogroup.Add "[PLC1]TLC5.TLC"
Ogroup.Add "[PLC1]TLC5.Product"
Ogroup.Add "[PLC1]TLC5.SetPoint"
Ogroup.Add "[PLC1]TLC5.Start"
Ogroup.Add "[PLC1]TLC5.Stop"
Ogroup.Add "[PLC1]TLC5.FT"
Ogroup.Add "[PLC1]TLC5.Pump"
Ogroup.Add "[PLC1]TLC5.Tank"
Ogroup.Add "[PLC1]TLC5.Truck_No"
Ogroup.Active = True
End If
Set TLC1_BatchNo = Ogroup.Item("{[PLC1]TLC1.Batch_No}")
Set TLC1_Truck = Ogroup.Item("{[PLC1]TLC1.Truck_No}")
Set TLC1_Start = Ogroup.Item("{[PLC1]TLC1.Start}")
Set TLC1_Stop = Ogroup.Item("{[PLC1]TLC1.Stop}")
Set TLC1_Pump = Ogroup.Item("{[PLC1]TLC1.Pump}")
Set TLC1_Tank = Ogroup.Item("{[PLC1]TLC1.Tank}")
Set TLC1_Product = Ogroup.Item("{[PLC1]TLC1.Product}")
Set TLC1_SP = Ogroup.Item("{[PLC1]TLC1.SetPoint}")
Set TLC1_FT = Ogroup.Item("{[PLC1]TLC1.FT}")
Set TLC1_Name = Ogroup.Item("{[PLC1]TLC1.TLC}")
Set TLC2_BatchNo = Ogroup.Item("{[PLC1]TLC2.Batch_No}")
Set TLC2_Truck = Ogroup.Item("{[PLC1]TLC2.Truck_No}")
Set TLC2_Start = Ogroup.Item("{[PLC1]TLC2.Start}")
Set TLC2_Stop = Ogroup.Item("{[PLC1]TLC2.Stop}")
Set TLC2_Pump = Ogroup.Item("{[PLC1]TLC2.Pump}")
Set TLC2_Tank = Ogroup.Item("{[PLC1]TLC2.Tank}")
Set TLC2_Product = Ogroup.Item("{[PLC1]TLC2.Product}")
Set TLC2_SP = Ogroup.Item("{[PLC1]TLC2.SetPoint}")
Set TLC2_FT = Ogroup.Item("{[PLC1]TLC2.FT}")
Set TLC2_Name = Ogroup.Item("{[PLC1]TLC2.TLC}")
Set TLC3_BatchNo = Ogroup.Item("{[PLC1]TLC3.Batch_No}")
Set TLC3_Truck = Ogroup.Item("{[PLC1]TLC3.Truck_No}")
Set TLC3_Start = Ogroup.Item("{[PLC1]TLC3.Start}")
Set TLC3_Stop = Ogroup.Item("{[PLC1]TLC3.Stop}")
Set TLC3_Pump = Ogroup.Item("{[PLC1]TLC3.Pump}")
Set TLC3_Tank = Ogroup.Item("{[PLC1]TLC3.Tank}")
Set TLC3_Product = Ogroup.Item("{[PLC1]TLC3.Product}")
Set TLC3_SP = Ogroup.Item("{[PLC1]TLC3.SetPoint}")
Set TLC3_FT = Ogroup.Item("{[PLC1]TLC3.FT}")
Set TLC3_Name = Ogroup.Item("{[PLC1]TLC3.TLC}")
Set TLC4_BatchNo = Ogroup.Item("{[PLC1]TLC4.Batch_No}")
Set TLC4_Truck = Ogroup.Item("{[PLC1]TLC4.Truck_No}")
Set TLC4_Start = Ogroup.Item("{[PLC1]TLC4.Start}")
Set TLC4_Stop = Ogroup.Item("{[PLC1]TLC4.Stop}")
Set TLC4_Pump = Ogroup.Item("{[PLC1]TLC4.Pump}")
Set TLC4_Tank = Ogroup.Item("{[PLC1]TLC4.Tank}")
Set TLC4_Product = Ogroup.Item("{[PLC1]TLC4.Product}")
Set TLC4_SP = Ogroup.Item("{[PLC1]TLC4.SetPoint}")
Set TLC4_FT = Ogroup.Item("{[PLC1]TLC4.FT}")
Set TLC4_Name = Ogroup.Item("{[PLC1]TLC4.TLC}")
Set TLC5_BatchNo = Ogroup.Item("{[PLC1]TLC5.Batch_No}")
Set TLC5_Truck = Ogroup.Item("{[PLC1]TLC5.Truck_No}")
Set TLC5_Start = Ogroup.Item("{[PLC1]TLC5.Start}")
Set TLC5_Stop = Ogroup.Item("{[PLC1]TLC5.Stop}")
Set TLC5_Pump = Ogroup.Item("{[PLC1]TLC5.Pump}")
Set TLC5_Tank = Ogroup.Item("{[PLC1]TLC5.Tank}")
Set TLC5_Product = Ogroup.Item("{[PLC1]TLC5.Product}")
Set TLC5_SP = Ogroup.Item("{[PLC1]TLC5.SetPoint}")
Set TLC5_FT = Ogroup.Item("{[PLC1]TLC5.FT}")
Set TLC5_Name = Ogroup.Item("{[PLC1]TLC5.TLC}")
opendbcon
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description
End Sub
Private Sub opendbcon()
'Declare variables
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordSet = New ADODB.Recordset
Dim PLCTestTag As Integer
'Open Connection
objMyConn.ConnectionString = " Provider=SQLNCLI10;Server=PLC1\SQLEXPRESS;Database=PLC1DB;Trusted_Connection=yes"
objMyConn.Open
'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn
If TLC1_Start = 1 Then
Set TLC1_StartTime = Date
objMyCmd.CommandText = "INSERT IGNORE INTO BatchTable (Batch_No, [TLC], [Product], Starttime, [Truck_No], [Tank], [Pump],Setpoint) VALUES (TLC1_BatchNo, [TLC1_Name], [TLC1_Product], [TLC1_StartTime], [TLC1_Truck], [TLC1_Tank], [TLC1_Pump], TLC1_SP);"
ElseIf TLC1_Stop.Value = True Then
Set TLC1_StopTime = Date
objMyCmd.CommandText = "IN SERT INTO BatchTable (Batch_No, [TLC], [Product], Stoptime, [Truck_No], [Tank], [Pump],Setpoint, Filledamount) VALUES (TLC1_BatchNo, [TLC1_Name], [TLC1_Product], [TLC1_StopTime], [TLC1_Truck], [TLC1_Tank], [TLC1_Pump], TLC1_SP, TLC1_FT);"
End If
If TLC2_Start = 1 Then
Set TLC2_StartTime = Date
objMyCmd.CommandText = "INSERT IGNORE INTO BatchTable (Batch_No, [TLC], [Product], Starttime, [Truck_No], [Tank], [Pump],Setpoint) VALUES (TLC2_BatchNo, [TLC2_Name],[TLC2_Product], [TLC2_StartTime], [TLC2_Truck], [TLC2_Tank], [TLC2_Pump],TLC2_SP);"
ElseIf TLC2_Stop.Value = True Then
Set TLC1_StopTime = Date
objMyCmd.CommandText = "IN SERT INTO BatchTable (Batch_No, [TLC], [Product], Stoptime, [Truck_No], [Tank], [Pump],Setpoint, Filledamount) VALUES (TLC2_BatchNo, [TLC2_Name],[TLC1_Product], [TLC1_StopTime], [TLC1_Truck], [TLC1_Tank], [TLC1_Pump], TLC2_SP, TLC2_FT);"
End If
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Close Connection
objMyConn.Close
End Sub
I'm having difficulty insert data to SQL in VBA.. I'm new to VBA and FT View SE, and any help would be greatly appreciated. The problem lies in SQL insert function when tag is 1.
The code I am working with:
Dim WithEvents Ogroup As TagGroup
Dim TLC1_BatchNo, TLC1_Truck, TLC1_Start, TLC1_Stop, TLC1_StartTime, TLC1_StopTime, TLC1_Pump, TLC1_Tank, TLC1_Product, TLC1_SP, TLC1_FT, TLC1_Name As Tag
Dim TLC2_BatchNo, TLC2_Truck, TLC2_Start, TLC2_Stop, TLC2_StartTime, TLC2_StopTime, TLC2_Pump, TLC2_Tank, TLC2_Product, TLC2_SP, TLC2_FT, TLC2_Name As Tag
Dim TLC3_BatchNo, TLC3_Truck, TLC_Start, TLC3_Stop, TLC3_StartTime, TLC3_StopTime, TLC3_Pump, TLC3_Tank, TLC3_Product, TLC3_SP, TLC3_FT, TLC3_Name As Tag
Dim TLC4_BatchNo, TLC4_Truck, TLC4_Start, TLC4_Stop, TLC4_StartTime, TLC4_StopTime, TLC4_Pump, TLC4_Tank, TLC4_Product, TLC4_SP, TLC4_FT, TLC4_Name As Tag
Dim TLC5_BatchNo, TLC5_Truck, TLC5_Start, TLC5_Stop, TLC5_StartTime, TLC5_StopTime, TLC5_Pump, TLC5_Tank, TLC5_Product, TLC5_SP, TLC5_FT, TLC5_Name As Tag
Private Sub Display_AnimationStart()
On Error Resume Next
Err.Clear
If Ogroup Is Nothing Then
Set Ogroup = Application.CreateTagGroup(Me.AreaName, 1000)
If Err.Number Then
LogDiagnosticsMessage "Error creating TagGroup. Error: " & Err.Description, ftDiagSeverityError
Exit Sub
End If
'Add PLC tags to taggroup
Ogroup.Add "[PLC1]TLC1.Batch_No"
Ogroup.Add "[PLC1]TLC1.TLC"
Ogroup.Add "[PLC1]TLC1.Product"
Ogroup.Add "[PLC1]TLC1.SetPoint"
Ogroup.Add "[PLC1]TLC1.Start"
Ogroup.Add "[PLC1]TLC1.Stop"
Ogroup.Add "[PLC1]TLC1.FT"
Ogroup.Add "[PLC1]TLC1.Pump"
Ogroup.Add "[PLC1]TLC1.Tank"
Ogroup.Add "[PLC1]TLC1.Truck_No"
Ogroup.Add "[PLC1]TLC2.Batch_No"
Ogroup.Add "[PLC1]TLC2.TLC"
Ogroup.Add "[PLC1]TLC2.Product"
Ogroup.Add "[PLC1]TLC2.SetPoint"
Ogroup.Add "[PLC1]TLC2.Start"
Ogroup.Add "[PLC1]TLC2.Stop"
Ogroup.Add "[PLC1]TLC2.FT"
Ogroup.Add "[PLC1]TLC2.Pump"
Ogroup.Add "[PLC1]TLC2.Tank"
Ogroup.Add "[PLC1]TLC2.Truck_No"
Ogroup.Add "[PLC1]TLC3.Batch_No"
Ogroup.Add "[PLC1]TLC3.TLC"
Ogroup.Add "[PLC1]TLC3.Product"
Ogroup.Add "[PLC1]TLC3.SetPoint"
Ogroup.Add "[PLC1]TLC3.Start"
Ogroup.Add "[PLC1]TLC3.Stop"
Ogroup.Add "[PLC1]TLC3.FT"
Ogroup.Add "[PLC1]TLC3.Pump"
Ogroup.Add "[PLC1]TLC3.Tank"
Ogroup.Add "[PLC1]TLC3.Truck_No"
Ogroup.Add "[PLC1]TLC4.Batch_No"
Ogroup.Add "[PLC1]TLC4.TLC"
Ogroup.Add "[PLC1]TLC4.Product"
Ogroup.Add "[PLC1]TLC4.SetPoint"
Ogroup.Add "[PLC1]TLC4.Start"
Ogroup.Add "[PLC1]TLC4.Stop"
Ogroup.Add "[PLC1]TLC4.FT"
Ogroup.Add "[PLC1]TLC4.Pump"
Ogroup.Add "[PLC1]TLC4.Tank"
Ogroup.Add "[PLC1]TLC4.Truck_No"
Ogroup.Add "[PLC1]TLC5.Batch_No"
Ogroup.Add "[PLC1]TLC5.TLC"
Ogroup.Add "[PLC1]TLC5.Product"
Ogroup.Add "[PLC1]TLC5.SetPoint"
Ogroup.Add "[PLC1]TLC5.Start"
Ogroup.Add "[PLC1]TLC5.Stop"
Ogroup.Add "[PLC1]TLC5.FT"
Ogroup.Add "[PLC1]TLC5.Pump"
Ogroup.Add "[PLC1]TLC5.Tank"
Ogroup.Add "[PLC1]TLC5.Truck_No"
Ogroup.Active = True
End If
Set TLC1_BatchNo = Ogroup.Item("{[PLC1]TLC1.Batch_No}")
Set TLC1_Truck = Ogroup.Item("{[PLC1]TLC1.Truck_No}")
Set TLC1_Start = Ogroup.Item("{[PLC1]TLC1.Start}")
Set TLC1_Stop = Ogroup.Item("{[PLC1]TLC1.Stop}")
Set TLC1_Pump = Ogroup.Item("{[PLC1]TLC1.Pump}")
Set TLC1_Tank = Ogroup.Item("{[PLC1]TLC1.Tank}")
Set TLC1_Product = Ogroup.Item("{[PLC1]TLC1.Product}")
Set TLC1_SP = Ogroup.Item("{[PLC1]TLC1.SetPoint}")
Set TLC1_FT = Ogroup.Item("{[PLC1]TLC1.FT}")
Set TLC1_Name = Ogroup.Item("{[PLC1]TLC1.TLC}")
Set TLC2_BatchNo = Ogroup.Item("{[PLC1]TLC2.Batch_No}")
Set TLC2_Truck = Ogroup.Item("{[PLC1]TLC2.Truck_No}")
Set TLC2_Start = Ogroup.Item("{[PLC1]TLC2.Start}")
Set TLC2_Stop = Ogroup.Item("{[PLC1]TLC2.Stop}")
Set TLC2_Pump = Ogroup.Item("{[PLC1]TLC2.Pump}")
Set TLC2_Tank = Ogroup.Item("{[PLC1]TLC2.Tank}")
Set TLC2_Product = Ogroup.Item("{[PLC1]TLC2.Product}")
Set TLC2_SP = Ogroup.Item("{[PLC1]TLC2.SetPoint}")
Set TLC2_FT = Ogroup.Item("{[PLC1]TLC2.FT}")
Set TLC2_Name = Ogroup.Item("{[PLC1]TLC2.TLC}")
Set TLC3_BatchNo = Ogroup.Item("{[PLC1]TLC3.Batch_No}")
Set TLC3_Truck = Ogroup.Item("{[PLC1]TLC3.Truck_No}")
Set TLC3_Start = Ogroup.Item("{[PLC1]TLC3.Start}")
Set TLC3_Stop = Ogroup.Item("{[PLC1]TLC3.Stop}")
Set TLC3_Pump = Ogroup.Item("{[PLC1]TLC3.Pump}")
Set TLC3_Tank = Ogroup.Item("{[PLC1]TLC3.Tank}")
Set TLC3_Product = Ogroup.Item("{[PLC1]TLC3.Product}")
Set TLC3_SP = Ogroup.Item("{[PLC1]TLC3.SetPoint}")
Set TLC3_FT = Ogroup.Item("{[PLC1]TLC3.FT}")
Set TLC3_Name = Ogroup.Item("{[PLC1]TLC3.TLC}")
Set TLC4_BatchNo = Ogroup.Item("{[PLC1]TLC4.Batch_No}")
Set TLC4_Truck = Ogroup.Item("{[PLC1]TLC4.Truck_No}")
Set TLC4_Start = Ogroup.Item("{[PLC1]TLC4.Start}")
Set TLC4_Stop = Ogroup.Item("{[PLC1]TLC4.Stop}")
Set TLC4_Pump = Ogroup.Item("{[PLC1]TLC4.Pump}")
Set TLC4_Tank = Ogroup.Item("{[PLC1]TLC4.Tank}")
Set TLC4_Product = Ogroup.Item("{[PLC1]TLC4.Product}")
Set TLC4_SP = Ogroup.Item("{[PLC1]TLC4.SetPoint}")
Set TLC4_FT = Ogroup.Item("{[PLC1]TLC4.FT}")
Set TLC4_Name = Ogroup.Item("{[PLC1]TLC4.TLC}")
Set TLC5_BatchNo = Ogroup.Item("{[PLC1]TLC5.Batch_No}")
Set TLC5_Truck = Ogroup.Item("{[PLC1]TLC5.Truck_No}")
Set TLC5_Start = Ogroup.Item("{[PLC1]TLC5.Start}")
Set TLC5_Stop = Ogroup.Item("{[PLC1]TLC5.Stop}")
Set TLC5_Pump = Ogroup.Item("{[PLC1]TLC5.Pump}")
Set TLC5_Tank = Ogroup.Item("{[PLC1]TLC5.Tank}")
Set TLC5_Product = Ogroup.Item("{[PLC1]TLC5.Product}")
Set TLC5_SP = Ogroup.Item("{[PLC1]TLC5.SetPoint}")
Set TLC5_FT = Ogroup.Item("{[PLC1]TLC5.FT}")
Set TLC5_Name = Ogroup.Item("{[PLC1]TLC5.TLC}")
opendbcon
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description
End Sub
Private Sub opendbcon()
'Declare variables
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordSet = New ADODB.Recordset
Dim PLCTestTag As Integer
'Open Connection
objMyConn.ConnectionString = " Provider=SQLNCLI10;Server=PLC1\SQLEXPRESS;Database=PLC1DB;Trusted_Connection=yes"
objMyConn.Open
'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn
If TLC1_Start = 1 Then
Set TLC1_StartTime = Date
objMyCmd.CommandText = "INSERT IGNORE INTO BatchTable (Batch_No, [TLC], [Product], Starttime, [Truck_No], [Tank], [Pump],Setpoint) VALUES (TLC1_BatchNo, [TLC1_Name], [TLC1_Product], [TLC1_StartTime], [TLC1_Truck], [TLC1_Tank], [TLC1_Pump], TLC1_SP);"
ElseIf TLC1_Stop.Value = True Then
Set TLC1_StopTime = Date
objMyCmd.CommandText = "IN SERT INTO BatchTable (Batch_No, [TLC], [Product], Stoptime, [Truck_No], [Tank], [Pump],Setpoint, Filledamount) VALUES (TLC1_BatchNo, [TLC1_Name], [TLC1_Product], [TLC1_StopTime], [TLC1_Truck], [TLC1_Tank], [TLC1_Pump], TLC1_SP, TLC1_FT);"
End If
If TLC2_Start = 1 Then
Set TLC2_StartTime = Date
objMyCmd.CommandText = "INSERT IGNORE INTO BatchTable (Batch_No, [TLC], [Product], Starttime, [Truck_No], [Tank], [Pump],Setpoint) VALUES (TLC2_BatchNo, [TLC2_Name],[TLC2_Product], [TLC2_StartTime], [TLC2_Truck], [TLC2_Tank], [TLC2_Pump],TLC2_SP);"
ElseIf TLC2_Stop.Value = True Then
Set TLC1_StopTime = Date
objMyCmd.CommandText = "IN SERT INTO BatchTable (Batch_No, [TLC], [Product], Stoptime, [Truck_No], [Tank], [Pump],Setpoint, Filledamount) VALUES (TLC2_BatchNo, [TLC2_Name],[TLC1_Product], [TLC1_StopTime], [TLC1_Truck], [TLC1_Tank], [TLC1_Pump], TLC2_SP, TLC2_FT);"
End If
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Close Connection
objMyConn.Close
End Sub