Stored Proc output param not getting returned to C# code

Topics: Data Access Application Block
Nov 9, 2012 at 7:17 PM
Edited Nov 9, 2012 at 7:19 PM

I am using entlib 4.1 in my C# application. This code has been working just fine for days now. I have used this same method in lots of projects without any issues. All of a sudden today this same method refuses to return the output parameter value back?? I keep getting "cannot cast from type DBNull" on return line. 

Can someone please tell me what I am doing wrong or not?


  public Int32 RequestHeaderSave(Int32 intReqID, string strRequester, string strCrtBy, string strTitle, string strType, Int32 intORG,
                                                 string strScope, string strScopeDtl, string strBizRsn, Int32 intSPN, Int32 intSPRN, Int32 intLRN, Int32 intRRN, Int32 intSTS)
                    OrmDB = DatabaseFactory.CreateDatabase("OrmDB");
                    sqlCommand = "[dbo].[ORM_RequestHeader_InsertUpdate]";
                    DbCommand dbCmd = OrmDB.GetStoredProcCommand(sqlCommand);
                    if (intReqID != 0)
                        OrmDB.AddInParameter(dbCmd, "@RHID", DbType.Int32, intReqID);
                    OrmDB.AddInParameter(dbCmd, "@REQ", DbType.String, strRequester);
                    OrmDB.AddInParameter(dbCmd, "@CBY", DbType.String, strCrtBy);
                    OrmDB.AddInParameter(dbCmd, "@TTL", DbType.String, strTitle);
                    OrmDB.AddInParameter(dbCmd, "@TYP", DbType.String, strType);
                    OrmDB.AddInParameter(dbCmd, "@ORG", DbType.Int32, intORG);
                    OrmDB.AddInParameter(dbCmd, "@SCP", DbType.String, strScope);
                    OrmDB.AddInParameter(dbCmd, "@SCPI", DbType.AnsiString, strScopeDtl);
                    OrmDB.AddInParameter(dbCmd, "@BIZ", DbType.AnsiString, strBizRsn);
                    if (intSPN != 0)
                        OrmDB.AddInParameter(dbCmd, "@SPN", DbType.Int32, intSPN);
                    if (intSPRN != 0)
                        OrmDB.AddInParameter(dbCmd, "@SPRN", DbType.Int32, intSPRN);
                    if (intLRN != 0)
                        OrmDB.AddInParameter(dbCmd, "@LRN", DbType.Int32, intLRN);
                    if (intRRN != 0)
                        OrmDB.AddInParameter(dbCmd, "@RRN", DbType.Int32, intRRN);
                    OrmDB.AddInParameter(dbCmd, "@STS", DbType.Int32, intSTS);
                    OrmDB.AddOutParameter(dbCmd, "@NEWID", DbType.Int32, 6);
                    //Save Request
                    //Get request number back from SP
                    if (OrmDB.GetParameterValue(dbCmd, "@NEWID") != null)
                        ///the error occurs on this line
                        return Convert.ToInt32(OrmDB.GetParameterValue(dbCmd, "@NEWID"));
                        return -1;
                catch (Exception ex)
                    return -1;

I have tested this SP directly on SQL server via mgmt studio and it works and returns the "@NEWID" value just fine. As I said this all worked just fine up through yesterday.

Nov 9, 2012 at 7:36 PM

One additional note....the stored procedure is not performing the insert into the table when called from the method above. That is probably the reason its not returning the value.

However, when I debug the SP in SQL mgmt studio it works just fine, the insert occurs and the ID is returned.

Any ideas on what could be the issue?

Nov 9, 2012 at 8:20 PM

I think you've pretty much answered the question.  The stored procedure is not returning a value so the "value" is DBNull.  When you try to convert to an int you will get an InvalidCastException with the message "Object cannot be cast from DBNull to other types."

From a coding perspective, the solution is to either always return an int -- perhaps using -1 or 0 to indicate that the newID was not actually populated -- or to handle the DBNull in the code using something like:

return (OrmDB.GetParameterValue(dbCmd, "@NEWID") as int?).GetValueOrDefault(-1);

Generic extension methods can make this more elegant.

From a design perspective you should decide on how the stored procedure should behave when it does not complete successfully (assuming that the stored procedure is not behaving properly).  E.g. should a status code be returned or an error raised or null returned.  Pick an approach and use it consistently.

Randy Levy
Enterprise Library support engineer 

Nov 9, 2012 at 8:31 PM

Thanks for the reply and the tip!


I actually just figured out the issue. One of the columns in the table does not allow nulls and I just changed some logic in the web page that caused it send a null value to that column. That was the reason the SP did not perform the insert and thus not returning the ID.

I do have a follow up question. What would be the best method, given the code I have in my method, for capturing the SQL stored procedure catch error message. In my SP I have a try catch block which does a select of the error number and message. How do I grab that in my code?