MS SQL database Questions, I'm completely new to VB

[ScottM]

Hello team,   
Love the project and am having a blast with it..

I have created a display with some live data in fields and a couple trending tables, all live..

I am wanting to get 4 bits logged in to a MS SQL server every hour. I have followed a video I found on YouTube and am able to log 1 tag from the PLC every 5 seconds no problem but I need every hour witch I think can do with a GSV and trigger bit from my CLX plc, I have now added more columns to my DB and need to get data to them. Would I need to do multiple datasubscribers or can it all be completed in 1?
Can someone please help with the code?

[Archie]

The DataSubscriber will only monitor a single address. However the DataSubscriber2 allows multiple address, but requires code in the DataReveived event in order to see what address the value belongs to. There are many examples on the forum showing how to use e.PLCAddress , such as this

https://www.advancedhmi.com/forum/index.php?topic=2291

[ScottM]

Thanks so much for the reply, Ill try installing Mysql instead of MS SQL as most the sample code I find does not use MS SQL.
But I may just need to have my customer purchase something like Ignition as they need a reliable tool for keeping water table records..

[Archie]

There is probably not much difference between MySQL and MS SQL. I prefer MSSQL simply because the integration into VS is cleaner.

[boardmaker]

If you go with mysql, here is a video tutorial on youtube.

https://www.youtube.com/watch?v=XMLAXQR_YFI&list=PLS1QulWo1RIZxHuINmI3EIAjCq3zzsiFy&index=1

[ScottM]

ok so I have some data recording in to MS SQL but my issue is now that my database datetime column stopped populating in the server. I have an auto index and a datetime field that was populating for the first 147 entries Index still works fine.

 Is there a way I can force a datetime from the code?

    Private t As New TestDBDataSet.ShepleyDataTable
    Private Sub DataSubscriber1_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataReturned
        If t.Count = 0 Then
            t.AddShepleyRow(t.NewRow)
        End If

        t(0).BH1 = e.Values(0)

        If Not t(0).IsBH1Null And Not t(0).IsBH2Null And Not t(0).IsBH3Null Then
            Using ta As New TestDBDataSetTableAdapters.ShepleyTableAdapter
                ta.Update(t)
                t.Clear()
            End Using
        End If


    End Sub

    Private Sub DataSubscriber2_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber2.DataReturned
        If t.Count = 0 Then
            t.AddShepleyRow(t.NewRow)
        End If

        t(0).BH2 = e.Values(0)

        If Not t(0).IsBH1Null And Not t(0).IsBH2Null And Not t(0).IsBH3Null Then
            Using ta As New TestDBDataSetTableAdapters.ShepleyTableAdapter
                ta.Update(t)
                t.Clear()
            End Using
        End If

    End Sub

    Private Sub DataSubscriber3_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber3.DataReturned
        If t.Count = 0 Then
            t.AddShepleyRow(t.NewRow)
        End If

        t(0).BH3 = e.Values(0)

        If Not t(0).IsBH1Null And Not t(0).IsBH2Null And Not t(0).IsBH3Null Then
            Using ta As New TestDBDataSetTableAdapters.ShepleyTableAdapter
                ta.Update(t)
                t.Clear()
            End Using
        End If

    End Sub



I created my DB with this code and as i said it was populating then stopped..

CREATE TABLE Shepley (
    ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    DataDateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
    BH1 nchar(10),
    BH2 Nchar(10),
    BH3 nchar(10),
    BH4 Nchar(10),
    BH5 nchar(10),
    BH6 Nchar(10),
    BH7 nchar(10),
    BH8 Nchar(10),
    BH9 nchar(10),
    BH10 Nchar(10),
    BH11 nchar(10),
    BH12 Nchar(10),
    BH13 nchar(10),
    BH14 Nchar(10),
    BH15 nchar(10),
    BH16 Nchar(10),
);



147   2022-03-16 13:10:22.813   29.445       27.86        21.962        NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL

148   NULL   26.445       27.8         22.125        NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL   NULL

[ScottM]

Ok so I was able to make it work by creating the system time in to a string then writing it to the DB.

How would I be able to make all this trigger off an input instead of datasubscriber and driver timer? I have a 30 minute timer in the PLC with a tag just need help getting the 4 items to log off of the 1 timer.

Thanks in advance.

[boardmaker]

Did you use Mysql?

If so something like this would work:

 Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        MysqlConn = New MySqlConnection
        MysqlConn.ConnectionString = "server=localhost;userid=root;password=password;database=databasename"
        Dim Reader As MySqlDataReader

        Try
            MysqlConn.Open()
            Dim Query As String
            Dim regDate As DateTime = DateTime.Now
            Dim strDate As String = regDate.ToString("yyyy-MM-dd HH:mm:ss")

            Query = "insert into databasename.tablename (tag1columnname, tag2columnname, tag3columnname) values ('" & "tag1" & "','" & tag2 & "','" & tag3 &"')"
            Command = New MySqlCommand(Query, MysqlConn)
            Reader = Command.ExecuteReader

            MysqlConn.Close()


        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            MysqlConn.Dispose()

        End Try
    End Sub

[Archie]

Ok so I was able to make it work by creating the system time in to a string then writing it to the DB.

In MS SQL, you can have the DB populate a time stamp by setting the default value for a date field as shown in the attached image

[ScottM]

Thanks so much for all the help.. Loving the software so far..

[ScottM]

Sorry to be a pest here, I have looked at all the links and read a lot of posts but I still cant make heads or tails of the datasubscriber2 code for MS SQL. I was wondering if someone could help me out and convert the below code to a datasubscriber2, My trigger bit will be BH_Mon_Bit[20] and will fire all 3 at the same time if possible..
Thanks for looking,

Private t As New TestDBDataSet.ShepleyDataTable
    Private Sub DataSubscriber1_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber1.DataReturned
        If t.Count = 0 Then
            t.AddShepleyRow(t.NewRow)
        End If

        t(0).BH1 = e.Values(0)

        If Not t(0).IsBH1Null And Not t(0).IsBH2Null And Not t(0).IsBH3Null Then
            Using ta As New TestDBDataSetTableAdapters.ShepleyTableAdapter
                ta.Update(t)
                t.Clear()
            End Using
        End If

    End Sub

    Private Sub DataSubscriber2_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber2.DataReturned
        If t.Count = 0 Then
            t.AddShepleyRow(t.NewRow)
        End If

        t(0).BH2 = e.Values(0)

        If Not t(0).IsBH1Null And Not t(0).IsBH2Null And Not t(0).IsBH3Null Then
            Using ta As New TestDBDataSetTableAdapters.ShepleyTableAdapter
                ta.Update(t)
                t.Clear()
            End Using
        End If

    End Sub

    Private Sub DataSubscriber3_DataReturned(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber3.DataReturned
        If t.Count = 0 Then
            t.AddShepleyRow(t.NewRow)
        End If

        t(0).BH3 = e.Values(0)

        If Not t(0).IsBH1Null And Not t(0).IsBH2Null And Not t(0).IsBH3Null Then
            Using ta As New TestDBDataSetTableAdapters.ShepleyTableAdapter
                ta.Update(t)
                t.Clear()
            End Using
        End If

    End Sub