SMesa SMesa - 1 year ago 66 Question

VB.NET SqlParameter Object Will not accept value of it's value property when coding for a stored procedure[Resolved]

I am creating a function that is used to retrieve a value from a stored procedure.
The code of the stored procedure is as follows:

create procedure [dbo].[sp_get_drivers_violations_count]
@driver_id int,
@counter int output

Select @counter = Count(fk_violation_id) from link_violations_drivers Where fk_driver_id = @driver_id;
Return @counter;


The code that I use in my VB.Net program is as follows:

Public Function SpGetDriversViolationsCount2(Id As Int32) As Int32
Dim SqlCon As SqlConnection = New SqlConnection With {.ConnectionString = New ConnectionStrings.ConnectionStrings().MsSqlConString}

Dim SqlCmd As SqlCommand = New SqlCommand("sp_get_drivers_violations_count", SqlCon)

SqlCmd.CommandType = CommandType.StoredProcedure

Dim RetValue As SqlParameter = SqlCmd.Parameters.Add("RetValue", SqlDbType.Int, 11)

RetValue.Direction = ParameterDirection.ReturnValue

Dim SpParameterId As SqlParameter = SqlCmd.Parameters.Add("@driver_id", SqlDbType.Int, 11)

SpParameterId.Direction = ParameterDirection.Input

Dim counter As SqlParameter = SqlCmd.Parameters.Add("@counter", SqlDbType.Int, 11)

counter.Direction = ParameterDirection.Output

SpParameterId.Value = Id

Dim ReturnValue As UInt32


Dim DataReader As SqlDataReader = SqlCmd.ExecuteReader()

Do While DataReader.Read
ReturnValue = DataReader.GetInt32(0)


Return ReturnValue

End Function

The code was created using this How-To. While trying to debug the function using NUnit, I noticed that the "SpParameterId" object, never get it's value property assigned to a value, even if i explicitly assign a value to it. Thank you.


Mark McGinty solution worked like a charm. After closing the DataReader object, (and of course removing the loop" I was able to run my code correctly).

Answer Source

The parameters collection provides access to values returned in output and return value parameters after the call, e.g.,

object rv = SqlCmd.Parameters["RetValue"].Value;

Note that these values are (according to the tech ref) inaccessible until after the DataReader has been closed.

In practice I usually return scalar values from a stored by selecting them, and then call SqlCommand.ExecuteScalar(), just for ease, but that's just a personal preference.