Scriptcontrol object returns recordset object

Question

I am creating a library using vbscript and using scriptcontrol object to call these routines from vba. I am trying to create a recordset as a routine with the given sql as parameter as below

sub GetData(sql, byref retrst)

stADO = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial 
         Catalog=CCMSProd;Data Source=sv-hfi-ccms;UID=vc;PWD=dw;"

cnconn as adoconnection
Set cnconn = CreateObject("ADODB.Connection")

cnconn.Open stADO

msgbox cnconn.ConnectionString
msgbox cnconn.state

    With cnconn
        .CommandTimeout = 0
        Set retrst = .Execute(sql)
        msgbox retrst.recordcount
    End With
 cnconn.close
end sub

and calling this routine as below

          scr.Run "GetConnection", tempload, rst

but my recordset returns as nothing, eventhough the connection and recordset created within vbscript.
I need help in this.

Answer 1

There are quite a few problems with your script. I'm not going to go in to each one but be aware that the recordset open call has more parameters. They aren't required but the defaults are adForwardOnly for the cursor type, and adLockReadOnly for locking. Using the defaults will not enable you to get a record count from the recordset so the code I provide just shows the value of the first record in the recordset to indicate data was retrieved.

https://support.microsoft.com/en-us/help/272067/how-to-get-a-record-count-from-a-sql-server-ce-recordset

The code below works but you'll need to modify it for your usage. Batch file to run the script:

GetRecordset.bat

cscript.exe /nologo GetRecordset.vbs
pause

Script to run:

GetRecordset.vbs

Dim objADORecordset
Dim strDBTableName

On Error Resume Next

'*****Modify below to a table name in your database*****
strDBTableName = "SomeDBTableName"

Set objADORecordset = CreateObject("ADODB.Recordset")
If Err.Number = 0 Then
    GetRecordset "SELECT * FROM " & strDBTableName, objADORecordset
    With objADORecordset
        If Not .BOF Then
            .MoveFirst
            WScript.Echo "Field 0: " & .Fields(0).Value
        Else
            WScript.Echo "No records in recordset"
        End If
    End With
Else
    WScript.Echo "Unable to create ADO Recordset"
End If
set objADORecordset = Nothing

WScript.Quit

'********************************************* 
Sub GetRecordset(astrSQLQuery, aobjRecordset)
    Dim strOLEConnect
    Dim strUserID
    Dim strUserPW
    Dim objADOConnection

    On Error Resume Next

    WScript.Echo "SQLQuery: " & astrSQLQuery

    strOLEConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=CCMSProd;Data Source=sv-hfi-ccms;UID=vc;PWD=dw;"
    strUserID = "vc"
    strUserPW = "dw"

    Set objADOConnection = CreateObject("ADODB.Connection")
    If Err.Number = 0 Then
        objADOConnection.Open strOLEConnect, strUserID, strUserPW
        If Err.Number = 0 Then
            aobjRecordset.Open astrSQLQuery, objADOConnection
            If Err.Number <> 0 Then
                WScript.Echo "Recordset open failed ERROR=" & Err.Number
            End If
        Else
            WScript.Echo "Connection open failed ERROR=" & Err.Number
        End If
    Else
        WScript.Echo "ADO connection failed ERROR=" & Err.Number
    End If
    Set objADOConnection = Nothing
End Sub

I left it similar to your code so you can understand it but it should really create and open the ADO connection before the ADORecordset. You would typically open the connection, run all of the required queries and process them before closing the connection. There's no sense in creating a new connection to the same database multiple times for different queries to the same database. It just adds unnecessary overhead. I'll leave it to you to improve the code for your use.