Insert/Update data into tables in SQL Server Express via FTView SE

drbitboy · Mar 5, 2021

FFP said:
Thanks a lot....Code function ok but don't save in database....I guess that TOTAL_LITROS_RECEBIDO is a integer field but in update code the value it's in quotes (')...

yes, as I noted I put ticks around everything, but they are not needed for numbers of course, although I expect SQL will make the conversion.

Also, as @parky notes the NULL check should be done like this:

Code:

& space & "DT_FIM_RECEBIMENTO IS NULL" [B][COLOR=Blue]_[/COLOR][/B]

There is a huge difference to between [the_field = ''] and [the_field IS NULL]: with the former, SQL does not find any records where the_field is NULL, and so no records are updated.

By the way, this all assumes there is a record where both statements:

DT_FIM_RECEBIMENTO IS NULL,
NR_RECEPCAO = 1

are true, but your first image indicates that is indeed the case.

drbitboy · Mar 5, 2021

parky said:
I think the "EMPTY" bit may be the problem

DT_FIM_RECEBIMENTO = " & aaaEmpty _
I have never heard of using this it is normally
DT_FIM_RECEBIMENTO IS NULL
However, I tried to recreate this but I found that the "DATE" & "TIME" ones did not work as passing them, had to create them as text, for example I tried DATE
I.e. Format (date, "YYYY-MM-DD")
This did not work
I think it is not trying to populate the fields because of the check for Empty, remove this to start with but I think then it will fail as I believe the DATE & TIME ones will fail.
Can you post a screenshot of the table layout i.e. the fields & their data types

Can you show the table definition/declaration? Similar to the [describe name_of_table] statement in MysQL?

It looks like Date and Time may be internal values, not strings; try summat like this:

Cf. https://www.sqlshack.com/sql-server-functions-for-converting-string-to-date/

Summat similar should be possible for time.

FFP · Mar 5, 2021

I guess Date and Time is not a problem because in Insert code it works ok....Strange is that in show message the SQL line code its ok

All the code is:

Dim dataString As String
Dim SQLStr As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Dim Ogroup As TagGroup
Dim Tag_Placa, Tag_Cod_Coleta, Tag_Silo_Destino, Tag_Nr_Lote_C, Tag_Nr_Lote_S, Tag_Tot_Litros, Nr_RCP, Tag_Save_RCP, Tag_Updt_RCP_1, Tag_Updt_RCP_2, Tag_Updt_RCP_3 As Tag

tick = "'"
aaaEmpty = tick & tick
comma = ","
aSpace = " "
semic = ";"
comsp = comma & aSpace

Set Ogroup = Application.CreateTagGroup(A, 1000)
Ogroup.Add "{[PLC_BETANIA]RCP.PLACA}"
Ogroup.Add "{[PLC_BETANIA]RCP.COD_COLETA}"
Ogroup.Add "{[PLC_BETANIA]RCP.SILO_DESTINO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_SILO}"
Ogroup.Add "{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_RCP}"
Ogroup.Add "{[PLC_BETANIA]Save_RCP}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_1}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_2}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_3}"

Set Tag_Placa = Ogroup.Item("{[PLC_BETANIA]RCP.PLACA}")
Set Tag_Cod_Coleta = Ogroup.Item("{[PLC_BETANIA]RCP.COD_COLETA}")
Set Tag_Silo_Destino = Ogroup.Item("{[PLC_BETANIA]RCP.SILO_DESTINO}")
Set Tag_Nr_Lote_C = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}")
Set Tag_Nr_Lote_S = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_SILO}")
Set Tag_Tot_Litros = Ogroup.Item("{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}")
Set Tag_Nr_RCP = Ogroup.Item("{[PLC_BETANIA]RCP.NR_RCP}")
Set Tag_Save_RCP = Ogroup.Item("{[PLC_BETANIA]Save_RCP}")
Set Tag_Updt_RCP_1 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_1}")
Set Tag_Updt_RCP_2 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_2}")
Set Tag_Updt_RCP_3 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_3}")

