Referencing parameters collection member by Name bombs?

[John Kotuby]

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.....

[Charlie Brown]

In your first example you should be using

Dim parameters as SQLParameterCollection
John Kotuby wrote:

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.....