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

FFP

Member
Join Date
Feb 2021
Location
Brazil
Posts
24
Hi All,
I need a step-by-step method to insert and update lines in the database SQL Server via software builded in FT View SE.

Thanks.
Fabricio
 
Last edited:
Here is some code using VB, might vary in SE

Option Explicit
Dim GroupStr1,SQLStr, BatchIDStr As String
Dim Rs1 As New ADODB.Recordset
Dim Conn1 As New ADODB.Connection
Dim Times As Variant
Dim Ck_Val As Single
Sub Main()

Times = "'" + Format(Now,"yyyy-mm-dd hh:mm:ss") + "'"

BatchIDStr = GetVariableValue(GroupStr1 & "Cooker_Batch_Code")
Ck_Val = GetVariableValue(GroupStr1 & "Cook_Value")
Conn1.ConnectionString = "dsn=SP3DB;uid='sa';pwd='';"
SQLStr = "UPDATE Cooker_Batch SET CookCompleted =" & Times & ", CookValue = " & Ck_Val & " Where BatchID =" & BatchIDStr & "
Conn1.Open
Conn1.Execute SQLStr
Conn1.Close

Set Conn1 =Nothing
SetVariableValue(GroupStr1 & "Scada_Batch_Complete",False)

End Sub

So in essence the update string is
UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;

Insert is just the string:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 
Hi parky,
Error in compile
Run-time error '-2147217843 (80040e4d)
[Microsoft][ODBC SQL Server Drive][SQL Server]Logon failed for user '\'sa\''

Thank you
Fabricio
 
Code:
Conn1.ConnectionString = "dsn=SP3DB;uid='sa';pwd='';"
You will have to specify the database username and password in this line of your code. This example is for a username "sa" and no password, but in practice there will be a password set for the sa account.
 
First of all have you created a "DSN" (Data source name), this is the easiest way to configure the connection string, so you go to Control panel, system & security, administrative tools, ODBC Data Sources, then 64 or 32 bit, select the driver i.e. SQL Server Native Client, create a data source, etc. then test the connection. see pic.
Depending on how you created the DB i.e. local, what DB engine you are using i.e. SQLExpress, is there a password. SQL connections and query strings have to be exact or it will produce an error.
SQL is very picky regarding correct strings i.e. an extra space will cause an error.

Datasource.jpg
 
Thanks a lot parky,
I solved the connection but my problem now is reference the variables of PLC in code. I don't know how catch the value of a PLC tags and include this values in my code. i.e.
Dim dataString As String
dataString = " ' "&Date&&" ',' "&PLCTag1&" ',' "&PLCTag2&" ' "
Insert into TBL_YYY (AAA, BBB, CCC) values (" &dataString& ")

But not function, erro showed * Run-time error '424' Object Required *

I try put Insert code in comment and MsgBox(dataString) but the same error showed

Thank you
 
My problem is reference PLC tag in vba code.
I have LevelTank1 that I need insert in a SQL Server Table called "TBL_RCP", but I can not use this PLC tag in vba code. I try only show but the error is * Run-time error '424' Object Required *
The code is MsgBox(LevelTank1.value)
How I reference and use the PLC tag values in vba???
 
Without the declaration of LevelTank1, we cannot guess at what the problem is. E.g. maybe it should be .Value instead of .value. Please ZIP and post all of the code.
 
Its the beginning of code.....Before I codifyng I need manipulate PLC tags in vba code.
BD_SETPOINT.PLACA is my PLC Tag

WhatsApp Image 2021-02-24 at 14.21.28.jpeg
 
Where is BD_SETPOINT declared? e.g.


Code:
Dim BD_SETPOINT As ???


And where is BD_SETPOINT assigned a value? e.g.


Code:
  BD_SETPOINT = ???
 
BD_SETPOINT is a Datatype in PLC....I need use in vba code the values of tags in this datatype i.e. BD_SETPOINT.PLACA and BD_SETPOINT.COD_COLETA

WhatsApp Image 2021-02-24 at 15.10.19.jpeg
 
That is in your PLC, is it not?


Where is BD_SETPOINT declared and assigned in VBA code on FTView system?


For example,

1) Try
Code:
MsgBox(BD_SETPOINT)
and I suspect you will get the same 424 error.


2) Try
Code:
MsgBox(FFP) or MsgBox(drbitboy)
and get the same 424 error again.


Computers can only do what they are told on bits they are told where to find, they cannot read our minds.
 
Last edited:
All I can say is, the is the kind of stuff where Induction Automation's Ignition is light years ahead of all the others.
if you need to get stuff in and out of a SQL database, Ignition is the way to go.
 

Similar Topics

I'm new to ST so go easy. Studio 5000 L33ER PLC I'm trying to take 2 strings, merge them into 1 string. with a space between the 2 strings...
Replies
3
Views
2,231
Hello everyone. Currently I am using Eplan p8 and faced (while creating a panel) with a challenge in finding a device called DIN mounting rail...
Replies
10
Views
3,277
Hello guys, I am working with a CompactLogix, which communicates over Ethernet/IP with a pin marker. Basically, The PLC sends the texts to be...
Replies
6
Views
1,344
Hi, I have some remote IO connected to my S7-317-2 PN/DP system. I have 1 particular profinet remote IO which is not important to the system, and...
Replies
9
Views
2,958
I am using VbScript in Indusoft to insert data into a MySql database. Immediately after inserting a row, I need to know what that row number is...
Replies
24
Views
5,223
Back
Top Bottom