output parameter syntax

[ChrisB]

Hello:

I am trying to use an output parameter with the data access application
block and seem to be running into some difficulties. Specifically, I am
receiving the error message: "Object reference not set to a reference of an
object" when I attempt to access the parameter value.

Component code (setup of parameters):
SqlParameter[] consumerParameters = new SqlParameter[2];

consumerParameters[0] = new SqlParameter("@ConsumerID", 1);

consumerParameters[1] = new SqlParameter("@Checksum", 0);

consumerParameters[1].Direction = ParameterDirection.Output;

SafeDataReader safeDataReader = new
SafeDataReader(SqlHelper.ExecuteReader(sqlTransaction,
CommandType.StoredProcedure, "ConsumerGet", consumerParameters));

. . .

_checksum = int.Parse(consumerParameters[1].Value.ToString()); // receive
error message here



Stored Procedure code:

CREATE PROCEDURE ConsumerGet
(@ConsumerID [uniqueidentifier],
@Checksum [bigint] Output)

etc.



Thanks for any input.

Chris

 

[William Ryan]

Chris:

I had the exact same problem except it wasn't an output parameter. In your
declaration of the parameter where you are using 0, use the value style
instead. Each time I used the syntax you used, my Parameter evaluted to
Nothing.

But changing it to this...
cmd.Parameters.Add("@ConsumerID");

cmd.Parameters["@ConsumerID"].Value = 0; fixed the problem.



It looks like when you use 0, it's thinking it's a valid enumarable value
for the overload (3 of 6) and it thinks you are setting the DbType. So you
think you are setting the value to 0, but it thinks you are only setting the
type and never assigning a value to it.



HTH,



Bill

 

[William \(Bill\) Vaughn]

The Parameters collection must be populated just like any other collection.
You need to use the Add method for each parameter.
There are a variety of ways to do this, but I find the easiest is to use the
wealth of overloads on the Add method itself. In one of my classes, I have a
list of examples that illustrate the use of the Add method. In the first
section, I create each

Dim myParm1 As New SqlParameter()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim cmd As New SqlCommand()
With cmd.Parameters
.Add(myParm1) ' This parameter still
needs to have its properties set.
.Add("@p2", "Value")
' NVarChar value set to string "Value"
.Add("@p3", 123)
' Integer Value property set to 123
.Add("@p4", txtTextBox.Text) '
NVarChar Value property set from TextBox
.Add("@p5", CInt(intTextBox1.Text.ToString)) 'Value in
TextBox must be integer
.Add("@p6", SqlDbType.Int) '
Integer, no value set
.Add("@p7", SqlDbType.Int).Value = 22 ' Integer
value 22 set
.Add("@p8", SqlDbType.VarChar, 20) ' VarChar
length 20, no value set
.Add("@p9", SqlDbType.VarChar, 20).Value = "twenty two" '
VarChar(20), value set
.Add("@pa", SqlDbType.VarChar, 50, "City").Direction =
ParameterDirection.Output
End With
End Sub
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[ChrisB]

Thanks for the info, Ryan.

In addition to the issue you raised, I also discovered something else that
is interesting.

Apparently, it is not possible to access the value of an output parameter
until the related datareader is closed:

So my code ended up looking like this:

safeDataReader.Close();
_checksum = long.Parse(consumerParameters[1].Value.ToString());

Not sure if this requirement is only necessary if using the DAAB or if it
applies to output parameters in general, but I thought I would pass this
along.

Thanks again,

Chris

Chris:

I had the exact same problem except it wasn't an output parameter. In your
declaration of the parameter where you are using 0, use the value style
instead. Each time I used the syntax you used, my Parameter evaluted to
Nothing.

But changing it to this...
cmd.Parameters.Add("@ConsumerID");

cmd.Parameters["@ConsumerID"].Value = 0; fixed the problem.



It looks like when you use 0, it's thinking it's a valid enumarable value
for the overload (3 of 6) and it thinks you are setting the DbType. So you
think you are setting the value to 0, but it thinks you are only setting the
type and never assigning a value to it.



HTH,



Bill

Hello:

I am trying to use an output parameter with the data access application
block and seem to be running into some difficulties. Specifically, I am
receiving the error message: "Object reference not set to a reference of an
object" when I attempt to access the parameter value.

Component code (setup of parameters):
SqlParameter[] consumerParameters = new SqlParameter[2];

consumerParameters[0] = new SqlParameter("@ConsumerID", 1);

consumerParameters[1] = new SqlParameter("@Checksum", 0);

consumerParameters[1].Direction = ParameterDirection.Output;

SafeDataReader safeDataReader = new
SafeDataReader(SqlHelper.ExecuteReader(sqlTransaction,
CommandType.StoredProcedure, "ConsumerGet", consumerParameters));

. . .

_checksum = int.Parse(consumerParameters[1].Value.ToString()); // receive
error message here



Stored Procedure code:

CREATE PROCEDURE ConsumerGet
(@ConsumerID [uniqueidentifier],
@Checksum [bigint] Output)

etc.



Thanks for any input.

Chris

 

[William Ryan]

Yep, if you are using a DataReader, then you have to process all of the
results before you can get the value. That little problem with the zero
took me some time to figure out....live and learn right ;-).

Cheers,

Bill

Thanks for the info, Ryan.

In addition to the issue you raised, I also discovered something else that
is interesting.

Apparently, it is not possible to access the value of an output parameter
until the related datareader is closed:

So my code ended up looking like this:

safeDataReader.Close();
_checksum = long.Parse(consumerParameters[1].Value.ToString());

Not sure if this requirement is only necessary if using the DAAB or if it
applies to output parameters in general, but I thought I would pass this
along.

Thanks again,

Chris

Chris:

I had the exact same problem except it wasn't an output parameter. In your
declaration of the parameter where you are using 0, use the value style
instead. Each time I used the syntax you used, my Parameter evaluted to
Nothing.

But changing it to this...
cmd.Parameters.Add("@ConsumerID");

cmd.Parameters["@ConsumerID"].Value = 0; fixed the problem.



It looks like when you use 0, it's thinking it's a valid enumarable value
for the overload (3 of 6) and it thinks you are setting the DbType. So you
think you are setting the value to 0, but it thinks you are only setting the
type and never assigning a value to it.



HTH,



Bill

Hello:

I am trying to use an output parameter with the data access application
block and seem to be running into some difficulties. Specifically, I am
receiving the error message: "Object reference not set to a reference

of
an

object" when I attempt to access the parameter value.

Component code (setup of parameters):
SqlParameter[] consumerParameters = new SqlParameter[2];

consumerParameters[0] = new SqlParameter("@ConsumerID", 1);

consumerParameters[1] = new SqlParameter("@Checksum", 0);

consumerParameters[1].Direction = ParameterDirection.Output;

SafeDataReader safeDataReader = new
SafeDataReader(SqlHelper.ExecuteReader(sqlTransaction,
CommandType.StoredProcedure, "ConsumerGet", consumerParameters));

. . .

_checksum = int.Parse(consumerParameters[1].Value.ToString()); // receive
error message here



Stored Procedure code:

CREATE PROCEDURE ConsumerGet
(@ConsumerID [uniqueidentifier],
@Checksum [bigint] Output)

etc.



Thanks for any input.

Chris