If Tag_Save_RCP.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
dataString = "'" & Date & "','" & Time & "','" & Tag_Placa.Value & "','" & Tag_Cod_Coleta.Value & "','" & Tag_Silo_Destino.Value & "','" & Tag_Nr_Lote_C.Value & "','" & Tag_Nr_Lote_S.Value & "','" & Tag_Nr_RCP.Value & "'"
SQLStr = " Insert into TBL_RCP_SETPOINT (DT_INICIO_RECEBIMENTO, HR_INICIO_RECEBIMENTO, PLACA, COD_COLETA, SILO_DESTINO, NR_LOTE_CAMINHAO, NR_LOTE_SILO, NR_RECEPCAO) values (" & dataString & ");"
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

If Tag_Updt_RCP_1.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
SQLStr = "Update TBL_RCP_SETPOINT" & aSpace & "SET" & aSpace & "TOTAL_LITROS_RECEBIDO = " & tick & Tag_Tot_Litros.Value & tick _
& comsp & "DT_FIM_RECEBIMENTO = " & tick & Date & tick _
& comsp & "HR_FIM_RECEBIMENTO = " & tick & Time & tick _
& aSpace & "WHERE" _
& aSpace & "DT_FIM_RECEBIMENTO = " & aaaEmpty _
& aSpace & "AND" _
& aSpace & "NR_RECEPCAO = 1" _
& semic

MsgBox (SQLStr)

Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

The table is:

CREATE TABLE [dbo].[TBL_RCP_SETPOINT](
[DT_INICIO_RECEBIMENTO] [date] NULL,
[HR_INICIO_RECEBIMENTO] [time](7) NULL,
[PLACA] [varchar](12) NULL,
[COD_COLETA] [int] NULL,
[SILO_DESTINO] [int] NULL,
[NR_LOTE_CAMINHAO] [varchar](50) NULL,
[NR_LOTE_SILO] [varchar](50) NULL,
[TOTAL_LITROS_RECEBIDO] [int] NULL,
[DT_FIM_RECEBIMENTO] [date] NULL,
[HR_FIM_RECEBIMENTO] [time](7) NULL,
[NR_RECEPCAO] [int] NULL
)

drbitboy · Mar 5, 2021

Is it possible that '05/03/21' is being interpreted as '2021-05-03' (03.May i.e. May third) instead of 2021-03-05 (05.Mar, 2021 i.e. March fifth i.e. today), or vice versa?

parky · Mar 5, 2021

Right This is how I got it to work
First I declared the variables as I did not have any
Notice the date & time need a ' either side of the format date or time (it is difficult to see the ' as it's in speech marks)
Dim Tag_Tot_Litros As Double
Dim My_Date As Variant
Dim My_Time As Variant
Dim My_Tag As Integer
Tag_Tot_Litros = 123.04
My_Date = "'" + Format (Date,"YYYY-MM-DD")+ "'"
My_Time ="'" + Format(Now, "HH:MM:SS") + "'"
My_Tag = 1

This is the SQL string
SQLStr = "UPDATE TBL_RCP_SETPOINT Set TOTAL_LITROS_RECEBIDO = " & Tag_Tot_Litros & ", DT_FIM_RECEBIMENTO = " & My_Date & ",HR_FIM_RECEBIMENTO = " & My_Time & " where DT_FIM_RECEBIMENTO IS NULL AND NR_RECEPCAO = " & My_Tag &"

parky · Mar 5, 2021

Here is the result

drbitboy · Mar 5, 2021

parky said:
(it is difficult to see the ' as it's in speech marks)

This is why I use the string variable [tick]; clarity is everything when debugging.

FFP · Mar 5, 2021

I guess not because in insert code the system insert line in the same way of update....Date (in code vba) is accepted in database

parky · Mar 5, 2021

I suppose it depends on how the VBA handles the Date format etc. My code was done on a Scada system where for some reason DATE does not work when populating the table, so had to format it in the way I did. Anyway I think you need to add the IS Null, then try again because I believe the reason the code works is the where clause is not correct so it runs the code but as the check for a null does not find one it skips so does not populate the table or create an error.

