SQL Server存储过程VB.NET调用参数异常：过多/缺失参数求助

错误现象 #

• 初始错误：System.Runtime.InteropServices.COMException: 'Procedure or function pro_SearchBySCQTY has too many arguments specified.'
• 更新后错误：System.Runtime.InteropServices.COMException: 'Procedure or function 'pro_SearchInvBySCwithQTY' expects parameter '@Qty', which was not supplied.'

当前代码 #

VB.NET 代码 #

If rsfind.State = ConnectionState.Open Then
    rsfind.Close()
End If
rsfind.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rsfind.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rsfind.LockType = ADODB.LockTypeEnum.adLockOptimistic
Dim cmd As New ADODB.Command
With cmd
    .ActiveConnection = [redacted]
    .CommandText = "pro_SearchInvBySCwithQTY"
    .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

    For i = cmd.Parameters.Count - 1 To 0 Step -1
        cmd.Parameters.Delete(i)
    Next i

    .Parameters.Append(.CreateParameter("@SearchTerm", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 50, Trim(ListView1.SelectedItems.Item(0).SubItems(0).Text)))
    .Parameters.Append(.CreateParameter("@QTY", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, ListView1.SelectedItems.Item(0).SubItems(3).Text))
    rsfind.Open(.Execute)
End With

SQL Server 存储过程代码 #

ALTER PROCEDURE [dbo].[pro_SearchInvBySCwithQTY] @SearchTerm NVARCHAR(MAX), @QTY INT
AS
BEGIN
    SELECT StockCode, Description, Supplier, AlternateKey1, AlternateKey2, Classification, QtyOnHand, DateLastStockMove, SafetyStockQty, ReOrderQty, SellingPrice, StockOnHold, StockUom, DefaultBin
    FROM vw_InventorySearchBySC
    WHERE StockCode = @SearchTerm AND QtyOnHand = @QTY
    order by DateLastStockMove DESC; 
END;

问题原因及修复方案 #

1. 字符串参数类型不匹配 #

存储过程中@SearchTerm定义为NVARCHAR(MAX)，但VB.NET中用了adVarChar（对应SQL的VARCHAR），类型不匹配会导致参数识别异常。需改为adVarWChar（对应NVARCHAR），同时长度设为-1以匹配MAX：

.Parameters.Append(.CreateParameter("@SearchTerm", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, -1, Trim(ListView1.SelectedItems.Item(0).SubItems(0).Text)))

2. 整数参数的长度参数错误+类型转换缺失 #

• CreateParameter的第四个参数是数据长度，对于整数类型无需指定，应设为0；
• ListView子项的文本是字符串类型，需显式转换为整数，否则会因类型不匹配导致参数未被正确识别：

.Parameters.Append(.CreateParameter("@QTY", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 0, CInt(ListView1.SelectedItems.Item(0).SubItems(3).Text)))

3. 简化参数清理逻辑 #

原代码循环删除参数的操作冗余，直接新建ADODB.Command对象即可，新对象的参数集合默认是空的，无需手动清理：

' 直接新建Command，无需删除旧参数
Dim cmd As New ADODB.Command
With cmd
    .ActiveConnection = [redacted]
    .CommandText = "pro_SearchInvBySCwithQTY"
    .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
    ' 直接添加新参数即可
    .Parameters.Append(.CreateParameter("@SearchTerm", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, -1, Trim(ListView1.SelectedItems.Item(0).SubItems(0).Text)))
    .Parameters.Append(.CreateParameter("@QTY", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 0, CInt(ListView1.SelectedItems.Item(0).SubItems(3).Text)))
    rsfind.Open(.Execute)
End With

完整修正后的VB.NET代码 #

If rsfind.State = ConnectionState.Open Then
    rsfind.Close()
End If
rsfind.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rsfind.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rsfind.LockType = ADODB.LockTypeEnum.adLockOptimistic
Dim cmd As New ADODB.Command
With cmd
    .ActiveConnection = [redacted]
    .CommandText = "pro_SearchInvBySCwithQTY"
    .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

    .Parameters.Append(.CreateParameter("@SearchTerm", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, -1, Trim(ListView1.SelectedItems.Item(0).SubItems(0).Text)))
    .Parameters.Append(.CreateParameter("@QTY", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 0, CInt(ListView1.SelectedItems.Item(0).SubItems(3).Text)))
    rsfind.Open(.Execute)
End With

内容的提问来源于stack exchange，提问作者huck