Returning Multiple Property Bags in VBScript

Question

I'm trying to return two values from my VBScript and stored them in separate property bags:

Contains the result of the If condition

    Call oBag.AddValue("Status","Bad")
    Call objAPI.AddItem(oBag)
Else
    Call oBag.AddValue("Status","Good")
    Call objAPI.AddItem(oBag)

Contains the output of the SQL query that been executed

Call oBag.AddValue("Count",objRS.Fields("CountStuff"))
Call objAPI.AddItem(oBag)

for passing on next to SCOM.

But while debugging, I'm getting the following error:

(41,1) ADODB.Field: Either BOF or EOF is True, or the current record has beed deleted. Requsted operation requires a current record.

Here is the full VBScript:

Dim objCN, strConnection
Dim objAPI, oBag

Set objCN = CreateObject("ADODB.Connection")
Set objAPI = CreateObject("MOM.ScriptAPI")
Set oBag = objAPI.CreateTypedPropertyBag(2)

strConnection = "Driver={SQL Server};Server=SCOMSRVDB01,2880;Database=DBABee;Trusted_Connection=TRUE"
objCN.Open strConnection

Dim strSQLQuery
strSQLQuery = "Select COUNT (*) as CountStuff from sys.objects"

Dim objRS
Set objRS = CreateObject("ADODB.Recordset")

Set objRS = objCN.Execute(strSQLQuery)

'WScript.Echo (objRS.Fields("CountStuff"))
Call oBag.AddValue("Count", objRS.Fields("CountStuff"))
Call objAPI.AddItem(oBag)

Do Until objRS.EOF
    'WScript.Echo objRS.Fields("CountStuff")
    If objRS.Fields("CountStuff") > 0 Then
        'WScript.Echo "evaluated as bad"
        Call oBag.AddValue("Status","Bad")
        Call objAPI.AddItem(oBag)
    Else
        Call oBag.AddValue("Status","Good")
        Call objAPI.AddItem(oBag)
     End If
     objRS.MoveNext
Loop

Call objAPI.ReturnItems
objRS.Close

'Property[@Name='Status']

Your code is missing a check that `objRS.EOF <> True` befor calling `Call oBag.AddValue("Count", objRS.Fields("CountStuff"))` — Siyon DP, Mar 26 '18 at 10:55
Thanks Ansgar Wiechers, i don't need to change anything in the loop itself? — edwio, Mar 27 '18 at 11:13

score 0 · Answer 1 · answered Mar 26 '18 at 19:05

0

The error message seems to be pretty straightforward. You need to use the MoveFirst method to move the current record position to the first record in the objRS recordset.

However, a call to either MoveFirst or MoveLast generates an error when the objRS recordset is empty (both BOF and EOF are True). Hence, call it conditionally as follows:

''' ''' '''
If Not ( objRS.BOF And objRS.EOF ) Then objRS.MoveFirst

Do Until objRS.EOF
    'WScript.Echo objRS.Fields("CountStuff")
    If objRS.Fields("CountStuff") > 0 Then
        'WScript.Echo "evaluated as bad"
        Call oBag.AddValue("Status","Bad")
        Call objAPI.AddItem(oBag)
    Else
        Call oBag.AddValue("Status","Good")
        Call objAPI.AddItem(oBag)
     End If
     objRS.MoveNext
Loop
''' ''' '''

Resource: MoveFirst, MoveLast, MoveNext, and MovePrevious Methods (ADO)

answered Mar 26 '18 at 19:05

JosefZ

28,460
5
44
83

Thanks JosefZ, i update the script following your instructions, and i see the query result (**72**) in the script output: **72** But that means that only 1 Property Bag has been successfully populated with the value of the SQL query that been executed. What about the second Property Bag that supposed to Contains the result of the `If `condition? – edwio Mar 27 '18 at 11:30
@edwio `Select COUNT (*) as CountStuff from sys.objects`: the `COUNT (*)` function returns the total number of rows in the `sys.objects` table i.e. the only number. Maybe something like `Select *, COUNT (*) as CountStuff from sys.objects GROUP BY something`? – JosefZ Mar 27 '18 at 16:18
i think you misunderstood me. i'm well aware for the `COUNT (*)` function. the only thing that i'm trying to accomplish at this moment. is to populate the `IF` contidon (Bad/Good) to another Property Bag. – edwio Mar 28 '18 at 08:55

score 0 · Answer 2 · answered Apr 03 '18 at 10:37

If you need to send only one PropertyBag DataItem you shouldn't add it to Collection.

Set oBag = Call objAPI.CreatePropertyBag()
If Not (objRS.BOF And objRS.EOF) Then
  objRS.MoveFirst
  Call oBag.AddValue("CountStuff", objRS.Fields("CountStuff"))
  Call objAPI.Return(oBag)
End If
Call objRS.Close()

And better to compare Bad/Good states in System.ExpressionFilter if it hasn't hard boolean logic:

<Expression>
  <SimpleExpression>
    <ValueExpression>
      <XPathQuery Type="Integer">Property[@Name="CountStuff"]</XPathQuery>
    </ValueExpression>
    <Operator>Greater</Operator>
    <ValueExpression>
      <Value Type="Integer">0</Value>
    </ValueExpression>
  </SimpleExpression>
</Expression>

Returning Multiple Property Bags in VBScript

2 Answers2