AdvancedHMI Software

General Category => Open Discussion => Topic started by: Holmux on October 30, 2019, 12:17:03 PM

Title: MS SQL generate value via trigger in DB
Post by: Holmux on October 30, 2019, 12:17:03 PM

Dear forum

I am looking for some help with a task I have.
I need to collect a value from a MS SQL DB, but the value in the DB will not be generated before I execute a trigger in the DB, I received the code I need to generate the number, but not sure how to attack this in AdvancedHMI, her is the SQL code I received:

Code: [Select]

DECLARE              @return_value int,
                                @newKey nvarchar(20)

EXEC      @return_value = [dbo].[NAP_UniqueKeyGen]
                                @newKey = @newKey OUTPUT

SELECT  @newKey as N'@newKey'


How do I attack this the right way?

/Holmux

Title: Re: MS SQL generate value via trigger in DB
Post by: Archie on October 30, 2019, 12:33:19 PM

Can this be put in a Stored Procedure in the database? If so, it is very easy to create a Data Source in Visual Studio which will generate all the code for you and give you strongly typed classes.

Title: Re: MS SQL generate value via trigger in DB
Post by: Holmux on October 31, 2019, 04:30:02 AM

Hi Arthie

I enden up with something like this, not sure if this is by the book :)

Code: [Select]

Private Sub DataSubscriber23_DataChanged(sender As Object, e As Drivers.Common.PlcComEventArgs) Handles DataSubscriber23.DataChanged
        If e.ErrorId = 0 Then
            If e.Values(0) = True Then
                Using Con As New SqlConnection("Data Source=SQLDB;Initial Catalog=warehouse;Persist Security Info=True;User ID=123456;Password=**********")
                    Using cmd As New SqlCommand
                        cmd.CommandText = "dbo.KeyGen"
                        cmd.CommandType = CommandType.StoredProcedure
                        cmd.Connection = Con
                        cmd.Parameters.Add("@newKey", SqlDbType.VarChar, 20)
                        cmd.Parameters("@newKey").Direction = ParameterDirection.Output
                        If Con.State.ToString <> "Open" Then
                            Con.Open()
                        End If
                        cmd.ExecuteNonQuery()
                        Dim KeyReturn As String = cmd.Parameters("@newKey").Value
                        EthernetIPforCLXCom1.Write("Tattoo_String_50", KeyReturn)
                    End Using
                End Using
            End If
        End If
    End Sub

If any one has any corrections please let me know :)

Thanks
/Holmux