ADO Command Parameter Not Passing to Stored Procedure or Stored Procedure 'Ignoring' Parameter

Question

Update 4

Updated the whole question to reflect my changes. Still Not Working.

This has been annoying me for two days now. I'm updating an old ordering interface system that our customers use, written in ASP Classic, VBScript. It connects to an 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 searchable 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 a search term:

exec sp_PalletSearch 100, '' and exec sp_PalletSearch 100, 'PalletCode'

ASP Web Page

So onto the web page itself... This is the ADO Command I use to get the recordset and this is where my problem starts. It just simply will not 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 Have 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 searching works fine. This is what I will stick to if a real solution isn't found.

---

If I edit the stored procedure to get the pallet code that equals the search term instead of LIKE, and comment out

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

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

---

Another thing to note is that replacing the ADO Command with the following works fine with pallet code LIKE. I don't see this snippet as a secure option, please tell me if I'm wrong. I would rather use the parametrised 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  

---

It's a big ask, but I like to do things efficiently and correctly, and I love to learn. I hope you guys can help me with this puzzle.

Answer 1

I think you causing yourself more issues by trying anything and everything. With each attempt you make slight mistakes in your syntax (like quotes in the wrong place, not specifying a CommandType etc).

If it helps this is how I would code for that stored procedure

Dim cmd, rs, sql
Dim data, rows, row

Set cmd = Server.CreateObject("ADODB.Command")
'Name of your stored procedure
sql = "dbo.sp_PalletSearch"

With cmd
  .ActiveConnection = cConn 'Assuming cConn is a connection string variable
  .CommandType = adCmdStoredProc
  .CommandText = sql
  'Define Stored Procedure parameters
  Call .Parameters.Append(.CreateParameter("@CustomerRef", adInteger, adParamInput, 4))
  Call .Parameters.Append(.CreateParameter("@SearchQuery", adVarChar, adParamInput, 15))
  'First parameter is optional so only pass if we have a value, will default to NULL.
  If Len(CustomerId) > 0 Then .Parameters("@CustomerRef").Value = CustomerID
  .Parameters("@SearchQuery").Value = strSearchQuery
  Set rs = .Execute()

  'Populate 2D-Array with data from Recordset
  If Not rs.EOF Then data = rs.GetRows()

  'Close and Release Recordset from memory
  Call rs.Close()
  Set rs = Nothing
End With
Set cmd = Nothing

If IsArray(data) Then
  rows = UBound(data, 2)
  For row = 0 To rows
    Call Response.Write("Pallet Id: " & data(0, row) & " | Pallet Code: " & data(1, row) & "</ br>")
  Next
End If

Answer 2

Solved

Thank you to Bond and especially Lankymart for your help. Lankymart, your suggestion to use SQL Profiler helped. My server has the older version I guess - Profiler.

I found this when looking in the Profiler Trace: @SearchQuery = 'bww100052 '

So I decided to force a Trim inside the stored procedure: LTRIM(RTRIM(@SearchQuery))

Stored Procedure

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

SET NoCount On  
SET @SearchQuery = '%' + COALESCE(LTRIM(RTRIM(@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

ADO Command

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 = Trim(strSearchQuery)

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

Finally

I thought I would never solve this one, it was just making no sense at all! I'll throw a few more tests at it, but it looks like trimming the variable was needed. I don't know why the extra space was added though.

Answer 3

Try it with

objCmd.Parameters.Append objCmd.CreateParameter("@SearchQuery", adVarChar, adParamInput, 15, "'PalletCode'")

Notice that "'PalletCode'" has an additional set of single quotes inside of it.