ADO 命令参数未传递给存储过程或存储过程“忽略"参数

2022-01-16 00:00:00 sql asp-classic sql-server vbscript ado

这已经困扰我两天了.我正在更新我们的客户使用的旧订购界面系统，它是用 ASP Classic、VBScript 编写的.它连接到 Windows Server 2003 上的 SQL 数据库.

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.

我有一个存储过程，它返回一个托盘代码列表，按客户 ID 过滤并可以按托盘代码搜索:

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

无论有没有搜索词，这在 SQL 查询分析器中似乎都可以正常工作:

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

exec sp_PalletSearch 100, '' 和 exec sp_PalletSearch 100, 'PalletCode'

所以到网页本身...这是我用来获取记录集的 ADO 命令，这就是我的问题开始的地方.它只是不会返回任何东西:

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

我试过了...

如果我在我的 ADO 命令中编辑这一行:

I Have Tried...

If I edit this line in my ADO Command:

objCmd.CommandText = "sp_PalletSearch"

并将其更改为:

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

然后删除:

objCmd.CommandType = adCmdStoredProc

所有搜索工作正常.如果找不到真正的解决方案，我会坚持这样做.

All searching works fine. This is what I will stick to if a real solution isn't found.

如果我编辑存储过程以获取 等于 搜索词而不是 LIKE 的托盘代码，并注释掉

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, '%') + '%'

然后我会得到完全匹配的.这将告诉我 ADO 命令正在传递参数正常.但是那么为什么存储过程不会得到结果LIKE @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?

要注意的另一件事是，将 ADO 命令替换为以下代码对于托盘代码 LIKE 效果很好.我不认为这个片段是一个安全的选项，如果我错了，请告诉我.我宁愿使用参数化命令:

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.

推荐答案

已解决

感谢 Bond，尤其是 Lankymart 的帮助.Lankymart，您使用 SQL Profiler 的建议有所帮助.我猜我的服务器有旧版本 - Profiler.

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.

我在查看 Profiler Trace 时发现了这一点:@SearchQuery = 'bww100052'

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

所以我决定在存储过程中强制进行修剪:LTRIM(RTRIM(@SearchQuery))

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

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 命令

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.

相关文章