CODEWITHSUNDEEP
sql-servervb.netado.net
Paulina Alvarado
Paulina Alvarado

Reputation: 11

The procedure has too many arguments specified

I have this code into a function:

    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

Basically, what I am trying to do is read directly to the database what parameters the stored procedure has and depending on the amount I have (input) I am creating them in this for cycle. So far so good, when I have to fill the DataTable with the result of the rows from the database I get the message: "The procedure has too many arguments specified". But if I debug the code, it assigns me the sql values and parameters correctly. If they are 3, 3 is created, if it is 1, 1 is created and so on.

Upvotes: 0

Views: 1642

Answers (2)

Paulina Alvarado
Paulina Alvarado

Reputation: 11

I'm fix it:

        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

Just comment on the "Add" line and it works. This is because I was trying to add parameters and just enough to pass the value of the encapsulated methods to the parameter of type SQLParameter. Thanks for the answers and the help provided.

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

You seem to get the SqlCommandBuilder to download all the parameter information from the DB and add it to an SqlCommand witha populated parameter collection, but then you skip through it looking for input parameters, load them into a list and enumerate it adding even more parameters to the same command:

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

Surely you should just enumerate the existing parameters and supply a value for them based on their name (the type will/ should already be correct? Parametros is a Dictionary(Of string, object) that has parameter names without @ and values ?)

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

Throw all that List inputParam/second ForEach away

Upvotes: 0

Related Questions