Parameter Binding to Prepared statements RRS feed

  • Question

  • I am trying to prepare a DB access layer (using OLEDB in .NET 2.0) for my application. One of the functions in the layer accepts a string with ? placeholders, and an array of objects, & attempts to construct a OleDBCommand with parameters properly bound to it.
    Now what I have seen is, that a simple loop like follows:
    Code Snippet

                For Each val As Object In values
                    'Create a parameter for the current val.
                    Dim param As New OleDbParameter
                    param.Value = val

                    'param.Direction = ParameterDirection.Input
                    'param.Size =

                    'Add it to the parameter collection.
                    param = command.Parameters.Add(param)

    builds the command object, that is executed successfully against the database. Setting the value automatically sets the OleDbType for the parameter correctly.
    But, I was not expecting it to be that simple. I have a feeling I am missing something, which might cause a problem later.
    Specifically, one item is the Size property. I read in the MSDN following lines:

    "Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met."

    In my case, I used Strings in the values array, that are variable length. But still, the command executed successfully.
    So, do I need to do something to satisfy the above statement.

    The other confusion is with regard to the Parameter Collection's Add method.
    VS2005's intellisense shows Add to be an overloaded method (4 overloads) that return an OleDbParameter type.
    But pressing F1 on Add, and going to the MSDN shows 6 overloads of Add that return the index of the parameter added to the collection. And all of them (4+7) work too!!!
    Why this discrepency between the two???
    Saturday, August 11, 2007 8:05 AM

All replies

  • Moreover, the ParameterCollection itself is causing confusion. I tried adding more parameters than there are placeholders. No exception was raised, simply extra params were discarded.

    One thing I forgot to mention above is that I am allowing OleDb to give parameters default names. This is because this function only handles direct Sql commands, so matching them to Stored procedures for e.g. will never happen, & thus will not cause a problem.
    Saturday, August 11, 2007 8:09 AM
  • Anyone, with answers???
    Thursday, August 23, 2007 5:16 PM