SQl

Good morning Parky, I'm trying to use this script from you in factorytalk 8.0 on supervisor to throw values ​​into a sql table.
I am having a little trouble communicating with my slq.

What would this CookerBatch be?

Where do I put in the name of my slq table?

It has as a peculiar aid.

Private Sub Button1_Released()

'Declare variables

Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordSet = New ADODB.Recordset
Dim PLCTestTag As Integer

objMyConn.ConnectionString = " Provider=SQLOLEDB.1;Server=SE06\FTVIEWX64TAGDB;Database =PLC1DB;Trusted_Connection=yes"
objMyConn.Open

'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn

objMyCmd.CommandText = "INSERT INTO BatchTable (TLC1_BatchNo,TLC1_Truck,TLC1_Start,TLC1_Stop,TLC1_StartTime,TLC1_StopTime,TLC1_Pump,TLC1_Tank,TLC1_Product,TLC1_SP,TLC1_FT,TLC1_Name) Values (" & TLC1_BatchNo & "," & TLC1_Truck & "," & TLC1_Start & "," & TLC1_Stop & "," & TLC1_StartTime & "," & TLC1_StopTime & "," & TLC1_Pump & "," & TLC1_Tank & "," & TLC1_Product & "," & TLC1_SP & "," & TLC1_FT & "," & TLC1_Name & ")"

objMyCmd.CommandType = adCmdText
objMyCmd.Execute

'Close Connection
objMyConn.Close


End Sub

first of all although it might be to do with how you have written the code in this thread or copied & pasted it there are a number of errors like spaces and the SQL string needs to be one long string, you can achieve this if your editor does not allow you to enter one long string by using an underscore after each line.
Or add smaller strings together as in your editor pressing return adds the char into the SQL string.
also I do not know about your connection string, I used a DSN (Data source Name) that is set up in control panel in ODBC Sources, this way you only need to set a DSN i.e. MyDSN tell it the type of driver and the database you are using follow the steps to create the connection and at the end you can test the connection. Go to control panel and run ODBC Data sources, add a new one, as an Native SQL client, give it a name like MYDSN for example, then a description then tell it what database to connect to, In my case it was a local DB (PC0851\SQLEXPRESS) for testing this way it means any reference to the connection in all your code only needs to be changed in the DSN, doing it in code might mean going through all instances of the connection. Next tell it what the authentication is in my case it was "Integrated windows Authentication, Then change the default DB, just press next to the rest of the fields then it will display test connection, if it connects ok then there is no problem in the connection. replace you connection string mine was Conn1.ConnectionString = "dsn=SP3DB;uid='sa';pwd='';"
This assumes DB is using standard login. I have not done any in FT this was in various other Scada systems so cannot comment on your scripts.
The Cooker Batch is the table, Inside the tables I used Date & time field so that any reporting software could limit date/time range & the Batch ID happened to be unique and used part of the date stamp i.e. yymmddhhmmnumber so for example 1911031031001 well it is unique til 2119 doubt it would still be running I did not use seconds as the 001 is user entered from their production schedule this way it will never repeat until 2119.
One other note: SQL is not very forgiving and the error telling you there is a problem not particularly good When I first started SQL script I tore my hair out, also there are different ways to do the SQL String
for example here is a way of instead of entering the fields then using the directive "values" you can do it like this
"UPDATE Cooker_Batch SET CookCompleted =" & Times & ", CookValue = " & Ck_Val & " Where BatchID =" & BatchIDStr & "
i.e. The field = the value
I strongly check using a DSN to check connection and possible some sort of error handling to display the SQL Error.

What's wrong with this script that I can't throw factory values ​​via vba to my SQL?

Picture of my script and my database for you to try to help me.

I can't pass the values ​​into my sql table.

Can you take a look at my script and see if it identifies something that is wrong?

If any of the data is collected by SCADA software, I'm sure SQL would be needed.

First of all I would put an error handler in your script for example:

After your dim statements "On error goto Error_Hand
do your script
......

