> Hi all,
> I am working on porting an application from VB6 to VB.NET 2003 and am
> running into some problems. When declaring and populating the parameters for
> a SQL Stored Procedure by using the SQLParameter() collection and trying to
> reference a particular parameter by name rather than index I get a Type
> Conversion error. But when declaring a SqlClient.SqlCommand object and then
> adding the parameters to the command object parameters collection I have no
> problem referencing a parameter by name. Why should there be a difference?
> To illustrate here is some simple code examples.
>
> 1. Using the SQLParameter() collection...
>
>             Dim parameters As SqlParameter() = { _
>              New SqlParameter("@DocRef", SqlDbType.VarChar, 30), _
>              New SqlParameter("@EdiDocID", SqlDbType.Int, 4), _
>              New SqlParameter("@myDocID", SqlDbType.Int, 4) _
>              }
>
>             parameters("@DocRef").Value = DocRef
>                 'parameters(0).Value = DocRef   '(commented out for testing)
>             parameters(1).Value = EdiDocID
>             parameters(2).Value = myDocIdent
>
> That example produces a type conversion error when I try to substitute the
> parameter name string "@DocRef" for the index integer. However:
>
> 2. Adding parameters to the Command object...
>
>         Dim cmd As SqlClient.SqlCommand
>         cmd = New SqlClient.SqlCommand
>         cmd.Parameters.Add("@DocRef", SqlDbType.VarChar, 30)
>         cmd.Parameters.Add("@EdiDocID", SqlDbType.Int, 4)
>         cmd.Parameters.Add("@myDocID", SqlDbType.Int, 4)
>
>         cmd.Parameters("@DocRef").Value = DocRef
>         cmd.Parameters("@EdiDocID").Value = EdiDocID
>         cmd.Parameters("@myDocID").Value = myDocIdent
>
> This example works just fine. I don't understand why there should be a
> difference.
>
> Here is the resulting problem.
> When I am passing 150 parameters to populate an Orders table, having to
> reference the parameters by numerical index can be cumbersome and very error
> prone, especially when fields are added or removed from the table. I am
> stuck with using the first method for reasons that I won't go into at this
> time. Is there any way to use the parameter name to derive the index value
> so I can don't have to renumber the list of parameter value assignments when
> a parameter in the called SQL Procedure is removed or added somewhere in the
> middle of the list?
>
> Thanks for your input.....