FFP · Mar 5, 2021

I copied exactly equal....Error showed below

Run-time error '438':
Object doesn't support this property or method

Answer for:

Right This is how I got it to work
First I declared the variables as I did not have any
Notice the date & time need a ' either side of the format date or time (it is difficult to see the ' as it's in speech marks)
Dim Tag_Tot_Litros As Double
Dim My_Date As Variant
Dim My_Time As Variant
Dim My_Tag As Integer
Tag_Tot_Litros = 123.04
My_Date = "'" + Format (Date,"YYYY-MM-DD")+ "'"
My_Time ="'" + Format(Now, "HH:MM:SS") + "'"
My_Tag = 1

This is the SQL string
SQLStr = "UPDATE TBL_RCP_SETPOINT Set TOTAL_LITROS_RECEBIDO = " & Tag_Tot_Litros & ", DT_FIM_RECEBIMENTO = " & My_Date & ",HR_FIM_RECEBIMENTO = " & My_Time & " where DT_FIM_RECEBIMENTO IS NULL AND NR_RECEPCAO = " & My_Tag &"

FFP · Mar 5, 2021

Thanks Guys.....the complete code worked only I changed in where DT_FIM_RECEBIMENTO=' ' for DT_FIM_RECEBIMENTO IS NULL....
The complete code below:

Dim dataString As String
Dim SQLStr As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Dim Ogroup As TagGroup
Dim Tag_Placa, Tag_Cod_Coleta, Tag_Silo_Destino, Tag_Nr_Lote_C, Tag_Nr_Lote_S, Tag_Tot_Litros, Nr_RCP, Tag_Save_RCP, Tag_Updt_RCP_1, Tag_Updt_RCP_2, Tag_Updt_RCP_3 As Tag

tick = "'"
aaaEmpty = tick & tick
comma = ","
aSpace = " "
semic = ";"
comsp = comma & aSpace

Set Ogroup = Application.CreateTagGroup(A, 1000)
Ogroup.Add "{[PLC_BETANIA]RCP.PLACA}"
Ogroup.Add "{[PLC_BETANIA]RCP.COD_COLETA}"
Ogroup.Add "{[PLC_BETANIA]RCP.SILO_DESTINO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_SILO}"
Ogroup.Add "{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_RCP}"
Ogroup.Add "{[PLC_BETANIA]Save_RCP}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_1}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_2}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_3}"

Set Tag_Placa = Ogroup.Item("{[PLC_BETANIA]RCP.PLACA}")
Set Tag_Cod_Coleta = Ogroup.Item("{[PLC_BETANIA]RCP.COD_COLETA}")
Set Tag_Silo_Destino = Ogroup.Item("{[PLC_BETANIA]RCP.SILO_DESTINO}")
Set Tag_Nr_Lote_C = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}")
Set Tag_Nr_Lote_S = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_SILO}")
Set Tag_Tot_Litros = Ogroup.Item("{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}")
Set Tag_Nr_RCP = Ogroup.Item("{[PLC_BETANIA]RCP.NR_RCP}")
Set Tag_Save_RCP = Ogroup.Item("{[PLC_BETANIA]Save_RCP}")
Set Tag_Updt_RCP_1 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_1}")
Set Tag_Updt_RCP_2 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_2}")
Set Tag_Updt_RCP_3 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_3}")

If Tag_Save_RCP.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
dataString = "'" & Date & "','" & Time & "','" & Tag_Placa.Value & "','" & Tag_Cod_Coleta.Value & "','" & Tag_Silo_Destino.Value & "','" & Tag_Nr_Lote_C.Value & "','" & Tag_Nr_Lote_S.Value & "','" & Tag_Nr_RCP.Value & "'"
SQLStr = " Insert into TBL_RCP_SETPOINT (DT_INICIO_RECEBIMENTO, HR_INICIO_RECEBIMENTO, PLACA, COD_COLETA, SILO_DESTINO, NR_LOTE_CAMINHAO, NR_LOTE_SILO, NR_RECEPCAO) values (" & dataString & ");"
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