Exit Sub
/ After exit sub command
handle the error
Error_Hand:
Debug.Print Str(Error) + " Batch Stage Log Failed For Cooker " & Pass
/ Note: this is a debug & assumes the FT can use this.
End Sub /Then end the sub
What this should do is if there is an error it jumps to the Error Handler and displays the error number & a message.
Note: if FT does not support Debug then you could put a break point
See pic below.
Also you need to make sure the data you are writing to the DB is of the correct type i.e. integer, float, text etc.
Just another note you could put a break point before the main code runs this should allow you to step through it (F8) in VB.
If you could post a pic of the tables and the type of data etc. I will try to re-create it.

Sorry my last post showed update of the fields, you are writing to add a new row I did this based on what you have posted, however, not having FT I can only do it in my scada package but apart from the way you show the commands should be the same, I strongly suggest you try to put a break point in and step through your code. This worked as you can see from the DB table.

parky said:

Sorry my last post showed update of the fields, you are writing to add a new row I did this based on what you have posted, however, not having FT I can only do it in my scada package but apart from the way you show the commands should be the same, I strongly suggest you try to put a break point in and step through your code. This worked as you can see from the DB table.

Click to expand...

Can you explain how you can connect your vba to your sql through this line there:

Conn1.ConnectionString = "dsn = SP3DB; uid = 'sa'; pwd '';"

What it means = SP3DB

You can do this example = Conn1.ConnectionString = "dsn = SP3DB; uid = 'sa'; pwd '';"
According to the photo data previously from my database?

You are writing to add a new line - Yes, every time I press or button, I want it to write a new line to my sql in my table.

This is a weak spot of mine that I really need to spend some time on.



Databases are becoming more often than not part of nearly every system.

What I do is create a Data Source Name in control panel.
So that is where objMyConn.ConnectionString = "dsn = SP3DB; uid = 'sa';" pwd '';"
So instead of creating the complete path I use a DSN.
To do that windows & load control panel then select ODBC Data Sources
Add a User DSN, then select the driver i.e. SQLServer Native client press finish it will then ask you to give it a name (in my case SP3DB an example is TestDB) then select the server from the drop down list C:\USERS\PUBLIC\DOCUMENTS\RSVIEW ENTERPRISE\ME\HMI PROJECTS\OBJECTS 5 SCREEN DEMO\TAG\OBJECTS 5 SCREEN DEMO.MDF (note: this was the only RSV DB I have on my pc so you need to find where your DB is.
Keep pressing next and it will save it Note: where it asks for Login I selected windows Auth without a password.
Then it will present you with a window to test connection if it fails then it will tell you why
So your connection string becomes "dsn = TestDB; uid = 'sa';" this mode uses the default log on with windows authentication etc. Note: if the DB was on a server controlled by IT then when you log on to your PC IT should give you access to that db using you login. however I believe it is all local to the machine so should be no problems.
By the way the use of a DSN has some advantages (not all of them safe as far as IT are concerned), however, it means if you need to point to a different database i.e. they move it's location or rename it you only need to change the DSN and not every instance of your connection string.

Here is a pdf of how to create a DSN

Parky friend I got it, all right. I created the DSN on ODBC and managed to communicate with my bank.

Now I'm trying to find out how to create a dbGRID in factoryTalk 8.0, you know what? If I have to import in VBA or enable in Reference a dbGrid?

skipa-za-ko_13 said:

reallyyyy

Click to expand...

I didn't understand what you wanted to say?

skipa-za-ko_13 said:

reallyyyy

Click to expand...

Do you know how I use a GRID in Factorytalk 8.0, if there is a way to allow VBA to reference a GRID or if I can import it?

I don't know about Factory talk but in VB you reference the grid like this
datagridview.rows(rownumber).cells(columnnumber).value = text string
So this will populate that row/column cell with the text (in your case a tag).
However there is a way to import it within your code by connecting to the DB and use the select statement.
Open the DB as per your code for writing to the DB then SQL text is:
objMyCmd.CommandText = "SELECT * FROM TableName;
Above returns all data
SELECT * FROM TableName WHERE ColumnName = 'Value';
The above returns only where the column name has a particular value

Set DataGrid1.DataSource = objMyConn
The above populates the datagrid with the returned data from the query
Sorry I cannot be of more help but I have no knowledge of FT