Move Move PLC data to MySQL when a PLC tag reaches a certain value

[lunenburger]

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

[Archie]

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.

[Phrog30]

I would use a trigger from the PLC, when the value changes then read all of the PLC variables and write to the database.

[Phrog30]

Also, I would look into insert with parameters. Just search Google for mysql insert with parameters.

[lunenburger]

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...

[Archie]

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.

[lunenburger]

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