By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Referencing parameters collection member by Name bombs?

P: n/a
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.....
Oct 6 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.....
Oct 6 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.