Output params from stored procs using MS Data Access Application Block

As my current client is early in the architecture phase, I’ve been reviewing the MS Data Access Application Block. I did a brief experiment, and I’m having problems retrieving output parameters from stored procedures. I created a simple stored proc (with CodeSmith):



[Edit: I just debugged this with one of the MS Consulting Services Architects on this project. There is a bug in the Data Access App Block that causes this problem when you call methods that take an array of objects that hold your parameter values. Calling the overload that takes a SqlParameter array works fine.  He wasn't real suprised that there are problems with the DAAB.]

[Edit2: Bill Selznick's comment is right on.  Most of the people that mail me about this problem are calling the wrong overload of ExecuteNonQuery.  Step into the DAAB and make sure you are calling the overload that you intend to.]

 

CREATE PROCEDURE dbo.InsertValidationPolicy @Severity int, @Message nvarchar(50), @Required bit, @ValidationPolicyID uniqueidentifier OUTPUT AS SET @ValidationPolicyID = NEWID() INSERT INTO [ValidationPolicies] ( [ValidationPolicyID], [Severity], [Message], [Required] ) VALUES ( @ValidationPolicyID, @Severity, @Message, @Required ) GO

And created a simple data access object with static methods (doing the prototype before I build the CodeSmith template). The below method calls the stored procedure to create a new object. Notice the fourth parameter is an output parameter, and that the stored procedure is executed using SqlHelper (the main component in the Data Access App Block). 
 static public Guid CreateValidationPolicy(int severity, string message, bool required) { SqlParameter paramSeverity = new SqlParameter("Severity", SqlDbType.Int); paramSeverity.Value=severity; SqlParameter paramMessage = new SqlParameter("Message", SqlDbType.NVarChar); paramMessage.Value=message; SqlParameter paramRequired = new SqlParameter("Required", SqlDbType.Bit); paramRequired.Value=required; SqlParameter paramID = new SqlParameter("PolicyID",SqlDbType.UniqueIdentifier); paramID.Direction = ParameterDirection.Output; SqlParameter[] parameters = { paramSeverity, paramMessage, paramRequired, paramID }; SqlHelper.ExecuteNonQuery(ConnectionString, "InsertValidationPolicy", parameters); return (Guid)paramID.Value; }

This method throws an Exception complaining that, “Object must implement IConvertible”. It works fine if I don’t use output parameters. There are several threads discussing this problem in the newsgroup dedicated to the MS Building Blocks. The best discussion was started by Jim Bentley on 2/27/03 (sorry, but I don’t know how to link to news threads) and includes some responses from Microsoft, but no solutions.

Ted
Published 14 May 2003 02:45 PM by Ted_Graham
Filed under:

Comments

# Jon Galloway said on 14 May, 2003 07:08 PM
Ted -

You may want to use ExecuteScalar:

ContentID = (int)SqlHelper.ExecuteScalar(conn, "spSaveInfo",0,"blah");

I'm using an autoincrement int here instead of a GUID so I can't vouch for them. I avoid GUIDs in DB's unless replication is involved.

Here's my basic SP:

IF @ContentID = 0
BEGIN
INSERT INTO Content (Info)
VALUES (@Info);
-- Return the new id
SELECT CAST(scope_identity() as int)
END
ELSE
BEGIN
SET NOCOUNT OFF;
UPDATE Content
SET Info = @Info
WHERE ContentID = @ContentID

SELECT @ContentID
END
# Me said on 14 May, 2003 08:55 PM
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=microsoft.public.dotnet.distributed_apps
# Ted Graham said on 15 May, 2003 09:38 AM

As I said above, I believe the problem is calling methods that take the parameters as object[]s. The conversion of those arrays to SqlParameter[]s in the DAAB has problems with output parameters. Calling the SqlParameter[] methods seems to work fine.
# Greg Robinson said on 15 May, 2003 02:02 PM
I have had a lot of success using the DAAB with some tweaking. I pass in a structure array for my params so it works in a remote environment.
# Scott Cate said on 03 August, 2003 05:46 PM
I'm getting the same problem. Object must implement IConvertible.
# Sam said on 04 August, 2003 06:18 PM
Did anybody have solution for this ? Please provide the link where I can find solution for this?
# Ted Graham said on 04 August, 2003 06:26 PM

Passing SqlParameters instead of objects as the params to the Data Access block will solve this.
# Steven Smith said on 19 August, 2003 01:03 AM
I'm passing in an array of SqlParameter objects but still get the error:
[InvalidCastException: Object must implement IConvertible.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, String spName, Object[] parameterValues)
# TrackBack said on 19 August, 2003 01:23 AM
# Jim Little said on 21 November, 2003 07:49 PM
i received a similar error. i discovered that i was trying to force a base64binary into an int. once i changed the value of the sp to accept a 'timestamp' rather than an 'int' i was able to move passed the error.
# Greg Norris said on 18 December, 2003 05:45 PM
I had this problem and discovered that I had not used the [text] property of a textbox.

I was trying to pass the entire object (tbUserId) which is why this error ocurred.

The error lines:
=======================
cmd.Parameters.Add("@UserId", SqlDbType.VarChar, 128).Value = tbUserId;
cmd.Parameters.Add("@password", SqlDbType.VarChar, 32).Value = tbPassword;

my working code..
=======================
SqlCommand cmd = new SqlCommand("sp_Name");
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserId", SqlDbType.VarChar, 128).Value = tbUserId.Text;
cmd.Parameters.Add("@password", SqlDbType.VarChar, 32).Value = tbPassword.Text;
cmd.Parameters.Add("@UseIntegratedSecurity", SqlDbType.Bit, 1).Value = null;
cmd.Parameters.Add("@TicketTypeCode", SqlDbType.VarChar, 32).Value = null;

SessionTicketId = new SqlParameter("@SessionTicketId", SqlDbType.Int);
SessionTicketId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(SessionTicketId);

returnParam = new SqlParameter("@ReturnMsg", SqlDbType.VarChar, 250);
returnParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(returnParam);

conn.Open();
cmd.ExecuteNonQuery();
# Bill Selznick said on 12 January, 2004 03:30 PM
No there is not a bug in MDAAB, well not in this case. Ted, if you examine your example call to ExecuteNonQuery: SqlHelper.ExecuteNonQuery(ConnectionString, "InsertValidationPolicy", parameters);
You'll see that this call qualifies for the overload having the following calling sequence: ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues). Notice the last parameter which is typed as object, and is expected to be a sequence of parameter VALUES! This is the only error. Like you I also spent an hour passing PARAMETERS. Since like you I already supplied values to my parameters before making the call to ExecuteNonQuery, I changed my call so that I get the following overload: ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters). See this one expects an array of parameters and not values.
# Naresh said on 30 January, 2004 09:57 AM
Yes that solved my problem
# Taranath Chebrolu said on 11 March, 2004 09:31 AM
with this update of MS Data Access Application Block we can solve those problem
As per MS Data Access Application Block Code
it was
ExecuteNonQuery(SqlConnection,CommandType,commandText,commandParameters as sqlparameter)
retval = cmd.ExecuteNonQuery()

now we do modification as
Dim ints As Integer
For Each p As SqlParameter In cmd.Parameters
If p.Direction = ParameterDirection.InputOutput Then
ints = p.Value
End If
Next

then we can OUTPUT value from SP
Good Luck
Taranath
India
# Taranath Chebrolu said on 11 March, 2004 09:34 AM
with this update of MS Data Access Application Block we can solve those problem
As per MS Data Access Application Block Code
it was
ExecuteNonQuery(SqlConnection,CommandType,commandText,commandParameters as sqlparameter)
retval = cmd.ExecuteNonQuery()

now we do modification as
Dim ints As Integer
For Each p As SqlParameter In cmd.Parameters
If p.Direction = ParameterDirection.InputOutput Then
ints = p.Value
End If
Next

then we can OUTPUT value from SP
Good Luck
Taranath
India
# Taranath Chebrolu said on 11 March, 2004 09:37 AM
a bit modification
with this update code of MS Data Access Application Block we can solve our problem
As per MS Data Access Application Block Code
it was
ExecuteNonQuery(SqlConnection,CommandType,commandText,commandParameters as sqlparameter)
retval = cmd.ExecuteNonQuery()

now we do modification as
Dim ints As Integer
For Each p As SqlParameter In cmd.Parameters
If p.Direction = ParameterDirection.InputOutput Then
ints = p.Value
End If
Next
retval = ints

then we can OUTPUT value from SP
Good Luck
Taranath
India
# Paul Thomson said on 02 April, 2004 05:40 PM
Its seems the difference between success and failure with regards to using MSDAAB and OUTPUT parameters is using the CommandType arguement. If you do not include the CommandType.StoredProcedure, the command will complete successfully but will not return your output parameters to you. If you include this optional arguement in your method call, you should expect your output parameters back.
# David Hart said on 09 June, 2004 11:19 PM
I am using the ExecuteScalar. I am including the CommandType.StoredProcedure and passing in an array of sqlparameters. I set my last parameter to have a direction = ParameterDirection.Output. My command completes successfully, but my output from the command is always <undefined value>. I never get anything back. Any thoughts?
# tk said on 10 July, 2004 01:46 PM
If you use a parameter array to return output values, be aware that you must extract the values (using the SqlParameter Value property) after you close the SqlDataReader object.
# Mauricio Feijo said on 02 August, 2004 02:39 PM
"I am using the ExecuteScalar. I am including the CommandType.StoredProcedure and passing in an array of sqlparameters. I set my last parameter to have a direction = ParameterDirection.Output. My command completes successfully, but my output from the command is always <undefined value>. I never get anything back. Any thoughts? "

Me too. And I close the SQLDatareader.
And get Nothing on the output parameter.

Anyone have a solution?
# Bob Sargent said on 06 August, 2004 01:00 PM
Many thanks to Bill Selznick for solving this problem for me before I had to spend too many more hours trying to figure it out. (I used the wrong overload too.)
# Work from home moms. said on 12 June, 2008 04:13 PM

Work from home moms.

# ламинат said on 25 August, 2008 02:25 AM

4fGood idea.8u I compleatly agree with last post.  nyo

<a href="http://skuper.ru">ламинированный паркет</a> 9h

Leave a Comment

(required) 
(required) 
(optional)
(required)