If Tag_Updt_RCP_1.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
SQLStr = "Update TBL_RCP_SETPOINT" & aSpace & "SET" & aSpace & "TOTAL_LITROS_RECEBIDO = " & tick & Tag_Tot_Litros.Value & tick _
& comsp & "DT_FIM_RECEBIMENTO = " & tick & Date & tick _
& comsp & "HR_FIM_RECEBIMENTO = " & tick & Time & tick _
& aSpace & "WHERE" _
& aSpace & "DT_FIM_RECEBIMENTO IS NULL" _
& aSpace & "AND" _
& aSpace & "NR_RECEPCAO = 1" _
& semic

MsgBox (SQLStr)
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

Now I work the code with other Ifs to inserts and updates and put this code in Display Activate in screen thats always opened and always trigger PLC is shooted I insert or update the tables.

Thanks a lot

parky · Mar 5, 2021

Glad you got it right, I thought that "NULL" is the way to go.

FFP · Mar 8, 2021

Dear colleagues,
Below is the complete code:

Private Sub Display_Activate()

Dim dataString As String
Dim SQLStr As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Dim Ogroup As TagGroup
Dim Tag_Placa, Tag_Cod_Coleta, Tag_Silo_Destino, Tag_Nr_Lote_C, Tag_Nr_Lote_S, Tag_Tot_Litros, Nr_RCP, Tag_Save_RCP, Tag_Updt_RCP_1, Tag_Updt_RCP_2, Tag_Updt_RCP_3 As Tag

tick = "'"
aaaEmpty = tick & tick
comma = ","
aSpace = " "
semic = ";"
comsp = comma & aSpace

Set Ogroup = Application.CreateTagGroup(A, 1000)
Ogroup.Add "{[PLC_BETANIA]RCP.PLACA}"
Ogroup.Add "{[PLC_BETANIA]RCP.COD_COLETA}"
Ogroup.Add "{[PLC_BETANIA]RCP.SILO_DESTINO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_LOTE_SILO}"
Ogroup.Add "{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}"
Ogroup.Add "{[PLC_BETANIA]RCP.NR_RCP}"
Ogroup.Add "{[PLC_BETANIA]Save_RCP}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_1}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_2}"
Ogroup.Add "{[PLC_BETANIA]Grava_FIM_RCP_3}"

Set Tag_Placa = Ogroup.Item("{[PLC_BETANIA]RCP.PLACA}")
Set Tag_Cod_Coleta = Ogroup.Item("{[PLC_BETANIA]RCP.COD_COLETA}")
Set Tag_Silo_Destino = Ogroup.Item("{[PLC_BETANIA]RCP.SILO_DESTINO}")
Set Tag_Nr_Lote_C = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_CAMINHAO}")
Set Tag_Nr_Lote_S = Ogroup.Item("{[PLC_BETANIA]RCP.NR_LOTE_SILO}")
Set Tag_Tot_Litros = Ogroup.Item("{[PLC_BETANIA]RCP.TOTAL_LITROS_RECEBIDO}")
Set Tag_Nr_RCP = Ogroup.Item("{[PLC_BETANIA]RCP.NR_RCP}")
Set Tag_Save_RCP = Ogroup.Item("{[PLC_BETANIA]Save_RCP}")
Set Tag_Updt_RCP_1 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_1}")
Set Tag_Updt_RCP_2 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_2}")
Set Tag_Updt_RCP_3 = Ogroup.Item("{[PLC_BETANIA]Grava_FIM_RCP_3}")

' INSERT no BD
If Tag_Save_RCP.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
dataString = "'" & Date & "','" & Time & "','" & Tag_Placa.Value & "','" & Tag_Cod_Coleta.Value & "','" & Tag_Silo_Destino.Value & "','" & Tag_Nr_Lote_C.Value & "','" & Tag_Nr_Lote_S.Value & "','" & Tag_Nr_RCP.Value & "'"
SQLStr = " Insert into TBL_RCP_SETPOINT (DT_INICIO_RECEBIMENTO, HR_INICIO_RECEBIMENTO, PLACA, COD_COLETA, SILO_DESTINO, NR_LOTE_CAMINHAO, NR_LOTE_SILO, NR_RECEPCAO) values (" & dataString & ");"
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

