ADO命令参数未传递给存储过程或存储过程“忽略”参数 [英] ADO Command Parameter Not Passing to Stored Procedure or Stored Procedure 'Ignoring' Parameter

问题描述

更新4

更新了整个问题以反映我的更改。仍然无法正常工作。

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

存储过程

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

 创建过程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 
和p.PalletCode类似@SearchQuery 
由p.PalletCode ASC 
 
设置NoCount Off 
 GO 
  

在带有或不带有搜索项的SQL查询分析器中，这似乎都可以正常工作：

exec sp_PalletSearch 100，''和 exec sp_PalletSearch 100，'PalletCode'

ASP网页

因此进入网页本身...这是ADO命令我用来获取记录集，而这就是我的问题所在。只是根本不会返回任何内容：

  Dim strSearchQuery 
 strSearchQuery = PalletCode 
 
 Dim objCmd 
设置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 
设置objRS = objCmd.Execute 
设置objCmd =不执行
 
 
不做objRS.EOF 
 Response.Write（objRS（ PalletID）。Name& ：& objRS（ PalletID）。Value& |& objRS（ PalletCode）。Name&：& objRS（ PalletCode）。Value&< br> ）
 objRS.Mo veNext 
循环
 
 objRS.Close 
设置objRS = Nothing 
  

---

我尝试过...

如果我在ADO命令中编辑此行：

  objCmd.CommandText = sp_PalletSearch 
  

并将其更改为：

  objCmd .CommandText = {call sp_PalletSearch（?,'& strSearchQuery& '）} 
  

并删除：

< pre class = lang-vb prettyprint-override> objCmd.CommandType = adCmdStoredProc


所有搜索正常。
如果找不到 real 解决方案，我将坚持这样做。

---

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

 -SET @SearchQuery ='％'+ COALESCE（@SearchQuery，'％'）+'％'
  

然后我将获得完全匹配的内容。这将告诉我ADO Command正在传递参数ok。 但是但是为什么存储过程不能得到结果 Like @SearchQuery ？

---

要注意的另一件事是，用托盘代码 Like 将以下命令替换为ADO命令即可。我认为此代码段不是一个安全的选择，如果我错了，请告诉我。我宁愿使用参数化命令：

  strSQL = EXECUTE sp_PalletSearch&客户编号，’和strSearchQuery& ' 
设置objRS = Server.CreateObject（ ADODB.Recordset）
设置objConn = Server.CreateObject（ ADODB.Connection）
 objConn.Open cConn 
 objRS 。打开strSQL，objConn 
  

---

这是一个很大的问题，但是我喜欢高效，正确地做事，并且喜欢学习。
我希望你们能帮助我解决这个难题。

解决方案

已解决

感谢邦德，尤其是Lankymart的帮助。 Lankymart，您对使用SQL Profiler的建议有所帮助。我的服务器具有较旧的版本-Profiler。

我在Profiler跟踪中查找时发现了这一点： @SearchQuery ='bww100052'

所以我决定在存储过程中强制执行Trim： LTRIM（RTRIM（@SearchQuery））

存储过程

 创建过程dbo.sp_PalletSearch 
 @CustomerRef Int，
 @SearchQuery VarChar（15）='％'
 AS 
 
 SET NoCount On 
 SET @ SearchQuery ='％'+ COALESCE（LTRIM（RTRIM（@SearchQuery）），'％'）+'％'
 
选择p.PalletID，
 p.PalletCode 
 FROM dbo.v_PalletSearch p 
 WHERE p.CustomerRef = @CustomerRef 
 AND p.PalletCode LIKE @SearchQuery 
按p.PalletCode ASC 
 
 SET NoCount Off 
 GO 
  

ADO命令

  Dim ob jCmd 
设置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 
设置objCmd =无
  

最后

我以为我永远都不会解决这个问题，那简直是毫无意义！
我将对其进行更多测试，但是看起来好像需要修剪变量。我不知道为什么要增加额外的空间。

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.

解决方案

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.

这篇关于ADO命令参数未传递给存储过程或存储过程“忽略”参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！