Data Access Application Block - How to get @RETURN_VALUE From Stored Proc????

[jack tonk]

Hey everyone. I've been trying to use the Data Access
Application Block. 'Tis pretty cool. One problem I've
found....Check this code:

Dim params() As SqlParameter
params = SqlHelperParameterCache.GetSpParameterSet
(ConnectionString, ProcName, False)

params(0).Value = "test"
params(1).Value = "test2"

Dim ds As DataSet = SqlHelper.ExecuteDataset
(ConnectionString, ProcName, params)

The above code works fine. It returns ds after feeding
the proc two parameters.

Now change the IncludeReturnValueParameter value to True.

Now the first part of the code becomes this:
Dim params() As SqlParameter
params = SqlHelperParameterCache.GetSpParameterSet
(ConnectionString, ProcName, True)

How do you get the @RETURN_VALUE parameter back from the
proc?? I've tried several approaches with no luck.

Has anyone been able to get return values from stored
procedures using the Data Access Application Block?

Thanks!

 

[Hussein Abuthuraya[MSFT]]

Jack,

If you selected the arguments array to include a return value, then the parameter name would be "@Return_Value" and placed in position 0 of the array. So to get it, do:

MessageBox.Show(params(0))

I hope this helps!

Thanks,
Hussein Abuthuraya
Microsoft Developer Support

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

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 

[Jack Tonk]

Thanks.
I don't think this works, though.
It causes a "Parameter count does not match Parameter
Value count" error in the application block.

Setting the IncludeReturnValueParameter flag to True
causes it to expect three params: One return and two
inputs. Of course, I can't pass a value with the 0 param
since it is the return value. I can only get the value
for params(0) after execution.

Dim params() As SqlParameter
params = SqlHelperParameterCache.GetSpParameterSet
(ConnectionString, ProcName, True)

params(0).Direction = ParameterDirection.ReturnValue
params(1).Value = "test1" 'first input
params(2).Value = "test2" 'second input

Dim ds As DataSet = SqlHelper.ExecuteDataset
(ConnectionString, ProcName, params)

response.write params(0).Value 'return_value

-----Original Message-----
Jack,

If you selected the arguments array to include a return

value, then the parameter name would be "@Return_Value"
and placed in position 0 of the array. So to get it, do:

MessageBox.Show(params(0))

I hope this helps!

Thanks,
Hussein Abuthuraya
Microsoft Developer Support

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

Are you secure? For information about the Microsoft

Strategic Technology Protection Program and to order your
FREE Security Tool Kit, please visit

 

[Hussein Abuthuraya[MSFT]]

What happens if you loop through and output all the values in the array? Do you see the Return value anywhere in the array?


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

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

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 

[Jack Tonk]

Yes indeed.

This code returns "@Return_Value" followed by the stored procedure's input
parameters



Dim params() As SqlParameter

params = SqlHelperParameterCache.GetSpParameterSet

(ConnectionString, ProcName, True)

Dim intPosition, i As Integer

For i = 0 To params.GetUpperBound(0)

Response.Write(params(i))

Next

What happens if you loop through and output all the values in the array?

Do you see the Return value anywhere in the array?

Thanks,
Hussein Abuthuraya
Microsoft Developer Support

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

Are you secure? For information about the Microsoft Strategic Technology

Protection Program and to order your FREE Security Tool Kit, please visit

 

[Jack Tonk]

The return value is in the array but I still can't retrieve it using the
data access app block. Is this a bug with the block?

What happens if you loop through and output all the values in the array?

Do you see the Return value anywhere in the array?

Thanks,
Hussein Abuthuraya
Microsoft Developer Support

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

Are you secure? For information about the Microsoft Strategic Technology

Protection Program and to order your FREE Security Tool Kit, please visit

 

[Jack Tonk]

Here is how to do it:
Dim params(3) As SqlParameter '@RETURN_VALUE counts as 1 array
value

params = SqlHelperParameterCache.GetSpParameterSet(CONN_STRING,
"Stored_Proc", True) 'get the params from the stored proc

params(0).Direction = ParameterDirection.ReturnValue 'return value

params(1).Value = "FirstParam"

params(2).Value = "second param"

Dim ds As DataSet = SqlHelper.ExecuteDataset(CONN_STRING,
CommandType.StoredProcedure, "Stored_Proc", params)

Response.Write(params(0).Value) 'Now you have the return value!