VBscript to read MSSQL Database table value in Siemens Wincc HMI

in this article we know how to connect MSSQL database table to read table value wincc hmi. to connect MSSQL database first connect database than read value of table. to do this first create a data base in same database create a table. second create a DSN to connect MSSQL database to VBSCRIPT. it is very simple to read and write value using vbscript in wincc HMI. VBScript have so many features. To read values from an MSSQL table using VBScript, you can use ADO (ActiveX Data Objects). in this article have simple VBscript help you connect to an MSSQL database, execute a query, and read table values.

---

what is Microsoft SQL server Management studio

Microsoft SQL Server Management Studio (SSMS) is an integrated environment for managing, configuring, and interacting with SQL Server instances. It is a powerful tool provided by Microsoft for database administrators (DBAs), developers, and analysts to manage databases, write queries, and perform administrative tasks. SSMS is free and can be downloaded from the official Microsoft website.

• OPEN sql server management studio (SSMS) and connect to your SQL Server instance.
• Use your credentials to log in (either Windows Authentication or SQL Server Authentication).

---

how to create tag database in MSSQL

first step to create tag database to store tag value so we can read and write these tag value. a tag database an organized collection of tag values that can be easily accessed, managed, and updated. You can create database by Using the GUI (Graphical User Interface) or Using SQL Query. follow these steps to create a database in MSSQL.
Using the GUI (Graphical User Interface):

1. In the Object Explorer, expand the Server node.
2. Right-click on the Databases folder.
3. Select New Database from the context menu.
4. In the New Database window:
   • Enter the Database Name (tagdatabase).
   • Review or modify settings like file locations or sizes if needed (optional).
5. Click OK to create the database.

---

how to create database Using SQL Query:

1. Open a new query window by clicking New Query in the toolbar.
2. Write the following SQL code:
   "CREATE DATABASE tagdatabase2;"
3. Execute the query by pressing F5 or clicking the Execute button.

---

how to create tag table in MSSQL

A table in a database is a structured format for storing data. It organizes data into rows and columns, making it easy to manage, retrieve, and manipulate. Tables are a fundamental component of relational databases. you can create table in mssql by using GUI or SQL query
Method 1: Using the GUI (Graphical User Interface)

1. Open the Table Designer:
   • Right-click on the Tables folder and select New Table.

---



---

2. Define Columns:
   • Enter the column names, data types (e.g., INT, NVARCHAR(50)), and constraints (e.g., NOT NULL, PRIMARY KEY).
3. Set a Primary Key (Optional):
   • Right-click the row for the primary key column and select Set Primary Key.

---



---

4. Save the Table:
   • Click File > Save tagtable (or press Ctrl+S) and provide a name for the table.

---

---

Method 2: create table Using SQL Queries

1. Open a New Query Window:
   • In SSMS, click New Query from the toolbar.
2. Write a SQL Script:
   • Example SQL to create a Users table:

   
   USE tagdatabase2; -- Switch to the database
   
   CREATE TABLE tagtable (
       id INT PRIMARY KEY,        -- Unique identifier
       tagname NVARCHAR(50) NOT NULL, -- Tag name
       tagvalue INT NOT NULL,         -- Tag value
       JoinDate DATE DEFAULT GETDATE() -- Default to current date
   );
                   

3. Execute the Script:
   • Press F5 or click the Execute button to create the table.

---

---

add value in tag table

after creating table add following value to tagtable
right click on tag table and then select edit top 200 rows

---

---

add following value to table

---

---

or execute following sql query directly to insert value in tagtable

---

---

how to configure dsn for wincc sql server

A Data Source Name (DSN) is a configuration that provides the necessary information for an application to connect to a database, such as the database driver, server address, and authentication details. DSN commonly stands for Data Source Name, a configuration used in computing to define how an application connects to a database.
Step 1: Open ODBC Data Source Administrator

1. open control panel
2. open windows tools
3. Choose the appropriate version of ODBC:
   • Use the 32-bit version if your application is 32-bit.
   • Use the 64-bit version if your application is 64-bit.

