CODEWITHSUNDEEP
sqlvbscript
myacobucci
myacobucci

Reputation: 185

How to evaluate stored functions?

I have the following saved in a database. 

    Function SearchFileForName()
        SearchFileForName = False
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set csvFile = fso.OpenTextFile("ListOfUsers.csv", 1)
        Do Until csvFile.AtEndOfStream
            if app.userID = csvFile.ReadLine then
                SearchFileForName = true
            end if
        loop
    end Function        

    Function WriteNameToFile()
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set csvFile = fso.OpenTextFile("ListOfUsers.csv", 8)
        csvFile.WriteLine app.userID
    end function        

    Function theMessage()
        if weekday(Now()) = vbfriday then
            response = msgbox ("Great Job "& split(app.userid, ".")(0) & "!" & _
                     vbnewline & vbnewline & _
                     "Keep up the good work and keep that morale high." & _ 
                      vbnewline & vbnewline & _
                     "Would you like a cookie for your efforts?", vbyesno)

            if response = vbyes then
                msgbox "Enjoy =)" & _
                             vbnewline & _
                             vbnewline & _
                       vbnewline & _
        "             _ . : : : : : . _" & vbnewline & _
        "         . : : : ` _ | _ ` : : : ." & vbnewline & _
        "     /  : : `   - - | - -   ` : : \" & vbnewline & _
        "   | : `   . - - - ` - - - .  `  : |" & vbnewline & _
        "   | :  (   O   R   E   O   )  : |" & vbnewline & _
        "   | : :   ` - - - - - - - `   : : |" & vbnewline & _
        "     \   : : : . . .  . . . . : : :  /" & vbnewline & _
        "         ` : : : : : : : : : : : `" & vbnewline & _
         "               ` ` ` ` ` ` `" & vbnewline

            elseif response = vbno then
                msgbox "That is too bad." & vbnewline & _
                             "Here is a cookie anyways." & vbnewline & _
                       "Enjoy =)" & _
                             vbnewline & _
                             vbnewline & _
                       vbnewline & _
        "             _ . : : : : : . _" & vbnewline & _
        "         . : : : ` _ | _ ` : : : ." & vbnewline & _
        "     /  : : `   - - | - -   ` : : \" & vbnewline & _
        "   | : `   . - - - ` - - - .  `  : |" & vbnewline & _
        "   | :  (   O   R   E   O   )  : |" & vbnewline & _
        "   | : :   ` - - - - - - - `   : : |" & vbnewline & _
        "     \   : : : . . .  . . . . : : :  /" & vbnewline & _
        "         ` : : : : : : : : : : : `" & vbnewline & _
         "               ` ` ` ` ` ` `" & vbnewline
            end if
        end if
    End Function        

    Function easterEgg()
        if not SearchFileForName() then
            WriteNameToFile
            theMessage
        end if
    end Function

So I call it with the following sql query

    Function easterEgg0()
        Set rseasterEgg =  CreateObject("ADODB.RecordSet")
            rseasterEgg.Open _
                "   SELECT  dyCode " & _
                "   FROM        DDCode " & _
                "   WHERE       dyName = 'EasterEggScript'", _
                 Connection, adOpenStatic, adLockBatchOptimistic, adCmdText
            Execute rseasterEgg.fields("dyCode").value
            Call easterEgg
    End Function

When I print it out it looks exactly as expected. But when I try to run it I get an error saying Typemismatch: 'SearchFileForName'?

What am I doing wrong?

Upvotes: 0

Views: 50

Answers (1)

Kul-Tigin
Kul-Tigin

Reputation: 16950

From MSDN - Execute Statement

The context in which the Execute statement is invoked determines what objects and variables are available to the code being run. In-scope objects and variables are available to code running in an Execute statement. However, it is important to understand that if you execute code that creates a procedure, that procedure does not inherit the scope of the procedure in which it occurred.

Like any procedure, the new procedure's scope is global, and it inherits everything in the global scope. Unlike any other procedure, its context is not global scope, so it can only be executed in the context of the procedure where the Execute statement occurred. However, if the same Execute statement is invoked outside of a procedure (i.e., in global scope), not only does it inherit everything in global scope, but it can also be called from anywhere, since its context is global.

To overcome this use ExecuteGlobal instead.

Function easterEgg0()
    Set rseasterEgg =  CreateObject("ADODB.RecordSet")
        rseasterEgg.Open _
            "   SELECT  dyCode " & _
            "   FROM        DDCode " & _
            "   WHERE       dyName = 'EasterEggScript'", _
             Connection, adOpenStatic, adLockBatchOptimistic, adCmdText
        ExecuteGlobal rseasterEgg.fields("dyCode").value
        Call easterEgg
End Function

Upvotes: 1

Related Questions