Passing Parameter Collections

[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

 

[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."

 

[Otis Mukinfus]

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.Value = The value;
// in my case I was setting the parameters using data from an SQLDataReader
// params.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

 

[Vayse]

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

 

[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

 

[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

 

[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."