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" <jk*****@snet.net> wrote in message
news:Oa**************@TK2MSFTNGP09.phx.gbl...

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.

[John Kotuby]

Thanks Shawn...

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

"Shawn" <sh**********@ccci.org> wrote in message
news:un**************@TK2MSFTNGP12.phx.gbl...

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" <jk*****@snet.net> wrote in message
news:Oa**************@TK2MSFTNGP09.phx.gbl...

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.