Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84650members
353990posts

[Imported] MsgBox Vba script combinations. vba SQL formatting.

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
sbeadle
Janeway Janeway
Janeway
0 Likes
1
555

[Imported] MsgBox Vba script combinations. vba SQL formatting.

>>Message imported from previous forum - Category:Scripts and Tips<<
User: ROVSCADAENGINEER, originally posted: 2019-04-30 22:12:01 Id:421
The following script displays a MsgBox based on the number of events from the SQL query. I have two questions.

Can I use relative coding to extract an Object ID on a point and then call this into the SQL in the script?

MsgBox "The reset no is" + SQLrows
MsgBox ( SQLrows, "is the number of resets" ) 40 Then
MsgBox "The resets today are"
Else
MsgBox SQLrows
End If
If SQL.Error Then
Msgbox SQL.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"
End If
**
Any help appreciated!**


Accepted Solutions
sbeadle
Janeway Janeway
Janeway
0 Likes
0
554

Re: [Imported] MsgBox Vba script combinations. vba SQL formatting.

>>Responses imported from previous forum


Reply From User: ROVSCADAENGINEER, posted: 2019-05-01 01:10:58
Ok so I ended up getting the timeframing to work. And I configured it in a way where the value is displayed in certain prompts. Yet I cannot seem to figure out now how to use relative querying? is this even possible? Say for instance I want to query a datapoint based on it's name and relative location to where the script is. Could I declare an object ID from findObject and then call that into the SQL somehow?

Code for reference below............

..
Dim iResponse
Dim iResponse2
Dim iResponse3
Dim SQLrows
Dim Value
Value = 2
Set SQL=Server.Query ("SELECT TOP(Value) * FROM CDBEVENTJOURNAL WHERE ID = 345005 AND RECORDTIME BETWEEN { OPC 'Day' } AND { OPC 'Day + 24 Hours' }")
SQLrows = SQL.RowCount
If Not(SQL.Error) Then '

If SQLrows 2 Then
iResponse = MsgBox ("You are about to reset the pump when there has been more than 2 resets. Actuating consectutive resets may cause damage to the pump. check number?", vbExclamation + vbOKCancel + vbDefaultButton1, "Warning")
If iResponse = vbOK Then
iResponse2 = MsgBox( SQLrows, vbExclamation + vbOKCancel + vbDefaultButton2, "Number of Pump Resets!")
If iResponse2 = vbOK Then
Server.SetOPCValue ".Reset.CurrentValue", 1
MsgBox "Pump Reset"

Else
Server.SetOPCValue ".Reset.CurrentValue", 0
MsgBox "Reset Cancelled"
End If
Else
Server.SetOPCValue ".Reset.CurrentValue", 0
MsgBox "Reset Cancelled"
End If
Else
Server.SetOPCValue ".Reset.CurrentValue", 1
MsgBox "Pump Reset"
End If

If SQL.Error Then
Msgbox SQL.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"
End If
End If

 


Reply From User: ROVSCADAENGINEER, posted: 2019-05-01 02:36:44
so I figured out how to get the object ID relative to the mimics position. I am now working on a way to match any Object ID's from the query so the script can run logic based only on that point. the code to do this is below.

 

Dim ResetID
ResetID=Server.GetOPCValue(".Reset.ID")
MsgBox "The Object ID is " & ResetID

See Answer In Context

1 Reply 1
sbeadle
Janeway Janeway
Janeway
0 Likes
0
555

Re: [Imported] MsgBox Vba script combinations. vba SQL formatting.

>>Responses imported from previous forum


Reply From User: ROVSCADAENGINEER, posted: 2019-05-01 01:10:58
Ok so I ended up getting the timeframing to work. And I configured it in a way where the value is displayed in certain prompts. Yet I cannot seem to figure out now how to use relative querying? is this even possible? Say for instance I want to query a datapoint based on it's name and relative location to where the script is. Could I declare an object ID from findObject and then call that into the SQL somehow?

Code for reference below............

..
Dim iResponse
Dim iResponse2
Dim iResponse3
Dim SQLrows
Dim Value
Value = 2
Set SQL=Server.Query ("SELECT TOP(Value) * FROM CDBEVENTJOURNAL WHERE ID = 345005 AND RECORDTIME BETWEEN { OPC 'Day' } AND { OPC 'Day + 24 Hours' }")
SQLrows = SQL.RowCount
If Not(SQL.Error) Then '

If SQLrows 2 Then
iResponse = MsgBox ("You are about to reset the pump when there has been more than 2 resets. Actuating consectutive resets may cause damage to the pump. check number?", vbExclamation + vbOKCancel + vbDefaultButton1, "Warning")
If iResponse = vbOK Then
iResponse2 = MsgBox( SQLrows, vbExclamation + vbOKCancel + vbDefaultButton2, "Number of Pump Resets!")
If iResponse2 = vbOK Then
Server.SetOPCValue ".Reset.CurrentValue", 1
MsgBox "Pump Reset"

Else
Server.SetOPCValue ".Reset.CurrentValue", 0
MsgBox "Reset Cancelled"
End If
Else
Server.SetOPCValue ".Reset.CurrentValue", 0
MsgBox "Reset Cancelled"
End If
Else
Server.SetOPCValue ".Reset.CurrentValue", 1
MsgBox "Pump Reset"
End If

If SQL.Error Then
Msgbox SQL.ErrorMessage, vbCritical + vbOKOnly, "Error in SQL Command"
End If
End If

 


Reply From User: ROVSCADAENGINEER, posted: 2019-05-01 02:36:44
so I figured out how to get the object ID relative to the mimics position. I am now working on a way to match any Object ID's from the query so the script can run logic based only on that point. the code to do this is below.

 

Dim ResetID
ResetID=Server.GetOPCValue(".Reset.ID")
MsgBox "The Object ID is " & ResetID