该过程指定的参数太多

Question

我将此代码放入一个函数中：

    Adp.SelectCommand = New SqlCommand()
    Adp.SelectCommand.Connection = oConn
    Adp.SelectCommand.CommandText = sp
    Adp.SelectCommand.CommandType = CommandType.StoredProcedure
    SqlCommandBuilder.DeriveParameters(Adp.SelectCommand)
    table = New DataTable
    Dim resultado = 0

    Dim inputParamList As New List(Of SqlParameter)
    For Each param As SqlParameter In Adp.SelectCommand.Parameters
        If param.Direction = Data.ParameterDirection.Input OrElse _
           param.Direction = Data.ParameterDirection.InputOutput Then
            inputParamList.Add(param)
        End If
    Next

    For Each parame As SqlParameter In inputParamList
        Dim metodo() As String
        Dim paramName As String = parame.ParameterName.ToString()
        Dim paramValue As Object = DBNull.Value

        metodo = parame.ParameterName.ToString().Split("@")
        paramValue = Parametros(metodo(1))
        Adp.SelectCommand.Parameters.AddWithValue(paramName, paramValue)
    Next

    resultado = Adp.SelectCommand.ExecuteNonQuery()
    Adp.Fill(table)

Catch ex As Exception
    MessageBox.Show("Ocurrió una excepción en: " + ex.Message + "", "SystemA", _
                     MessageBoxButtons.OK)
    Adp.Dispose()
    table.Dispose()
End Try

Return table

基本上，我尝试做的是将存储过程具有的参数直接读取到数据库中，并根据我拥有的(输入)数量在这个for循环中创建它们。到目前为止，一切正常，当我必须用数据库中的行的结果填充DataTable时，我得到消息：“该过程指定了太多参数”。但是如果我调试代码，它会正确地为我分配SQL值和参数。如果是3，则创建3；如果是1，则创建1，依此类推。

Answer 1

我来解决它：

        Dim inputParamList As New List(Of SqlParameter)
        For Each param As SqlParameter In Adp.SelectCommand.Parameters
            If param.Direction = Data.ParameterDirection.Input OrElse param.Direction = Data.ParameterDirection.InputOutput Then
                inputParamList.Add(param)
            End If
        Next

        For Each parame As SqlParameter In inputParamList
            Dim metodo() As String
            Dim paramName As String
            Dim paramValue As Object

            metodo = parame.ParameterName.ToString().Split("@")
            paramName = parame.ParameterName
            paramValue = Parametros(metodo(1))

            Adp.SelectCommand.Parameters(parame.ParameterName).Value = paramValue
       ==>  'Adp.SelectCommand.Parameters.Add(parame.ParameterName, parame.SqlDbType)
        Next

        Adp.Fill(table)

    Catch ex As Exception
        MessageBox.Show("Ocurrió una excepción en: " + ex.Message + "", "Sistema de Agencias", MessageBoxButtons.OK)
        Adp.Dispose()
        table.Dispose()
    End Try

    Return table

只需在"Add“行上进行注释，它就可以正常工作。这是因为我试图添加参数，仅够将封装方法的值传递给SQLParameter类型的参数。感谢您提供的答案和帮助。

Answer 2

您似乎让SqlCommandBuilder从DB下载所有参数信息，并使用填充的参数集合将其添加到SqlCommand中，但随后您跳过了查找输入参数的过程，将它们加载到列表中并枚举它，向同一命令添加更多参数：

For Each parame As SqlParameter In inputParamList
    Dim metodo() As String
    Dim paramName As String = parame.ParameterName.ToString()
    Dim paramValue As Object = DBNull.Value

    metodo = parame.ParameterName.ToString().Split("@")
    paramValue = Parametros(metodo(1))

    'you're adding more parameters, with a slightly different but essentially same name!!?
    Adp.SelectCommand.Parameters.AddWithValue(paramName, paramValue)
Next

当然，您应该只枚举现有的参数，并根据它们的名称为它们提供一个值(类型将/应该已经正确？Parametros是一个(字符串、对象的)字典，它的参数名称不带@和值?)

For Each param As SqlParameter In Adp.SelectCommand.Parameters
    If param.Direction = Data.ParameterDirection.Input OrElse _
       param.Direction = Data.ParameterDirection.InputOutput Then
        param.Value = Parametros(param.ParameterName.TrimStart("@"c))
    End If
Next

丢弃所有inputParam/second ForEach列表