Passing Parameter Collections

27 Mar 2006 5:05 PM

Vayse

Two questions

1) Can a Parameter Collection be created without a Command?
I've tried
Dim paramsCustomer As OleDbParameterCollection
paramsCustomer.Add(New OleDbParameter("CustomerBalance", OleDbType.Single,
8, "CustomerBalance"))
paramsCustomer.Add(New OleDbParameter("CustomerId", OleDbType.SmallInt, 8,
"CustomerId"))

But it won't work. I get "Object reference not set to an instance of an
object."
So for now, I have to add paramsCustomer = cmdTest.Parameters after the Dim
statement.
Is there any way around this?

2) Passing the Parameters Collection
So I have a function like this:

Function UpdateTable(ByVal stCommandText As String, ByVal Params As
OleDbParameterCollection, ByVal dtOrig As DataTable) As Long
Dim cmdUpdate As New OleDbCommand(stCommandText, connData)

How do I set cmdUpdate parameters to the Params that I pass? Can't get it to
work.

Thanks
Vayse

28 Mar 2006 1:49 AM

Kevin Yu [MSFT]

Hi Vayse,

You're getting an "Object reference not set to an instance of an object."
because the paramsCustomer is only a reference which is not pointing to any
objects. Generally, when we're trying to create a new object reference
pointing to some valid objects, we use Dim a as New ClassA. This requires
the ClassA has a constructor. However, in this case, the class
OleDbParameter does not have a constructor, so we cannot create it without
the a Command.

If you already have a command object and need to assign a parameter
collection to it, just use oledbCommand.Parameters = Params

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

28 Mar 2006 9:27 AM

Vayse

"Kevin Yu [MSFT]" <v-k***@online.microsoft.com> wrote in message
news:0oKWlngUGHA.4712@TK2MSFTNGXA01.phx.gbl...

> If you already have a command object and need to assign a parameter
> collection to it, just use oledbCommand.Parameters = Params
>
Hi Kevin

That wouldn't work for me. Here's what I have
Function UpdateTable(ByVal stCommandText As String, ByVal Params As
OleDbParameterCollection, ByVal dtOrig As DataTable) As Long

Dim cmdUpdate As New OleDbCommand(stCommandText, connData)
cmdUpdate.Parameters = Params

This won't compile, as Parameters is a read only property. Is there a
different way I could do this?
Thanks
Diarmuid

29 Mar 2006 6:46 AM

Kevin Yu [MSFT]

Sorry, that I made a mistake here. Yes, the Parameters property is
read-only. So I think there will be no way of achieving this. I suggest you
try to pass the whole SqlCommand reference instead of only the parameters
collection.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

28 Mar 2006 2:31 AM

Otis Mukinfus

Show quote

On Mon, 27 Mar 2006 18:05:44 +0100, "Vayse" <vayse@nospam.nospam> wrote:

>Two questions
>
>1) Can a Parameter Collection be created without a Command?
>I've tried
>Dim paramsCustomer As OleDbParameterCollection
>paramsCustomer.Add(New OleDbParameter("CustomerBalance", OleDbType.Single,
>8, "CustomerBalance"))
>paramsCustomer.Add(New OleDbParameter("CustomerId", OleDbType.SmallInt, 8,
>"CustomerId"))
>
>But it won't work. I get "Object reference not set to an instance of an
>object."
>So for now, I have to add paramsCustomer = cmdTest.Parameters after the Dim
>statement.
>Is there any way around this?
>
>2) Passing the Parameters Collection
>So I have a function like this:
>
>Function UpdateTable(ByVal stCommandText As String, ByVal Params As
>OleDbParameterCollection, ByVal dtOrig As DataTable) As Long
>Dim cmdUpdate As New OleDbCommand(stCommandText, connData)
>
>How do I set cmdUpdate parameters to the Params that I pass? Can't get it to
>work.
>
>Thanks
>Vayse
>
>
>

I've just finished doing this in a project.

1. Create your Command object.
2. Add the parameters to it's Parameters collection.

Here's some pseudo code:

// declare method
public OleDbParametersCollection FillParameters(OleDbParameterCollection params)
{
    //loop through the parameters
    for( int i = 0; i < params.Count; i++)
    {
        params[i].Value = The value;
        // in my case I was setting the parameters using data from an SQLDataReader
        // params[i].Value = dr.GetString(i);
    }
    return params;
}

use it like this:

cmd.Parameters = FillParameters(cmd.Parameters);

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

28 Mar 2006 9:33 AM

Vayse

>
> cmd.Parameters = FillParameters(cmd.Parameters);
>
> Otis Mukinfus

Thanks. In my case, I'm tyring to write a class to take care of all the
updating. See my other post about the idead - ADO Update Code Reuse.
So I'd like to pass the parameters to a function. But it looks like I can't
have a Parameters collection without a command, so I might change it so that
I pass a command instead.
Vayse

28 Mar 2006 1:01 PM

Otis Mukinfus

Show quote

On Tue, 28 Mar 2006 10:33:57 +0100, "Vayse" <vayse@nospam.nospam> wrote:

>>
>> cmd.Parameters = FillParameters(cmd.Parameters);
>>
>> Otis Mukinfus
>
>
>Thanks. In my case, I'm tyring to write a class to take care of all the
>updating. See my other post about the idead - ADO Update Code Reuse.
>So I'd like to pass the parameters to a function. But it looks like I can't
>have a Parameters collection without a command, so I might change it so that
>I pass a command instead.
>Vayse
>

Yes, I believe that will work too.

Good luck with your project.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com