Clearscript vbscript com ado interaction

Question

I'm using clearscript as a vbscript execution engine.

I have exposed the following C# object (I appreciate it doesn't actually execute a query, I'm just testing for now):

public class SCB
{
        public ADODB.Recordset executeQuery(string q)
        {
            ADODB.Recordset _recordset = new ADODB.Recordset();

            _recordset.Fields.Append("Id", ADODB.DataTypeEnum.adInteger);
            _recordset.Fields.Append("Name", ADODB.DataTypeEnum.adVarChar, 20);
            _recordset.Open(System.Reflection.Missing.Value
                    , System.Reflection.Missing.Value
                    , ADODB.CursorTypeEnum.adOpenStatic
                    , ADODB.LockTypeEnum.adLockOptimistic, 0);
            _recordset.AddNew(Type.Missing, Type.Missing);
            _recordset.Fields["Name"].Value = "Test";

            return _recordset;
        }
    }
}

I have created a vbscript host using:

_engine = new VBScriptEngine(WindowsScriptEngineFlags.EnableDebugging);

And added the class as an object with this line:

_engine.AddHostObject("SCB", HostItemFlags.GlobalMembers, new SCB());

If I run some vbscript code via the following call I receive an error (described below):

_engine.ExecuteCommand(code);

The code I am executing is the following:

Function GetUniversalMessage
    dim lRS, sMessage, sColour,sTimeout
    Set Lrs = SCB. executeQuery ("SELECT MESSAGE, TIMEOUT, COLOUR, ENABLED FROM U_SCROLLER WHERE SCROLLER_ID=1 AND ENABLED='Y' AND (DISABLE_TIME IS NULL OR DISABLE_TIME>GETDATE())")
    if not lRS.EOF then
    End If
End Function

I receive an exception that lrs.eof is not a valid field ... but it is valid for an ado com object, and if I check the object created in executeQuery before it returns to the script engine, the EOF field is present. When I debug by attaching the debugger and calling the stop command, I can see that the lrs object in vbscript does not contain EOF, or most of the valid Recordset fields.

Can someone assist me in explaining what clearscript is doing to the object before it is inserted into the vbscript engine?

Thanks,

Rob

Do you have any resources on how to call vbscript from c# other than the skinny MS FAQ page? If so kindly let me know. I can open a new question if you like. Thanks. — NoChance, Dec 13 '20 at 23:49

score 0 · Accepted Answer · answered Apr 08 '15 at 13:10

0

Check whether you're using embedded Interop types. That feature is problematic for scripting because the embedded types are stripped of any members you don't use in managed code.

For example, if you don't use EOF in managed code, the metadata for the EOF property will be left out of the embedded version of Recordset.

In Visual Studio, try setting the Embed Interop Types property of your ADODB reference to False.

Another thing to try would be to change the executeQuery return type to object, or add the attribute [ScriptMember(ScriptMemberFlags.ExposeRuntimeType)].

answered Apr 08 '15 at 13:10

BitCortex

3,328
1
15
19

Thank you so much BitCortex. Your answer is detailed and absolutely correct. – Rob Clayton Apr 09 '15 at 00:33
I am now facing a subsequent problem in that a call to lRS.MoveFirst is failing with an 'Invocation Failed', unknown binding error. `code Function GetUniversalMessage dim lRS, sMessage, sColour,sTimeout Set Lrs = SCB. executeQuery ("SELECT MESSAGE, TIMEOUT, COLOUR, ENABLED FROM U_SCROLLER WHERE SCROLLER_ID=1 AND ENABLED='Y' AND (DISABLE_TIME IS NULL OR DISABLE_TIME>GETDATE())") lRS.MoveFirst End Function – Rob Clayton Apr 09 '15 at 03:15
The issue regarding the unknown binding error was due to using the NuGet package version of clearscript, which is version 5.3.11 rather than the latest which is (currently) 5.4.1. Once I updated, the binding error was resolved. – Rob Clayton Apr 09 '15 at 05:57

Clearscript vbscript com ado interaction

1 Answers1