Learning Foundation: Database Operations
This page has one defined job in the WinCC VBScript learning path. Master this foundation before using the same concept inside larger SCADA, SQL Server and reporting scripts.
SQL database/table exists; understand ADODB connections and error handling.
CRUD scripts should validate WinCC values, use controlled SQL statements/parameters where feasible, verify affected records and clean up database objects.
Insert, read, update and delete SQF furnace event records in dbo.tblEvent using the same project fields.
Implement practical Create/Read/Update/Delete patterns with transactions, diagnostics and predictable cleanup.
Collect validated process values
Open database connection
INSERT / SELECT / UPDATE / DELETE
Commit/rollback or verify
Trace/result to operator
Complete WinCC VBScript Learning Path
Use Previous/Next for the recommended practical order, or open any topic below as a reference.
SQL Server CRUD Operations with WinCC VBScript and ADODB
This lab targets wincc vbscript sql server crud with copy-ready examples, expected results and diagnostic guidance.
1. Use a Least-Privilege ADODB Connection
Dim connection
Set connection = CreateObject("ADODB.Connection")
connection.Open "Provider=MSOLEDBSQL;Server=SOFTWELL\WINCC;" & _
"Database=SQF_DB;Trusted_Connection=Yes;"
If connection.State <> 1 Then
HMIRuntime.Trace "SQL connection did not open"
End IfEasy TestEasy Test 1 — InputBox + MsgBox
Live connection test only; it does not modify dbo.tblEvent.
On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 1 — InputBox + MsgBox", "SOFTWELL\WINCC")
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"
If Err.Number <> 0 Then
MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
Err.Clear
Else
MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If
If Not Conn Is Nothing Then
If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0Use the installed, approved provider and the Runtime service identity. Grant only the required operations on the intended tables.
2. Insert with ADODB Parameters
Dim command
Set command = CreateObject("ADODB.Command")
Set command.ActiveConnection = connection
command.CommandText = "INSERT INTO dbo.ProcessLog " & _
"(EventTime, Temperature) VALUES (?, ?)"
command.CommandType = 1
command.Parameters.Append command.CreateParameter("pTime", 135, 1, , Now)
command.Parameters.Append command.CreateParameter("pTemp", 5, 1, , 650.5)
command.ExecuteEasy TestEasy Test 2 — InputBox + MsgBox
Shows the exact timestamp formatting used later in tblEvent.TM.
Dim LabelText, TM
LabelText = InputBox("Enter a label for this timestamp", "Easy Test 2 — InputBox + MsgBox", "SQF Event")
TM = Right("0" & Hour(Now), 2) & ":" & _
Right("0" & Minute(Now), 2) & ":" & _
Right("0" & Second(Now), 2)
MsgBox LabelText & vbCrLf & _
"Date/Time = " & Now & vbCrLf & _
"TM = " & TM, vbInformation, "Date/Time Quick Test"Parameter markers represent values, not table or column names. Keep identifiers as reviewed constants.
3. Read Records Safely
Dim recordset
Set recordset = connection.Execute( _
"SELECT TOP (10) EventTime, Temperature " & _
"FROM dbo.ProcessLog ORDER BY EventTime DESC")
Do Until recordset.EOF
HMIRuntime.Trace CStr(recordset.Fields("EventTime").Value) & _
" | " & CStr(recordset.Fields("Temperature").Value)
recordset.MoveNext
LoopEasy TestEasy Test 3 — InputBox + MsgBox
Change the cycle count to see how repeated execution works.
Dim CountTo, i, ResultText
CountTo = CInt(InputBox("How many loop cycles?", "Easy Test 3 — InputBox + MsgBox", "5"))
ResultText = ""
For i = 1 To CountTo
ResultText = ResultText & "Cycle " & i & vbCrLf
Next
MsgBox ResultText, vbInformation, "Loop Quick Test"4. Update and Delete with Guard Conditions
Every UPDATE and DELETE needs a reviewed WHERE clause based on a key. Test against disposable rows, check affected-record counts and never expose unrestricted SQL text to an operator field.
5. Transactions and Cleanup
Use BeginTrans, CommitTrans and RollbackTrans when several writes must succeed together. Close the recordset, release the command, close the connection and handle rollback on every failure path.
6. SQL CRUD Troubleshooting
| Problem | Action |
|---|---|
| Provider not found | Install/approve the matching OLE DB provider and check process bitness |
| Login failed | Verify the WinCC Runtime identity and SQL permissions |
| Parameter type error | Match ADODB parameter type, size and direction to the SQL column |
| Database remains locked | Close recordsets/connections and avoid long transactions in Runtime scripts |
Hands-On Verification Lab
Hands-onPrepare a controlled test
Create only the internal tags, folders or disposable database rows required by this tutorial.
Run the smallest example
Execute one operation and inspect WinCC diagnostics before expanding the script.
Test a failure path
Use a safe backup copy to test an invalid tag, path or disposable input.
Frequently Asked Questions
Should this WinCC VBScript be tested in production?
No. Use a backed-up training or staging project and follow the plant change-control process.
Which WinCC versions does the example target?
The examples target classic WinCC Explorer V7.x/V8.x. Verify object properties, action signatures and providers in the installed WinCC help.
How should Runtime failures be diagnosed?
Use scoped Err checks, WinCC object diagnostics and HMIRuntime.Trace while recording the exact station, trigger, tag and Runtime identity.
SQF Running Project Lab · Blog 16
Insert one WinCC process snapshot into tblEvent
SQL = "INSERT INTO dbo.tblEvent " & _
"(DT,TM,SQF_No,ChargeNo,Event_From,Event_To,Temp_Set,Temp_Act," & _
"Cp_Set,Cp_Act,Oil_Set,Oil_Act,Jacket_Set,Jacket_Act,Fan_Status) VALUES (" & _
"SYSDATETIME(),'" & TM & "'," & SQFNo & ",'" & Charge & ",'" & _
EventFrom & "','" & EventTo & "'," & TempSet & "," & TempAct & "," & _
CpSet & "," & CpAct & "," & OilSet & "," & OilAct & "," & _
JacketSet & "," & JacketAct & "," & FanStatus & ")"
Conn.Execute SQLEasy TestEasy Test 4 — InputBox + MsgBox
Dialog simulation of Set / object reference / Nothing before live Runtime testing.
Dim ObjectName
ObjectName = InputBox("Enter the object/tag name you want to test", "Easy Test 4 — InputBox + MsgBox", "Temp_Act")
MsgBox "Object reference concept:" & vbCrLf & _
"Set obj = HMIRuntime.Tags("" & ObjectName & "")" & vbCrLf & _
"After use: Set obj = Nothing", vbInformation, "Object Quick Test"