Referencing parameters collection member by Name bombs?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • John Kotuby

    #1

    Referencing parameters collection member by Name bombs?

    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.SqlCo mmand 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.VarCh ar, 30), _
    New SqlParameter("@ EdiDocID", SqlDbType.Int, 4), _
    New SqlParameter("@ myDocID", SqlDbType.Int, 4) _
    }

    parameters("@Do cRef").Value = DocRef
    'parameters(0). Value = DocRef '(commented out for testing)
    parameters(1).V alue = EdiDocID
    parameters(2).V alue = 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.SqlCo mmand
    cmd = New SqlClient.SqlCo mmand
    cmd.Parameters. Add("@DocRef", SqlDbType.VarCh ar, 30)
    cmd.Parameters. Add("@EdiDocID" , SqlDbType.Int, 4)
    cmd.Parameters. Add("@myDocID", SqlDbType.Int, 4)

    cmd.Parameters( "@DocRef").Valu e = DocRef
    cmd.Parameters( "@EdiDocID").Va lue = EdiDocID
    cmd.Parameters( "@myDocID").Val ue = 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

    #2
    Re: Referencing parameters collection member by Name bombs?

    In your first example you should be using

    Dim parameters as SQLParameterCol lection


    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.SqlCo mmand 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.VarCh ar, 30), _
    New SqlParameter("@ EdiDocID", SqlDbType.Int, 4), _
    New SqlParameter("@ myDocID", SqlDbType.Int, 4) _
    }
    >
    parameters("@Do cRef").Value = DocRef
    'parameters(0). Value = DocRef '(commented out for testing)
    parameters(1).V alue = EdiDocID
    parameters(2).V alue = 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.SqlCo mmand
    cmd = New SqlClient.SqlCo mmand
    cmd.Parameters. Add("@DocRef", SqlDbType.VarCh ar, 30)
    cmd.Parameters. Add("@EdiDocID" , SqlDbType.Int, 4)
    cmd.Parameters. Add("@myDocID", SqlDbType.Int, 4)
    >
    cmd.Parameters( "@DocRef").Valu e = DocRef
    cmd.Parameters( "@EdiDocID").Va lue = EdiDocID
    cmd.Parameters( "@myDocID").Val ue = 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.....

    Comment

    Working...