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

[FFP]

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

[widelto]

This webinar can help you.

https://www.youtube.com/watch?v=9gzZmCSvRZ8

regards,

william

[parky]

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, ...);

[FFP]

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

[ASF]

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.

[parky]

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.

[FFP]

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

[drbitboy]

post your code i.e. zip the files and attach to a post.

[FFP]

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???

[drbitboy]

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.

[FFP]

Its the beginning of code.....Before I codifyng I need manipulate PLC tags in vba code.
BD_SETPOINT.PLACA is my PLC Tag

[drbitboy]

Where is BD_SETPOINT declared? e.g.

Dim BD_SETPOINT As ???

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

  BD_SETPOINT = ???

[FFP]

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

[drbitboy]

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

MsgBox(BD_SETPOINT)

and I suspect you will get the same 424 error.


2) Try

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.

[curlyandshemp]

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.