here we select 32 bit version for this article.

---

Step 2: Add a New DSN

1. In the ODBC Data Source Administrator, go to the System DSN or User DSN tab:
   • System DSN: Available to all users on the system.
   • User DSN: Available only to the current user.
2. Click the Add button.

---

select driver for DSN

Select SQL Server or ODBC Driver for SQL Server from the list of drivers and click Finish.

---

Step 3: Configure the DSN

1. In the Create a New Data Source to SQL Server window:
   • Enter a name for the DSN (e.g., MySql).
   • Provide an optional description.
   • Select the SQL Server instance to connect.
2. Click Next.

---

Step 4: Configure Authentication

1. Choose the authentication method:
   • Windows Authentication: Uses the current Windows user's credentials.
   • SQL Server Authentication: Requires a username and password.
2. Click Next.

---

Step 5: Configure the Default Database

1. Select the default database for the DSN from the dropdown menu.
2. Click Next.

---

Step 6: Test the DSN

1. Click the Test Data Source button to ensure the DSN is working correctly.
2. If the test is successful, click Finish.

---

open tia portal and create a project

open tia portal and create a new project enter details of project or add hmi in your project. in this example we have a button and two text field. when button is pressed value of sql table is show in text field. add a script and rename to read_sql table. Assign vbscript to button1 press event property. when button is pressed value of selected table column show in text field. if any error comes during connection of read value it shows on text field.

---

VBscript to read MSSQL Database table value in Siemens Wincc HMI

example of vbscript to read MSSQL Database table value in Siemens Wincc HMI
This script demonstrates how to connect to a database using ODBC, execute an SQL query, and display the results in a human-machine interface (HMI).

---

---

1. Several variables are defined to manage the database connection, SQL query, and text fields:
   • conn: The database connection object.
   • strConn: The connection string for the ODBC Data Source Name (DSN).
   • sqlSelect: The SQL query to retrieve the desired value.
   • dsn: The ODBC DSN name (e.g., "MySql").
   • valueRead: Holds the value retrieved from the database.
   • textfield1 and textfield2: References to text fields in the HMI runtime.
2. Setting Up HMI Text Fields : References to the text fields are set using the HmiRuntime.Screens object:

   Set textfield1 = HmiRuntime.Screens("Screen_1").ScreenItems("Text field_1")
   Set textfield2 = HmiRuntime.Screens("Screen_1").ScreenItems("Text field_2")
               

   These text fields are used to display the results of the script.
3. The ODBC DSN is specified, and the connection string is constructed:

   dsn = "MySql"
   strConn = "DSN=" & dsn & ";Trusted_Connection=Yes;"
               

   The Trusted_Connection=Yes setting enables Windows Authentication.
4. The SQL query retrieves the value where tagname = 'temprature1':

   sqlSelect = "SELECT tagvalue FROM tagtable WHERE tagname = 'temprature1'"
               

5. Database Connection

   The script creates and opens the connection using the ADO object:

   Set conn = CreateObject("ADODB.Connection")
   conn.Open strConn
               

   Error handling is enabled using On Error Resume Next.
6. Executing the Query The query is executed, and the result is checked:

   Set rs = conn.Execute(sqlSelect)
   If Not rs.EOF Then
       valueRead = rs.Fields("tagvalue").Value
       textfield1.Text = "Value retrieved: " & valueRead
   Else
       textfield2.Text = "No record found for tagname 'temprature1'."
   End If
               

   If the query returns a result, the value is displayed in textfield1. Otherwise, an error message is shown in textfield2.
7. Error Handling and Cleanup If the connection fails, an error message is displayed:
   

   textfield2.Text = "Failed to connect to the database."
               

   All resources (recordset and connection) are closed and cleaned up at the end:

   If conn.State = 1 Then conn.Close
   Set conn = Nothing
               

Important Notes

• Ensure the ODBC DSN is properly configured on the system.
• Replace placeholder values (e.g., MySql, tagtable) with actual database and table details.
• This script is designed for use in a Human-Machine Interface (HMI) environment.

---

---