Executing User Defined Function via Classic ASP Function

Question

OK, I am trying to write a Classic ASP function that will call a SQL function and return the output. I am trying to use a paramaterized ADODB connection but I don't quite know how these work. Trying to learn the correct way.

The SQL function just takes two string input where one is a "salt" and the other the actual text and turnes it into a hex. Works fine in SQL but I just can not get it to work through classic ASP.

I keep getting, ADODB.Command error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

Sub Encrypt(plainString)
    strSQL = "SET NOCOUNT ON;SELECT dbo.Encrypt('xx', '?') as keycode"
    Set cnnEncrypt = Server.CreateObject("ADODB.Connection")
    cnnEncrypt.open CONNSTRING

    Dim cmd1
    Set cmd1 = Server.CreateObject("ADODB.Command")
    cmd1.ActiveConnection = cnnEncrypt
    cmd1.CommandText = strSQL
    cmd1.CommandType = adCmdText
    cmd1.Parameters(0) = plainString      (**Original Error Occured Here!!!!**)
    Set rsEncrypt = cmd1.Execute()

    If not rsEncrypt.EOF Then
        Encrypt = rsEncrypt.Fields("keycode").Value
    Else
        Encrypt = "blank"
    End If

    ' Clean Up    
    rsEncrypt.Close    
    Set rsEncrypt = Nothing    
    cnnEncrypt.Close    
    Set cnnEncrypt = Nothing    

End Sub

New Working Version after reviewing "Cheran Shunmugavel" answer. Calling on site like this

< % Response.Write Decrypt(Encrypt("test")) % >

You can't print the Encrypted code to the page because it is Binary. You would need a Binary to String function. I converted from Sub to Function because I wanted the function to return a value.

Function Encrypt(byVal plainString)
    strSQL = "SET NOCOUNT ON;SELECT dbo.Encrypt('xx', ?) as keycode"
    Set cnnEncrypt = Server.CreateObject("ADODB.Connection")
    cnnEncrypt.open CONNSTRING

    Dim cmd1
    Set cmd1 = Server.CreateObject("ADODB.Command")
    cmd1.ActiveConnection = cnnEncrypt
    cmd1.CommandText = strSQL
    cmd1.CommandType = adCmdText
    cmd1.Parameters.Append cmd1.CreateParameter("", adVarChar, adParamInput, Len(plainString)+1, plainString)  
    Set rsEncrypt = cmd1.Execute()

    If not rsEncrypt.EOF Then
        Encrypt = rsEncrypt.Fields("keycode").Value
    Else
        Encrypt = "blank"
    End If

    ' Clean Up    
    rsEncrypt.Close    
    Set rsEncrypt = Nothing    
    cnnEncrypt.Close    
    Set cnnEncrypt = Nothing    
End Function

And here is the decrypt function.

Function Decrypt(byVal plainString)
    strSQL = "SET NOCOUNT ON;SELECT dbo.Decrypt('xx', ?) as keycode"
    Set cnnDecrypt = Server.CreateObject("ADODB.Connection")
    cnnDecrypt.open CONNSTRING

    Dim cmd1
    Set cmd1 = Server.CreateObject("ADODB.Command")
    cmd1.ActiveConnection = cnnDecrypt
    cmd1.CommandText = strSQL
    cmd1.CommandType = adCmdText
    cmd1.Parameters.Append cmd1.CreateParameter("", adVarBinary, adParamInput, LenB(plainString)+1, plainString)  
    Set rsDecrypt = cmd1.Execute()

    If not rsDecrypt.EOF Then
        Decrypt = rsDecrypt.Fields("keycode").Value
    Else
        Decrypt = "blank"
    End If

    ' Clean Up    
    rsDecrypt.Close    
    Set rsDecrypt = Nothing    
    cnnDecrypt.Close    
    Set cnnDecrypt = Nothing    
End Function

Answer 1

First off, you don't need delimiters around the parameter placeholder. SQL Server will handle it appropriately.

strSQL = "SET NOCOUNT ON;SELECT dbo.Encrypt('xx', ?) as keycode"

---

Secondly, the Parameters collection is initially empty and must be populated before you try to access it (i.e., the line cmd1.Parameters(0) = plainString). There are several ways of doing this, but I prefer creating the parameters manually using the CreateParameter method:

cmd1.Parameters.Append cmd1.CreateParameter("", adVarChar, adParamInput, Len(plainString), plainString)

---

Also, it's not apparent from your code, but make sure you've got the ADO constants defined, either by referencing the type library, or by including adovbs.inc.