FT View SE 8.1 VBA code for SQL database

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 was just about to post some of the code i have done to connect to a SQL database, but i think it may be more the execution of the code:

• Have you tired copying the code and testing it from a button without the other data, to prove the command actually inserts the data into a table?
  
• Also try assigning your variables as public or global (which ever FT likes i cant remember) at the top of your code.

Give that a shot & let me know

Ash

Dear Ash,

I have checked code and working.it is reading data from PLC and insert some values to SQL table. when i tried to insert PLC's tag values to table, I am getting error. really i dont have much idea about VBA code. I feel that in my code insert is not defined well. Please could you help me best way to connect SQL and insert into tables..


Thanks alot.

Thiru.

When referring to tags, ensure you use .Value

Haha. Oops. Totally overlooked that one!!

Dear Ash,
Thanks for information . I have tried following way also but still i am getting error

If TLC1_Start.Value = True Then
objMyCmd.CommandText = "INSERT IGNORE INTO tblBatch ([Batch_No], [Product]) VALUES ( TLC1_BatchNo, [TLC1_Product]);"

Run time Error 438 and checked syntax " & TLC1_BatchNo & ", ' " & TLC1_Product & " ' still error. Could you advise me insert function format.

Thanks

Thiru

ranjithirup said:

Run time Error 438 and checked syntax " & TLC1_BatchNo & ", ' " & TLC1_Product & "

Click to expand...

Its because you still nee to use the .Value where you have used the tag references. In this case where you have TLC1_BatchNo it should be TLC1_BatchNo.Value and you will also need to format the string correctly e.g. "Text" & Tag.Value & "Text"

Heres an extract from a code i have got running:

'Combine the tag values into one data sting
dataString = "'" & dateTag.Value & "', '" & timeTag.Value & "', '" & userName & "', '" & area.Value & "', '" & machine.Value & "', '" & description.Value & "', '" & comments.Value & "', '" & actions.Value & "'"

commandString = "INSERT IGNORE INTO PPM_Log (Date, Time, UserName, Area, Machine, Description, Comments, ActionsRequired)VALUES (" & dataString & ");"




Ash

Thanks Mr.Ash

It is working fine.
Regards,
Thiru

Just on this point, Rockwell sell a product called transaction manager. It allows triggers to read or write data to a sql table.

We are looking at it for a big project on site, may be worth looking into!!

Ash

Ash,

Save yourself some headaches and steer clear of FT Transaction Manager. I've not used it myself, but from all reports it's a PITA.

The two most popular suggestions I've seen around here are the Inductive Automation SQL Bridge module (software solution) and the Oldi eATM ControlLogix module (hardware solution). I'm currently setting up the eATM module on a project and finding it very, very user friendly. With my just-enough-knowledge-to-be-dangerous understanding of SQL, I had data transfer in both directions in under an hour. But I would advise you to sit down before you look at the price of the module

Hi Ash,

Is possible Crystal Report can be done from FT View SE 8.1, If yes, could you share any sample coding.

Thanks alot..

Thiru

similar issue

Hello,
i am trying to write a script to save/change record in a sql table from a FTView se 8.20 display
I am able to read from the table and display it on the HMI but having difficulties writing to the SQL table or changing an existing record
my connection string looks ok since I am able to read from the table and I only get an error message when trying to run the code from the runtime display, when I run the code directly from the editor it runs but no effect is noticed in the data base records
also save/change - I am planning on checking the number of rows returned by the query to decide if it is a new record or an existing one if any one has a sample of a similar function in code I would appreciate it a lot

my code looks like this (this is just the writing attempt:

SQLstring = "Select * from [***].[dbo].[****] where **** like '" & TagName & "'"

'Open the connection:
rsValue.Open SQLstring, CONN, adOpenKeyset, adLockOptimistic

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set MyTagGroup = Application.CreateTagGroup(Me.AreaName)
For X = 1 To 16

MyTagGroup.Add Cname & "\" & pressname & "of\offline_step_" & X & "_time"
MyTagGroup.Active = True

Set TempTag = MyTagGroup.Item(Cname & "\" & pressname & "of\offline_step_" & X & "_time")

rsValue.Fields("REC_WORD_" & X) = TempTag.Value

Next X


thank you in advance

Script para obtenção de Dados do Sql para o FactoryTalk.

Boa tarde, estou precisando de uma ajuda.

Alguém sabe como fazer para buscar dados do Sql Server e mostrar este no meu monitor FactoryTalk?

Sabem de algum script para fazer esta leitura?

Agradeço a colaboração.