Cannot refer to ADO SQLParameters by name

[John Kotuby]

Hi All,
I am trying to move my experience with VB6 and ADO over to VB.NET. I am
having difficulty with assigning values to SQL parameters. In classic ADO a
value could be assigned to a parameter by referring to it with the index as
the parameter name in quotes. Here is some code to illustrate the problem.

Dim parameters As SqlParameter() = { _
New SqlParameter("@DocRef", SqlDbType.VarChar, 30), _
New SqlParameter("@EdiDocID", SqlDbType.Int, 4) _
}
parameters(0).Value = DocRef
parameters(1).Value = EdiDocID

That code works fine. But when I try:

parameters("@DocRef").Value = DocRef
parameters("@EdiDocID" ).Value = EdiDocID

I get a type conversion error. This doesn't present much of a problem with
only 2 parameters, but I will be working with a stored procedure that will
take up to 200 parameters. That will become a maintenance nightmare if just
1 parameter is added or removed in the list. It will throw the whole numeric
index sequence off. Can somebody please suggest how I might circumvent this
problem?
Thank you all in advance.

 

[Shawn]

Try this...

Dim cmd As New SqlClient.SqlCommand()

cmd.Parameters.Add("@DocRef", SqlDbType.VarChar, 30).Value = DocRef
cmd.Parameters.Add("@EdiDocID", SqlDbType.VarChar, 30).Value = EdiDocID

 

[John Kotuby]

Thanks Shawn...

I will try that syntax. Looks like it will be easier to manage.