Scribd Logo
Upload

Welcome to Scribd!

  • 60 views

    SQL Database Using VBScripts

    Uploaded by

    Rezha Falaq Rizki
    This document contains 4 VBScript examples for interacting with an SQL database: 1. Script-1 creates a database using ADO and SQL commands. 2. Script-2 creates a table and columns in the database by executing an SQL CREATE TABLE statement. 3. Script-3 writes data to the database table by inserting values using an SQL INSERT statement. 4. Script-4 reads data from the database table by executing a SELECT statement and assigning the returned fields to tags.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    SQL Database Using VBScripts

    Uploaded by

    Rezha Falaq Rizki
    60 views2 pages
    This document contains 4 VBScript examples for interacting with an SQL database: 1. Script-1 creates a database using ADO and SQL commands. 2. Script-2 creates a table and columns in the database by executing an SQL CREATE TABLE statement. 3. Script-3 writes data to the database table by inserting values using an SQL INSERT statement. 4. Script-4 reads data from the database table by executing a SELECT statement and assigning the returned fields to tags.

    Original Title

    SQL+database+using+VBScripts

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains 4 VBScript examples for interacting with an SQL database: 1. Script-1 creates a database using ADO and SQL commands. 2. Script-2 creates a table and columns in the database by executing an SQL CREATE TABLE statement. 3. Script-3 writes data to the database table by inserting values using an SQL INSERT statement. 4. Script-4 reads data from the database table by executing a SELECT statement and assigning the returned fields to tags.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    60 views2 pages

    SQL Database Using VBScripts

    Uploaded by

    Rezha Falaq Rizki
    This document contains 4 VBScript examples for interacting with an SQL database: 1. Script-1 creates a database using ADO and SQL commands. 2. Script-2 creates a table and columns in the database by executing an SQL CREATE TABLE statement. 3. Script-3 writes data to the database table by inserting values using an SQL INSERT statement. 4. Script-4 reads data from the database table by executing a SELECT statement and assigning the returned fields to tags.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    SQL database using VBScripts

    Monday, April 5, 2021 1:38 PM

    Script-1 - Creating Database


    ODBC Data Source
    Sub VBFunction_1(ByRef Database_Name) (Control Panel)

    Dim conn, rst, SQL_Table

    TIA Portal SQL Database


    Set conn = CreateObject("ADODB.Connection") (Microsoft SQL Server)
    Set rst = CreateObject("ADODB.Recordset") (VB Scripting)

    conn.Open "Provider=MSDASQL;DSN="&Database_Name&""

    SQL_Table = "CREATE DATABASE " & SmartTags("database") SQL is an acronym that stands for Structured Query Language. SQL is a programming language designed for managing data
    in relational database management systems. Since its initial development, SQL has continued to grow in popularity and is
    Set rst = conn.Execute(SQL_Table) the most widely used database language in the world

    conn.close There are different database server available to store and create relational database one of which is Microsoft SQL Server
    which is very popular and easy to use. Sql language can be further used to run queries
    Set conn = Nothing
    Set rst = Nothing Scripting in HMI/SCADA is used to provide customization such as reading tags, linking databases,

    VBS (Visual Basic Script) is a scripting language developed by Microsoft. It is closely related to VB (Visual Basic) and VBA
    End Sub (Visual Basic for Applications). VBS is used in WinCC to dynamize the graphical objects in process operation (Runtime):

    • Tags Tag values can be read and written, e.g. to specify tag values for the controller via a mouse click event on a button.
    • Objects Object properties can be made dynamic using actions, and actions can be triggered by events influencing objects.
    • Screen-independent actions Screen-independent actions can be triggered cyclically or according to tag values, e.g. for the
    daily transfer of values into an Excel table.

    Time Series Database can also be created using SQL Database for which expensive historians are used however that also
    includes some additional features such as analytics.

    Siemens Scripting: https://cache.industry.siemens.com/dl/files/206/109773206/att_1026568/v1/109773206


    _WinCC_Scripting_en.pdf

    Script-2 - Creating Table and Columns

    Dim conn, rst, SQL_Table

    Set conn = CreateObject("ADODB.Connection")


    Set rst = CreateObject("ADODB.Recordset")

    conn.Open "Provider=MSDASQL;Initial Catalog=" & SmartTags("database") &


    ";DSN="&Database_Name&""

    SQL_Table = "CREATE TABLE "& SmartTags("tablename") & " (Nr SMALLINT, " _
    & SmartTags("col_1") & " CHAR(30), " & SmartTags("col_2") & " SMALLINT, " _
    & SmartTags("col_3") & " SMALLINT)"

    Set rst = conn.Execute(SQL_Table)

    conn.close

    Set rst = Nothing


    Set conn = Nothing

    End Sub

    'Create tags of tablename and col in string

    Script-3 - Write Data

    Dim conn, rst, SQL_Table

    Set conn = CreateObject("ADODB.Connection")


    Set rst = CreateObject("ADODB.Recordset")

    conn.Open "Provider=MSDASQL;Initial Catalog=" & SmartTags("database") &


    ";DSN="&Database_Name&"" 'DSN= Name of the ODBC database - DSN= Name der ODBC-Datenbank

    SQL_Table = "SELECT * FROM " & SmartTags("tablename") & " WHERE Nr = " & SmartTags("nDat_No")

    Set rst = conn.Execute(SQL_Table)

    SQL_Table = "INSERT INTO "& SmartTags("tablename") & " VALUES ('" & SmartTags("nDat_No") &
    _
    "' , '" & SmartTags("val1") & "' , '" & SmartTags("val2") & _
    "' , '" & SmartTags("val3") & "')"

    Set rst = conn.Execute(SQL_Table)

    conn.close

    Set rst = Nothing


    Set conn = Nothing

    Script-4 - Read Data

    Dim conn, rst, SQL_Table

    Set conn = CreateObject("ADODB.Connection")


    Set rst = CreateObject("ADODB.Recordset")

    conn.Open "Provider=MSDASQL;Initial Catalog=" & SmartTags("database") & ";DSN="&Database_Name&""

    Main Page 1
    SQL_Table = "SELECT * FROM " & SmartTags("tablename") & " WHERE Nr = " & SmartTags("s_no")

    Set rst = conn.Execute(SQL_Table)

    SmartTags("s_no") = rst.Fields(0).Value
    SmartTags("val1") = rst.Fields(1).Value
    SmartTags("val2") = rst.Fields(2).Value
    SmartTags("val3") = rst.Fields(3).Value
    SmartTags("col1") = rst.Fields(1).Name
    SmartTags("col2") = rst.Fields(2).Name
    SmartTags("col3") = rst.Fields(3).Name

    rst.close

    conn.close

    Set rst = Nothing


    Set conn = Nothing

    End Sub

    Main Page 2

    You might also like