Output params from stored procs using MS Data Access Application Block

19 Comments

• 
  
  
  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.
  

• 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.
  

• I'm getting the same problem. Object must implement IConvertible.
  

• Did anybody have solution for this ? Please provide the link where I can find solution for this?
  

• 
  
  Passing SqlParameters instead of objects as the params to the Data Access block will solve this.
  

• 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)
  
  
  

• 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.
  

• 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();
  
  

• 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.
  

• Yes that solved my problem
  

• 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
  
  

• 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
  
  

• 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
  
  

• 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.
  

• 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?
  

• 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.
  

• "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?
  

• 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.)
  

• yes, just adding command type solved my problem.