Write/Log Data to Database - (WINCC Advanced V13 Sample project)

Click this link to get sample program. Wincc Advanced V13 SP1 is necessary to open the project.

Click here to download


The same vbscript you can use for Wincc V13 Professional 

Read Data from Database - (WINCC Advanced V13 Sample project)

Click this link to get sample program. Wincc Advanced V13 SP1 is necessary to open the project.

Click here to download

The same vbscript you can use for Wincc V13 Professional 

WINCC VBScript - Write data into database

This script is useful in datalogging purpose. This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim objConnection
Dim strConnectionString
Dim FieldName
Dim Record
Dim strSQL
Dim objCommand
Dim strfile

strfile = "C:\Reports\Database.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

strSQL = "INSERT INTO TableName (" & FieldName & ") VALUES (" & Record & ");"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
    .ActiveConnection = objConnection
    .CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing

WINCC VBScript - Read data from database

This script is common for Wincc Advanced, Wincc Professional. For Wincc 7.x the tag declaration will change

Dim objConnection
Dim objCommand
Dim objRecordset
Dim strConnectionString
Dim strSQL
Dim lngValue
Dim lngCount
Dim strfile

strfile = "C:\Reports\Database.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

strSQL = "select * from DQ_LOG where DATE_TIME >= CDATE('"& Start_Time &"') And  DATE_TIME <= CDATE('"& End_Time &"')"

Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objRecordset = CreateObject("ADODB.Recordset")
Set objCommand = CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = strSQL
Set objRecordset = objCommand.Execute

Smarttags("Tagvalue1")  = objRecordset.Fields(0).Value
Smarttags("Tagvalue2")  = objRecordset.Fields(1).Value

Set objCommand = Nothing
objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing

Steps:
1. Simply copy paste this script
2. Do minor changes as per your application.
Enough!! 

SQL Tutorial 2

** SQL is very important for SCADA Report generation**

INSERT INTO

The INSERT INTO statement will create new row and insert the value into the table.

Syntax:

INSERT INTO table1 (column1,column2,column3) VALUES (value1,value2,value3);

Example:

INSERT INTO Temperature_Log (Set_point1,Set_Point2,Set_Point3)
VALUES ('35.7', '37.9', '44.1');

UPDATE Statement

The INSERT INTO statement will replace the existing value with new value.

Syntax:

UPDATE table1 SET column1=value1,column2=value2 WHERE column3=value1;

Example:

UPDATE Temperature_Log SET Set_point1='35.7', Set_point2 ='37.9' WHERE Present_Value ='20';

SQL DELETE Statement
The DELETE statement is used to delete single row or multiple rows in a table. 

Syntax:
DELETE FROM table1 WHERE column1=value1;

Example:
To delete all rows in a table
  DELETE FROM table1;
To delete all rows in a table
  DELETE FROM Temperature_Log WHERE Channel_Name='Chamber_PID' AND Furnace_name='Hardening';

SQL LIKE Operator

The LIKE operator is used to fetch for a specified text in a column.

Syntax:

SELECT column1 FROM table1 WHERE column1 LIKE specific_text;

Example:
SELECT * FROM Temperature_Log WHERE Channel_Name LIKE 'PID%';

BETWEEN Operator
The BETWEEN operator selects values within a range. The values can be numbers, text, or date. This is very important in SCADA projects because mostly we used to generate reports between two date and time.
Syntax:
SELECT column1 FROM table1 WHERE column1 BETWEEN value1 AND value2;

Example:
Using Number:
SELECT * FROM Temperature_Log WHERE Temperature BETWEEN 35 AND 66;
Using Date:
Select * FROM Temp_Log  where DATE_TIME >= CDATE('" & Start_Date1 & "')and DATE_TIME <=CDATE('" & End_Date1 & "') order by DATE_TIME;

INSERT INTO SELECT Statement
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. This will not delete any data in existing table, it will just append the data.
Syntax:
INSERT INTO table2 SELECT * FROM table1;

Example:
"INSERT INTO Temp_Report_Date_Trend (DATE_TIME ,HARD_TEMP1_SP) SELECT DATE_TIME ,HARD_TEMP1_SP from Temp_Log where Date_Time >= CDATE('" & Start_dates & "')and Date_Time <=CDATE('" & End_dates & "') order by Date_Time"

SQL Tutorial 1

** SQL is very important for SCADA Report generation**

SQL is a standard query language for accessing databases. It can be used in vbscripts while accessing databases.

For Example:

strfile = "D:\Database.mdb"

Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

Set rs = New ADODB.Recordset

cn.Open Strcon

sqlstr = "SELECT * from Datalog_Table"

rs.Open sqlstr, cn, adOpenDynamic, adLockOptimistic

Text1.Text = rs!DateTime_Column

Text2.Text = rs!Data1_Column

rs.Close

cn.Close

from the above vbscript the green highlighted line is the SQL Query. There are different types of SQL queries for different types of database access. They are:

SELECT

Syntax:

SELECT column1,column2 FROM table1;

or

SELECT * FROM table1;

Example:

SELECT SupplierName,Country FROM Suppliers;

SELECT DISTINCT

Syntax:

SELECT DISTINCT column1,column2 FROM table1;

Example:

SELECT DISTINCT City FROM Suppliers;

WHERE Clause

Syntax:

SELECT column1,column2 FROM table1 WHERE column3 operator value;

Example:

SELECT * FROM Suppliers WHERE SupplierID=5;

AND & OR Operators

These operators are used in where clause.

The AND operator is used to display a record if both the condition1 AND the condition2 are true.

The OR operator is used to displays a record if either the condition1 OR condition2 is true.

Example:

SELECT * FROM Suppliers WHERE Country='India' AND City='Japan';

SELECT * FROM Suppliers WHERE Country='India' OR City='Japan';

ORDER BY

Syntax:

SELECT column1,column2 FROM table1 ORDER BY column3 ASC/DESC,column4 ASC/DESC;

ASC - Order(sort) by Ascending order

DESC - Order(sort) by Descending order

Example:

1. SELECT * FROM Suppliers ORDER BY City;

2. SELECT * FROM Suppliers ORDER BY City DESC;
3. SELECT * FROM Suppliers ORDER BY City ASC, Suppliername DESC;

VB Script for Write into database Table

Write data to MS ACCESS Database

Free code, example code, sample code

Step 1: Open new VB6 project.
Step 2: Select Standard EXE
Step 3:

Step 3: Select the following references

Step 4: Create MS ACCESS Database

Step 5: Add text-box and button

Step 5: Paste this VBScript

Private Sub Command1_Click()

Dim strfile As String

Dim Strcon As String

Dim sqlstr As String

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

strfile = "D:\Database.mdb"

Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strfile & ";"

Set rs = New ADODB.Recordset

cn.Open Strcon

sqlstr = "SELECT * from Datalog_Table"

rs.Open sqlstr, cn, adOpenDynamic, adLockOptimistic

Text1.Text = rs!DateTime_Column

Text2.Text = rs!Data1_Column

rs.Close

cn.Close

End Sub

Enjoy! Now you can able to write present value and setpoint to database