' UPDATE no DB
' Recepcao 1
If Tag_Updt_RCP_1.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
SQLStr = "Update TBL_RCP_SETPOINT" & aSpace & "SET" & aSpace & "TOTAL_LITROS_RECEBIDO = " & tick & Tag_Tot_Litros.Value & tick _
& comsp & "DT_FIM_RECEBIMENTO = " & tick & Date & tick _
& comsp & "HR_FIM_RECEBIMENTO = " & tick & Time & tick _
& aSpace & "WHERE" _
& aSpace & "DT_FIM_RECEBIMENTO IS NULL" _
& aSpace & "AND" _
& aSpace & "NR_RECEPCAO = 1" _
& semic

MsgBox (SQLStr)
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

' Recepcao 2
If Tag_Updt_RCP_2.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
SQLStr = "Update TBL_RCP_SETPOINT" & aSpace & "SET" & aSpace & "TOTAL_LITROS_RECEBIDO = " & tick & Tag_Tot_Litros.Value & tick _
& comsp & "DT_FIM_RECEBIMENTO = " & tick & Date & tick _
& comsp & "HR_FIM_RECEBIMENTO = " & tick & Time & tick _
& aSpace & "WHERE" _
& aSpace & "DT_FIM_RECEBIMENTO IS NULL" _
& aSpace & "AND" _
& aSpace & "NR_RECEPCAO = 2" _
& semic

MsgBox (SQLStr)
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

' Recepcao 3
If Tag_Updt_RCP_3.Value = 1 Then
Conn1.ConnectionString = " dsn=DSN_BD_BETANIA; User Id='sa'; Password='}7@t_hNg'; "
SQLStr = "Update TBL_RCP_SETPOINT" & aSpace & "SET" & aSpace & "TOTAL_LITROS_RECEBIDO = " & tick & Tag_Tot_Litros.Value & tick _
& comsp & "DT_FIM_RECEBIMENTO = " & tick & Date & tick _
& comsp & "HR_FIM_RECEBIMENTO = " & tick & Time & tick _
& aSpace & "WHERE" _
& aSpace & "DT_FIM_RECEBIMENTO IS NULL" _
& aSpace & "AND" _
& aSpace & "NR_RECEPCAO = 3" _
& semic

MsgBox (SQLStr)
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close
Set Conn1 = Nothing
End If

End Sub

Now my main problem is to always run this code with the supervisory running on FT View SE. I need to put this code in a place where it is executed continuously and when one of the execution variables is activated (Insert trigger Tag_Save_RCP.Value or one of the update tags Tag_Updt_RCP_1.Value or Tag_Updt_RCP_2.Value or Tag_Updt_RCP_3.Value) do the given IFs of the script. Or I can divide the logic between 4 Scripts, one for each recording (1 insert and 3 updates). I put this script in the Activate of the display but it is not running continuously.
Thank you very much in advance.

parky · Mar 8, 2021

Never used FT but it may be possible to split the code, remove the if statements and use those variables on change event to call the code.
The problem is the activate display only runs the code when opening the display, I think you need to use the variables to trigger the code.
Ones I have done in other Scada systems is to set a variable in the PLC & then reset it in the VB Code.
For example call the code in a script that triggers on the variable going high, I know you can do it on the old RSView32.

parky · Mar 9, 2021

Just had a very quick look at the manual and it looks like you can create events based on tags changing state & use those events to tun your code.

Insert/Update data into tables in SQL Server Express via FTView SE

drbitboy

Lifetime Supporting Member

drbitboy

Lifetime Supporting Member

FFP

Member

drbitboy

Lifetime Supporting Member

parky

Member

parky

Member

drbitboy

Lifetime Supporting Member

FFP

Member

parky

Member

FFP

Member

FFP

Member

parky

Member

FFP

Member

parky

Member

parky

Member

Similar Topics