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

[sbeadle]

>>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!**

[sbeadle · Accepted solution]

>>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