Decimal Parameter with DAAB

Topics: Data Access Application Block
Apr 23, 2007 at 9:40 PM

I would like to see if anyone knows how to call a SP which contains a parameter which is of type decimal (with precision and scale) with DAAB. The C# code below throws a SQL Exception “Error converting data type decimal to numeric.”. The following is some debugging information:

C# Code
cmd = database.GetStoredProcCommand(storedProcName);
database.AddParameter(cmd, parmName, DbType.Decimal, ParameterDirection.InputOutput, parmName, DataRowVersion.Default, 0);

SQL Server Execution from Profiler:
declare @p2 numeric(1,0)
set @p2=0
exec PROJECT.USPGETREPORTOUTPUTVALUE @pReportID=639,@pReportValue=@p2 output
select @p2

Actual SQL Server SP Declaration:
@pReportID INT
, @pReportValue DECIMAL(18,2) OUTPUT
, @IsDebug INT = 0

Later, I also tried to edit the C# code to, but the SQL which gets executed in the backend does not change.

int size = 8;
byte precision = 18;
byte scale = 2;
bool nullable = false;

database.AddParameter(cmd, parmName, type, size, ParameterDirection.InputOutput, false, precision, scale, parmName, DataRowVersion.Default, value);

Anyone with any ideas....

Thanks a ton!
Apr 24, 2007 at 5:40 PM
This works for me (with my admittedly over-simple implementation):

        public static void GET_REPORT_OUTPUT_VALUE(int pReportID, ref decimal pReportValue, int IsDebug)
            //  Create the Database object, using the default database service. The
            //  default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "USP_GET_REPORT_OUTPUT_VALUE";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            // Add procedure parameters
            db.AddInParameter(dbCommand, "pReportID", DbType.Int32, pReportID);
            db.AddOutParameter(dbCommand, "pReportValue", DbType.Decimal, 0);
            db.AddInParameter(dbCommand, "IsDebug", DbType.Int32, IsDebug);
            // Get output parameter values
            pReportValue = (decimal)db.GetParameterValue(dbCommand, "pReportValue");
May 16, 2007 at 5:41 AM
that source code will round your decimal values to the nearest integer.

anyone know how to make this work without it rounding?
May 16, 2007 at 2:08 PM
The rounding problem has been around for a while, I believe it stems from a particular function overload to AddOutParameter (or maybe AddParameter) that sets the precision to zero and doesn't offer any way to override it. IIRC the fix was to add your own overload that would allow you to pass in the precision.
May 20, 2007 at 7:50 AM
The problem is that Dbtype.Decimal isnt the right type for doing this, use dbtype.Double in place of it and it will work.