Messages

1

Application Showcase / Re: Custom Trending with SQLite

« on: May 27, 2021, 07:12:54 AM »

I have not tried MS Power BI.  I have tried to stick with an open source stack.

2

Application Showcase / Re: Custom Trending with SQLite

« on: May 12, 2021, 09:20:46 PM »

Yes quite happy to share code.  I would just have to go back through my projects and find it.

However, I wouldn't reckon doing this, it took hours/days of code writing and not to mention the maintenance.  I have since discovered InfluxDB, Grafana and Node Red. 

InfluxDB is a purpose built time series database built for trending historical data, grafan takes out all the hard work with querying databases and it provides visually appealing dashboards.  Combined with node-red it takes roughly 5 mins to set this software stack up compared to hours/days of tinkering in Visual Basic code.

If you would still like the source code let me know.

3

Support Questions / Reading boolean arrays using DataSubscriber2

« on: January 04, 2021, 03:49:40 AM »

Hi,

Does anyone know how to read individual elements in an Boolean array using the DataSubscriber2 control?

4

Support Questions / Add extra properties to control

« on: January 02, 2021, 04:38:26 AM »

Hi all,

Does anyone know how to add 2 extra "PLCSelectBackColor" properties to the group panel control?

5

Application Showcase / Custom Trending with SQLite

« on: December 13, 2020, 11:09:44 AM »

I have created a custom dynamic trend graph that gives the operator options to change x and y axis scale.

I used a Data Subscriber to insert 3 values (pump speed, temp and current) into a SQLite database.  I then linked the chart to the SQLite database. 

See the image attached for greater detail. 

6

Open Discussion / Re: If Tag Exists

« on: November 11, 2020, 03:12:03 AM »

Would the base tag be like a prefix to the other tags? For example you would pass a base tag of "Motor1" and the tags to be check would be "Motor1Amps", "Motor1Speed", etc

How hard is it to create a group of controls e.g(motor, buttons, values) into one group that passes a base tag and uses that base as a prefix to reference other tags, similar to your example above?

I can see this function extremely helpful allowing me to build screen very quickly and to standardize all my controls. 

7

Support Questions / Re: SQLite and data subscriber

« on: October 10, 2020, 06:48:00 PM »

I put my insert statement under a one second timer tick event, which has resolved my issue.

8

Support Questions / Re: SQLite and data subscriber

« on: September 29, 2020, 03:58:13 AM »

Yes my insertion trigger is data returned which is linked to a one second modbus driver.  I tried your suggested code for no success.  I still get three rows per time stamp with incorrect data being inserted 

9

Support Questions / Re: SQLite and data subscriber

« on: September 27, 2020, 01:04:07 AM »

Yes, essentially I would like to have one row per one second time stamp that contains 3 other columns of values.  Where as currently there three rows being generated for each one second time stamp.  I am un-sure what is causing this is happen.  What are insertion trigger are you referring to?

This is the full code snippet under the data returned event handler

Code: [Select]

    Private Sub DataSubscriber21_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber21.DataReturned

        Dim tstamp As String = DateAndTime.Now
        Dim hrnow As Integer = DateAndTime.Now.TimeOfDay.Hours

        Dim p1speed As Integer
        Dim p1amps As Integer
        Dim p1temp As Integer

        If Not System.IO.File.Exists(fullname) Then
            Dim ct As String = "CREATE TABLE Trends (tstamp Datetime,p1speed Integer,p1amps Integer, p1temp Integer);"

            Using sqlconn As New SQLite.SQLiteConnection(cs)
                Dim cmd As New SQLiteCommand(ct, sqlconn)
                sqlconn.Open()
                cmd.ExecuteNonQuery()
            End Using

        End If

        If e.PlcAddress = "400201" Then
            p1speed = e.Values(0)
        End If

        If e.PlcAddress = "400202" Then
            p1amps = e.Values(0)
        End If

        If e.PlcAddress = "400103" Then
            p1temp = CInt(e.Values(0))
        End If

        Dim myconnection As New SQLiteConnection(cs)
        myconnection.Open()

        Dim cmdd As New SQLiteCommand
        cmdd.Connection = myconnection

        cmdd.Parameters.AddWithValue("@tstamp", tstamp)
        cmdd.Parameters.AddWithValue("@p1speed", p1speed)
        cmdd.Parameters.AddWithValue("@p1amps", p1amps)
        cmdd.Parameters.AddWithValue("@p1temp", p1temp)
        cmdd.CommandText = "Insert into Trends (tstamp,p1speed,p1amps,p1temp) VALUES(@tstamp,@p1speed,@p1amps,@p1temp)"

        cmdd.ExecuteNonQuery()
        myconnection.Close()

    End Sub
