Array value

[Godra]

I was just trying MySQL, and here is what worked for me without creating a Data Source:

Code: [Select]

    Private Sub DataSubscriber1_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataReturned
        Dim conString As String = "server=presario-c700;userid=dell;password=;database=test"
        Using con As New MySqlConnection(conString)
            Using cmd As New MySqlCommand
                With cmd
                    .Connection = con
                    .CommandText = "INSERT INTO test.datatable (value) VALUES (@value)"
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@value", e.Values(0))
                End With
                Try
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                Catch ex As MySqlException
                    MessageBox.Show(ex.Message + System.Environment.NewLine, "MySQL Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
        End Using
    End Sub

This is the configuration:
     Database = test
     Database Table = datatable
     Database Table Fields = id and value

The code above was adopted from Phrog30's other project and it did insert more than a 100 values into "value" field while "id" field was auto incremented.

If you want to try it then just correct parts that apply to your setup (like server, userid, password, database, datatable).

Attached is the picture of MySQL.

[Godra]

Here is a couple more screenshots of MySQL and this time with timestamp column, automatically populated by MySQL upon insertion of new values.

[Phrog30]

I think database stuff is fun.  I wish I knew more about, I wouldn't mind doing that stuff full time.

[MEDALI1TN]

thank you friends
It works very well
thannnnnnnnnnnnnnnnnnnnnnnnnkkkkkkkkkkkkk

Just i want to add to the value in the database some words like Kw or Volt
exmp: 100 KVA

another thing please, how I can program the writing in the database
for example I want to write the information every 5 min

all my thanks to Godra, Archie, Phrog30 .... and all the team

[Phrog30]

You have a few options,
1. Add another column and insert units.
2. Change the current column to varchar and before inserting the data, concatenate the value and unit into one string.

I'm going to say best practice is add another column so any queries will be easier to do. Just my opinion though.

The way godra showed, you can add more parameters.

[Godra]

For timed writing you could add a timer to the form, enable it and set its interval to 300000 (this is in milliseconds which equals 5 minutes).
Then double click the timer to take you back to the code, which will be its tick event, and here is where you would setup the reading of the values (your writing to the database should then be done in the DataReceived event of the opc driver).

Another possible way could be to use a timer in the PLC and set certain bit to true when 5 minutes is up.
Then you can use the DataSubscriber to observe this bit and whenever it goes true then write to the database (you would also reset the bit which should restart the timer).

[Godra]

Here is another possible way, a simple example for timed writing to MySQL database, so make sure to check the attached pictures as well:

Only standard labels are used in this example.

A DataSubscriber2 component is added to the form and it is set to observe 4 x Boolean plus 1 x FloatArray PLC addresses.
It is set to update form labels whenever data changes.

A Timer is also added to the form, set to enabled and with an interval of 10000 (which is 10 seconds).
Every 10 seconds the timer is collecting values currently displayed in the labels and writing them to the database.

Code: [Select]

    Private Sub DataSubscriber21_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber21.DataChanged
        If e.ErrorId = 0 Then
            If e.PlcAddress = "Data Type Examples.16 Bit Device.K Registers.Boolean1" Then
                Label6.Text = e.Values(0)
            ElseIf e.PlcAddress = "Data Type Examples.16 Bit Device.K Registers.Boolean2" Then
                Label7.Text = e.Values(0)
            ElseIf e.PlcAddress = "Data Type Examples.16 Bit Device.K Registers.Boolean3" Then
                Label8.Text = e.Values(0)
            ElseIf e.PlcAddress = "Data Type Examples.16 Bit Device.K Registers.Boolean4" Then
                Label9.Text = e.Values(0)
            ElseIf e.PlcAddress = "Data Type Examples.16 Bit Device.K Registers.FloatArray" Then
                Label14.Text = e.Values(0)
                Label15.Text = e.Values(1)
                Label16.Text = e.Values(2)
                Label17.Text = e.Values(3)
            End If
        End If
    End Sub

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        Dim conString As String = "server=presario-c700;userid=dell;password=;database=test"
        Using con As New MySqlConnection(conString)
            Using cmd As New MySqlCommand
                With cmd
                    .Connection = con
                    .CommandText = "INSERT INTO test.datatable (Bool1, Bool2, Bool3, Bool4, FloatArr0, FloatArr1, FloatArr2, FloatArr3) VALUES (@val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8)"
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@val1", Convert.ToInt32(CBool(Label6.Text)))
                    .Parameters.AddWithValue("@val2", Convert.ToInt32(CBool(Label7.Text)))
                    .Parameters.AddWithValue("@val3", Convert.ToInt32(CBool(Label8.Text)))
                    .Parameters.AddWithValue("@val4", Convert.ToInt32(CBool(Label9.Text)))
                    .Parameters.AddWithValue("@val5", Convert.ToSingle(Label14.Text))
                    .Parameters.AddWithValue("@val6", Convert.ToSingle(Label15.Text))
                    .Parameters.AddWithValue("@val7", Convert.ToSingle(Label16.Text))
                    .Parameters.AddWithValue("@val8", Convert.ToSingle(Label17.Text))
                End With
                Try
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                Catch ex As MySqlException
                    MessageBox.Show(ex.Message + System.Environment.NewLine, "MySQL Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
        End Using
    End Sub


[Godra]

Just for anybody who might be interested, here is a few more screenshots of how to administer MySQL server via web.

The program is called phpMyAdmin and could be installed by using XAMPP installer:
 
      https://www.apachefriends.org/index.html

For my test, during the setup I only selected Apache, PHP and phpMyAdmin (MySQL server was already installed on another computer).

Small modifications are required to be done to the config.inc.php file, related to the IP address/user/password for MySQL server.
This file is accessed by clicking the "Config" button in the XAMPP Control Panel.

The browser and the dashboard are started by clicking the "Admin" button (you have to start the Apache server prior to this).
phpMyAdmin is started from the dashboard.

[MEDALI1TN]

Thank you friends
I became an expert on reading variables and saving them in the database
And that's thanks to you.

when programming my first application I encountered some problems and all I need to insert different parameters of several tabs (which I already achieve by advanced HMI) but with conditions.
for example, inserting a variable x from a table TAB1 into a table TAB2 only if the variable Y of TAB2 equals 1.

[Phrog30]

Google is your friend, you just have to type:
https://stackoverflow.com/questions/4241621/mysql-insert-into-table-data-from-another-table

If this isn't quite what you are looking for, there are more where that came from, just look harder.  There are other sites that can help better on database stuff than here, stack overflow is one...

[M4]

Hi Ghodra/Phrog 30,
I am using Microsoft SQL data base(File) with table dbo.DataFile and trying to update Seq,Serial columns, with PLC addresses: Record_Seq and Record_Serial using basicdatalogger 2 when event changes (Number_Validated bit).
Using following code:

   Private Value1, Value2 As String
    Private Sub BasicDataLogger21_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles BasicDataLogger21.DataChanged
        If e.PlcAddress = "Number_Validated" Then
            BasicLabel1.Text = e.Values(0)
            BasicLabel2.Text = e.Values(1)
        End If
        Dim con As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Try
            con.ConnectionString = "Data Source=xxx.10.14.xxx;Initial Catalog=info;UID=sa;PWD=user"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "INSERT INTO Test(value1,value2) VALUES(@Value1,@Value2)"
           
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
        Finally
            con.Close()
        End Try

I am not pretty much sure with the code and very new to VB. Can you please help me out how to record  Seq,Serial data in database table when Number_Validated bit changes it state.

Thanks
 

[Godra]

You should be using the latest v3.99y Beta version of AHMI, since it has the BasicDataLogger2 component with the latest updates.
Or check this post here: https://www.advancedhmi.com/forum/index.php?topic=2027.msg11388#msg11388

Then read all the posts in this topic, starting with reply #45 ... it shows what you need to know in general.

I am not familiar with SQL but will suggest that you pay attention to format used in code for MySQL:

The code in replies #45 and #51, when generalized, shows as this:

 INSERT INTO databaseName.datatableName (column1name, column2name) VALUES (@Value1, @Value2)

Your database seems to be named "File" and the datatable is "dbo.DataFile" with columns "Seq" and "Serial", so the code in your case should be:

 INSERT INTO File.dbo.DataFile (Seq, Serial) VALUES (@Value1, @Value2)

and then specify the values with:

 .Parameters.AddWithValue("@Value1", e.Values(0))
 .Parameters.AddWithValue("@Value2", e.Values(1))

And also make sure to compare the format of the ConnectionString used in MySQL to the one you posted.

This is all suggested only as a general guidance.
You might try using the code posted in replies as it is, just change MySQL to SQL related entries and remove all unnecessary items.

[M4]

Thanks Godra, I got this working

[Godra]

If you would care to share your working code/solution, that might eventually help someone else.

[M4]

Sorry for late reply.

I did the same thing what Archie asked to do in https://www.advancedhmi.com/forum/index.php?topic=1864.msg10345#msg10345 reply 1. Code will be the same.

Thanks