adding sqlparameter without name

Question

I'm trying to create a sub to add as many parameters as I want, I used to do this in vb6 but here in vb.net it requires me to provide the parameter name (.add(@parameter, value)). I need to find a way to do it without knowing the parameter name, i used to send the parameters using the parameter order in the stored procedure, here the code:

Public Sub EjecutarSP(ByVal SP As String, ByVal ParamArray Parametros() As Object)
    Dim cnn As New SqlConnection(ConfigurationSettings.AppSettings("connString").ToString)
    Dim cmd As New SqlCommand(SP, cnn)
    Dim i As Integer
    Dim Param As SqlParameter
    Try
        For i = 0 To UBound(Parametros)
            Param = New SqlParameter("str", Parametros(i))
            cmd.Parameters.Add(Param)
        Next
        cmd.CommandTimeout = 0
        cmd.CommandType = CommandType.StoredProcedure
        If cmd.Connection.State <> ConnectionState.Open Then cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd = Nothing
    Catch ex As Exception
        Err.Raise(1000, "EjecutarSP", ex.Message)
    End Try
End Sub

thanks

score 0 · Answer 1 · answered Dec 01 '14 at 18:05

AFAIK the SqlClient doesn't support nameless parameters

From here:

Nameless, also called ordinal, parameters are not supported by the .NET Framework Data Provider for SQL Server.

However, instead of explicitly creating the parameters, you could fetch the parameters for the Proc with DeriveParameters, once you are connected.

Assuming you wish to blindly wish to trust the order of the parameters to match the parameters of the Proc, simply set the .Value of the parameters at the given index.

Dim cmd As New SqlCommand(SP, cnn)
...
SqlCommandBuilder.DeriveParameters(cmd)
For i = 0 To UBound(Parametros)
    cmd.Parameters(i).Value = Parametros(i)
Next

score 0 · Answer 2 · answered Aug 02 '16 at 08:09

Just reset the name to nothing (null) once the parameter has been added, something like this:

    For i = 0 To UBound(Parametros)
        Param = New SqlParameter("str", Parametros(i))
        cmd.Parameters.Add(Param)
        Param.ParameterName = Nothing
    Next

score 0 · Answer 3 · answered Nov 02 '19 at 06:59

You don't have to explicity name or type parameters. There are actually a couple of ways to execute stored procs without naming each parameter. Here is one way using Microsoft's old EnterpriseLibrary (which I still like in 2019).

NB: in VB.NET the size of the parameter array is one less than the number of parameters in the stored proc.

    Dim dt As System.Data.DataTable
    Dim arrParameters(6) As Object

    arrParameters(0) = "Hello"
    arrParameters(1) = "World"
    arrParameters(2) = "Lorem"
    arrParameters(3) = "Ipsum"
    arrParameters(4) = "Dolor"
    arrParameters(5) = "Blabla"
    arrParameters(6) = 12345

    Try
        Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase("TheNameOfYourConnectionStringInWebConfig")
        dt = db.ExecuteDataSet("TheNameOfYourStoredProcedure", arrParameters).Tables(0)
    Catch ex As Exception

    End Try

adding sqlparameter without name

3 Answers3