End Class
Can you see anything wrong with this code?

10

Support Questions / Re: SQLite and data subscriber

« on: September 26, 2020, 11:16:28 PM »

I am using Datasubsciber2 to monitor 3 tags from a plc, I then write them three values to a SQLite db with accompanied buy a timestamp column.  However when I check the db, instead of writing all three values into the same row it generates a new row for each value.  Any clue on how I get it to write all the data required to a single row, have I made an error in my code?  See attached a screenshot of the db.

Code: [Select]

Dim tstamp As String = DateAndTime.Now
        Dim hrnow As Integer = DateAndTime.Now.TimeOfDay.Hours

        Dim p1speed As String
        Dim p1amps As Integer
        Dim p1temp As Integer

        If Not System.IO.File.Exists(fullname) Then
            Dim ct As String = "CREATE TABLE Trends (tstamp Text,p1speed Integer,p1amps Integer, p1temp Integer);"

            Using sqlconn As New SQLite.SQLiteConnection(cs)
                Dim cmd As New SQLiteCommand(ct, sqlconn)
                sqlconn.Open()
                cmd.ExecuteNonQuery()
            End Using

        End If

        If e.PlcAddress = "400201" Then
            p1speed = e.Values(0)
        End If

        If e.PlcAddress = "400202" Then
            p1amps = e.Values(0)
        End If

        If e.PlcAddress = "400103" Then
            p1temp = CInt(e.Values(0))
        End If

        Dim myconnection As New SQLiteConnection(cs)
        myconnection.Open()

        Dim cmdd As New SQLiteCommand
        cmdd.Connection = myconnection

        cmdd.CommandText = "Insert into Trends (tstamp,p1speed,p1amps,p1temp) VALUES(@tstamp,@p1speed,@p1amps,@p1temp)"
        cmdd.Parameters.AddWithValue("@tstamp", tstamp)
        cmdd.Parameters.AddWithValue("@p1speed", p1speed)
        cmdd.Parameters.AddWithValue("@p1amps", p1amps)
        cmdd.Parameters.AddWithValue("@p1temp", p1temp)

        cmdd.ExecuteNonQuery()
        myconnection.Close()


11

Support Questions / Re: SQLite and data subscriber

« on: September 13, 2020, 05:21:29 AM »

I re-typed my code and fixed the issue. My chart is now doing working correctly.  Thanks for you help!

12

Support Questions / Re: SQLite and data subscriber

« on: August 22, 2020, 05:40:42 AM »

Essentially I have 52 data base entries week (x value) and pump run hours (y value) so I wish to have 52 columns on my graph.  However when I load the graph I only have three columns.   

This is my rough testing code. Can anyone see where I am going wrong?

Code: [Select]

        Dim myconnection As New SQLiteConnection("Data Source=C:\test.db")
        myconnection.Open()

        Dim cmd As New SQLiteCommand
        cmd.Connection = myconnection

        cmd.CommandText = "Select * from test01"

        Dim rdr As SQLiteDataReader = cmd.ExecuteReader()

        Dim dt As New DataTable

        dt.Load(rdr)
        rdr.Close()

        Dim dat As Integer
        dt.Rows.Item(0)("date") = CInt(Dat)

        Dim inte As Integer
        dt.Rows.Item(0)("int") = CInt(inte)


        Chart1.Series(0).XValueMember = Dat
        Chart1.Series(0).YValueMembers = inte
        Chart1.ChartAreas(0).AxisX.Minimum = 1
        Chart1.ChartAreas(0).AxisX.Maximum = 52
        Chart1.DataSource = dt


13

Support Questions / Re: SQLite and data subscriber

« on: August 20, 2020, 10:50:45 PM »

I have managed to write the data from the data subscriber into a SQLite database.  I found a very helpful video that explains this step.

https://youtu.be/QFT4cBXZ0kU

Now I wish to graph the two columns in the data base (one column for the x value and one column for the y value).  How do I port the data from the SQLite database into a chart control? 

14

Support Questions / SQLite and data subscriber

« on: August 10, 2020, 03:40:39 AM »

Hi guys,

I am wanting to like a data subscriber to SQLite.  Has anyone done this? Or know how to link a data subscriber to a SQLite data base.

Thanks.