You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

Reply
 
Thread Tools Display Modes
Old February 22nd, 2021, 01:18 PM   #1
FFP
Member
Brazil

FFP is offline
 
Join Date: Feb 2021
Location: Brazil
Posts: 17
Insert/Update data into tables in SQL Server Express via FTView SE

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 by FFP; February 22nd, 2021 at 02:04 PM.
  Reply With Quote
Old February 22nd, 2021, 03:12 PM   #2
widelto
Member
Colombia

widelto is offline
 
widelto's Avatar
 
Join Date: Jul 2005
Location: Barranquilla, Colombia
Posts: 2,995
This webinar can help you.

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

regards,

william
__________________
My two cents.
  Reply With Quote
Old February 23rd, 2021, 04:01 AM   #3
parky
Member
United Kingdom

parky is offline
 
parky's Avatar
 
Join Date: Oct 2004
Location: Midlands
Posts: 2,953
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, ...);
  Reply With Quote
Old February 23rd, 2021, 02:30 PM   #4
FFP
Member
Brazil

FFP is offline
 
Join Date: Feb 2021
Location: Brazil
Posts: 17
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
  Reply With Quote
Old February 23rd, 2021, 05:39 PM   #5
ASF
Lifetime Supporting Member
Australia

ASF is offline
 
Join Date: Jun 2012
Location: Australia
Posts: 3,242
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.
  Reply With Quote
Old February 24th, 2021, 02:16 AM   #6
parky
Member
United Kingdom

parky is offline
 
parky's Avatar
 
Join Date: Oct 2004
Location: Midlands
Posts: 2,953
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.
Attached Images
File Type: jpg Datasource.jpg (207.7 KB, 60 views)
  Reply With Quote
Old February 24th, 2021, 08:23 AM   #7
FFP
Member
Brazil

FFP is offline
 
Join Date: Feb 2021
Location: Brazil
Posts: 17
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
  Reply With Quote
Old February 24th, 2021, 08:46 AM   #8
drbitboy
Lifetime Supporting Member
United States

drbitboy is online now
 
drbitboy's Avatar
 
Join Date: Dec 2019
Location: Rochester, NY
Posts: 2,317
post your code i.e. zip the files and attach to a post.
  Reply With Quote
Old February 24th, 2021, 09:23 AM   #9
FFP
Member
Brazil

FFP is offline
 
Join Date: Feb 2021
Location: Brazil
Posts: 17
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???
  Reply With Quote
Old February 24th, 2021, 10:51 AM   #10
drbitboy
Lifetime Supporting Member
United States

drbitboy is online now
 
drbitboy's Avatar
 
Join Date: Dec 2019
Location: Rochester, NY
Posts: 2,317
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.
  Reply With Quote
Old February 24th, 2021, 12:28 PM   #11
FFP
Member
Brazil

FFP is offline
 
Join Date: Feb 2021
Location: Brazil
Posts: 17
Its the beginning of code.....Before I codifyng I need manipulate PLC tags in vba code.
BD_SETPOINT.PLACA is my PLC Tag
Attached Images
File Type: jpeg WhatsApp Image 2021-02-24 at 14.21.28.jpeg (54.2 KB, 51 views)
  Reply With Quote
Old February 24th, 2021, 01:08 PM   #12
drbitboy
Lifetime Supporting Member
United States

drbitboy is online now
 
drbitboy's Avatar
 
Join Date: Dec 2019
Location: Rochester, NY
Posts: 2,317
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 = ???
  Reply With Quote
Old February 24th, 2021, 01:14 PM   #13
FFP
Member
Brazil

FFP is offline
 
Join Date: Feb 2021
Location: Brazil
Posts: 17
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
Attached Images
File Type: jpeg WhatsApp Image 2021-02-24 at 15.10.19.jpeg (182.8 KB, 50 views)
  Reply With Quote
Old February 24th, 2021, 01:26 PM   #14
drbitboy
Lifetime Supporting Member
United States

drbitboy is online now
 
drbitboy's Avatar
 
Join Date: Dec 2019
Location: Rochester, NY
Posts: 2,317
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 by drbitboy; February 24th, 2021 at 01:31 PM.
  Reply With Quote
Old February 24th, 2021, 01:41 PM   #15
curlyandshemp
Lifetime Supporting Member
Canada

curlyandshemp is offline
 
curlyandshemp's Avatar
 
Join Date: Jul 2005
Location: Toronto
Posts: 1,890
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.
__________________
Work to Ride,
Then Ride to Work

I would rather have a bottle in front of me..
than have a frontal lobotomy
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
Deployment of a Data Server in the Galaxy (ArchestrA IDE) NathBarreiros LIVE PLC Questions And Answers 2 January 31st, 2021 10:16 AM
FactoryTalk Studio Alarm Issue Sham LIVE PLC Questions And Answers 10 April 2nd, 2020 05:36 PM
FTView SE - unable to delete application or add network server phuz LIVE PLC Questions And Answers 4 June 9th, 2017 08:00 AM
FTView Datalogging to SQL Express. How to purge database MorphuisOGrady LIVE PLC Questions And Answers 5 September 5th, 2016 04:57 PM
Data Trends Not Always Appearing (FTView SE) jfd7851 LIVE PLC Questions And Answers 0 March 1st, 2016 04:11 PM


All times are GMT -5. The time now is 04:46 PM.


.