AdvancedHMI Software
General Category => Support Questions => Topic started by: lunenburger on January 11, 2019, 10:32:38 AM
-
Hi there,
I am new to using vb.net and i am having trouble finding an example where a PLC tag value triggers an insert instruction to write to a mysql database.
What I am trying to do is write 6 PLC tag values to a mysql database when the compactlogix plc tag "CODICE_EVENTO" equals a value of 10.
I am using DataSubscriber21 to get my PLC data, if i want to write multiple PLC values into a database all at the same time and into the same row, do I need multiple DataSubscribers or can I do it with one?
When I am writing multiple PLC values to mysql, I am getting lots of zeros when there should be a PLC value. Is there a way to set the plc value in vb.net before inserting it into mysql.
Here is the code I have so far:
Imports MySql.Data.MySqlClient
Public Class MainForm
Dim MysqlConn As MySqlConnection
Dim COMMAND As MySqlCommand
'*******************************************************************************
'* Stop polling when the form is not visible in order to reduce communications
'* Copy this section of code to every new form created
'*******************************************************************************
Private NotFirstShow As Boolean
Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
'* Do not start comms on first show in case it was set to disable in design mode
If NotFirstShow Then
AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
Else
NotFirstShow = True
End If
End Sub
'***************************************************************
'* .NET does not close hidden forms, so do it here
'* to make sure forms are disposed and drivers close
'***************************************************************
Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
Dim index As Integer
While index < My.Application.OpenForms.Count
If My.Application.OpenForms(index) IsNot Me Then
My.Application.OpenForms(index).Close()
End If
index += 1
End While
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=localhost;userid=root;password=Splash123;database=icsdata"
Try
mysqlconn.Open()
MessageBox.Show("Connected")
mysqlconn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
mysqlconn.Dispose()
End Try
End Sub
Private Sub DataSubscriber21_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber21.DataChanged
Dim mytimestamp As String = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
Dim eventcode As Integer
MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=localhost;userid=root;password=Splash123;database=icsdata"
Dim reader As MySqlDataReader
If e.PlcAddress = "CODICE_EVENTO" Then
eventcode = e.Values(0)
End If
If eventcode = 10 Then
End If
Try
MysqlConn.Open()
Dim query As String
query = "insert into icsdata.run_header (Date,EventCode) values ('" & mytimestamp & "','" & eventcode & "')"
COMMAND = New MySqlCommand(query, MysqlConn)
reader = COMMAND.ExecuteReader
MessageBox.Show("Data Saved")
MysqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
MysqlConn.Dispose()
End Try
End Sub
Private Sub EthernetIPforCLXCom1_DataReceived(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles EthernetIPforCLXCom1.DataReceived
End Sub
End Class
-
The DataSubscriber2 will fire the event for each value returned. So you will need to store the values in local variables until all are received, then write to the database.
-
I would use a trigger from the PLC, when the value changes then read all of the PLC variables and write to the database.
-
Also, I would look into insert with parameters. Just search Google for mysql insert with parameters.
-
Yes, I would like to move some PLC values into the database when a tag called CODICE_EVENTO (it means event code in Italian) equals 10.
But when I use more than one tag in datasubscriber, it sometimes fills zeros into the database where the PLC data should be.
I am not sure how to store the PLC variables into vb.net local variables, would it be an = function like this:
If e.PlcAddress = "CODICE_EVENTO" Then
eventcode = e.Values(0)
End If
Dim eventcodevalue As Decimal = eventcode
Where eventcodevalue would be the local vb.net variable...
-
It sounds like you only need to monitor the one tag in a DataSubscriber, then Read the rest within the event handler.
Dim Val1 as string=EthernetIpForCLXCom1.Read("MyTag")
Dim Val2 as string=EthernetIpForCLXCom1.Read("MyTag2")
.
.
'* Write Val1, Val2, etc to the DB record.
-
That worked beautifully....
Thank you!!
Here is a copy of my new code:
Imports MySql.Data.MySqlClient
Public Class MainForm
Dim MysqlConn As MySqlConnection
Dim COMMAND As MySqlCommand
'*******************************************************************************
'* Stop polling when the form is not visible in order to reduce communications
'* Copy this section of code to every new form created
'*******************************************************************************
Private NotFirstShow As Boolean
Private Sub Form_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
'* Do not start comms on first show in case it was set to disable in design mode
If NotFirstShow Then
AdvancedHMIDrivers.Utilities.StopComsOnHidden(components, Me)
Else
NotFirstShow = True
End If
End Sub
'***************************************************************
'* .NET does not close hidden forms, so do it here
'* to make sure forms are disposed and drivers close
'***************************************************************
Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
Dim index As Integer
While index < My.Application.OpenForms.Count
If My.Application.OpenForms(index) IsNot Me Then
My.Application.OpenForms(index).Close()
End If
index += 1
End While
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=localhost;userid=root;password=Splash123;database=icsdata"
Try
mysqlconn.Open()
MessageBox.Show("Connected")
mysqlconn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
mysqlconn.Dispose()
End Try
End Sub
Private Sub DataSubscriber21_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber21.DataChanged
Dim mytimestamp As String = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
Dim eventcode As Integer
MysqlConn = New MySqlConnection
MysqlConn.ConnectionString = "server=localhost;userid=root;password=Splash123;database=icsdata"
Dim reader As MySqlDataReader
If e.PlcAddress = "CODICE_EVENTO" Then
eventcode = e.Values(0)
End If
Dim eventcodevalue As Decimal = eventcode
If eventcodevalue = 10 Then
Dim jobnumber As String = EthernetIPforCLXCom1.Read("cesti_barcode[0].Lotto")
Dim report As Decimal = EthernetIPforCLXCom1.Read("N022[40]")
Dim basket As Decimal = EthernetIPforCLXCom1.Read("N022[40]")
Dim programname As String = EthernetIPforCLXCom1.Read("Programma[17]")
Dim programnumber As String = EthernetIPforCLXCom1.Read("N022[0]")
Dim perator As String = EthernetIPforCLXCom1.Read("cesti_barcode[0].Utente")
Dim barcode As String = EthernetIPforCLXCom1.Read("cesti_barcode[0].Barcode")
Dim temperature As Decimal = EthernetIPforCLXCom1.Read("N015[220]")
Dim total As Decimal = EthernetIPforCLXCom1.Read("N014[61]")
Dim time1 As Integer = EthernetIPforCLXCom1.Read("N046[1]")
Dim time2 As Integer = EthernetIPforCLXCom1.Read("N046[21]")
Dim drip As Integer = EthernetIPforCLXCom1.Read("N046[1]")
Dim ph As Decimal = EthernetIPforCLXCom1.Read("Ph_V1_2")
Try
MysqlConn.Open()
Dim query As String
query = "insert into icsdata.run_header (Date,JobNumber,Report,Basket,ProgramName,ProgramNumber,Operator,Barcode,EventCode,Description,Temperature,TotalTime,Time1,Time2,Drip,pH) values ('" & mytimestamp & "','" & jobnumber & "','" & report & "','" & basket & "','" & programname & "','" & programnumber & "','" & perator & "','" & barcode & "','" & eventcode & "','" & "Start Cleaning Tank 1" & "','" & temperature & "','" & total & "','" & time1 & "','" & time2 & "','" & drip & "','" & ph & "')"
COMMAND = New MySqlCommand(query, MysqlConn)
reader = COMMAND.ExecuteReader
MessageBox.Show("Data Saved")
MysqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message)
Finally
MysqlConn.Dispose()
End Try
End If
End Sub
Private Sub EthernetIPforCLXCom1_DataReceived(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles EthernetIPforCLXCom1.DataReceived
End Sub
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
End Class