Update 4

Updated the whole question to reflect my changes. Still not working.

This has annoyed me for two days now. I am updating an old ordering interface system our clients use written in ASP Classic, VBScript. It connects to SQL Database on Windows Server 2003.

Stored procedure

I have a stored procedure that returns a list of pallet codes, filtered by customer ID, and can be found by pallet code:

CREATE PROCEDURE dbo.sp_PalletSearch
@CustomerRef        Int,
@SearchQuery        VarChar(15) = '%'
AS

SET NoCount On  
SET @SearchQuery = '%' + COALESCE(@SearchQuery, '%') + '%'

SELECT      p.PalletID,
            p.PalletCode
FROM        dbo.v_PalletSearch p
WHERE       p.CustomerRef   = @CustomerRef
AND         p.PalletCode    LIKE @SearchQuery
ORDER BY    p.PalletCode    ASC

SET NoCount Off
GO

      

        
        
        
      

    

This seems to work fine in SQL Query Analyzer with and without search term:

exec sp_PalletSearch 100, ''

and exec sp_PalletSearch 100, 'PalletCode'

ASP Web Page

So on the webpage itself ... This is the ADO command I use to get the recordset and this is where my problem begins. It just won't return anything:

Dim strSearchQuery
strSearchQuery = "PalletCode"

Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = cConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_PalletSearch"
objCmd.Parameters.Append objCmd.CreateParameter("@CustomerRef", adInteger, adParamInput)
objCmd.Parameters.Append objCmd.CreateParameter("@SearchQuery", adVarChar, adParamInput, 15)
objCmd.Parameters("@CustomerRef").Value = CustomerID
objCmd.Parameters("@SearchQuery").Value = strSearchQuery

Dim objRS
Set objRS = objCmd.Execute
Set objCmd = Nothing


Do While Not objRS.EOF
    Response.Write(objRS("PalletID").Name & ": " & objRS("PalletID").Value & " | " & objRS("PalletCode").Name & ": " & objRS("PalletCode").Value & "<br>")
    objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing

      

        
        
        
      

    

I tried...

If I edit this line in my ADO command:

objCmd.CommandText = "sp_PalletSearch"

      

        
        
        
      

    

And change it to:

objCmd.CommandText = "{call sp_PalletSearch(?, '" & strSearchQuery & "')}"

      

        
        
        
      

    

And remove:

objCmd.CommandType = adCmdStoredProc

      

        
        
        
      

    

All search jobs are fine. This is what I will stick with if no real solution is found.

If I edit the stored procedure to get the pallet code which is equal to the search term and not LIKE and comment out

--SET @SearchQuery = '%' + COALESCE(@SearchQuery, '%') + '%'

      

        
        
        
      

    

then I will get an exact match. This will tell me that the ADO command is passing parameters ok. But why won't the stored procedure get LIKE results @SearchQuery

?

Another thing to note is that replacing the ADO command with the following works fine with the LIKE pallet code. I don't see this snippet as a safe option, please tell me I'm wrong. I would rather use a parameterized command:

strSQL = "EXECUTE sp_PalletSearch " & CustomerID & ", '" & strSearchQuery & "' "
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open cConn
objRS.Open strSQL, objConn  

      

        
        
        
      

    

This is a big question, but I enjoy doing things effectively and correctly, and I love learning. Hope you guys can help me with this puzzle.