从存储过程中获取参数时出现溢出异常

I have a store procedure. From this I want output value but there is showing the following error:

我有一个商店程序。从这里我想要输出值但是显示以下错误：

System.OverflowException: Value was either too large or too small for an Int32.

System.OverflowException：对于Int32，值太大或太小。

My Store Procedure

我的商店程序

ALTER proc dbo.[RECnoofDue]        
@acno varchar(100),
@NoofDue numeric(18,2) output        
as 
Begin
select @NoofDue=isnull(SUM(Avgr),0) from (
select NoOfDue, LHEMI, TotalEMIDue, sunday, Avgr from 
(SELECT     COUNT(LHID) AS NoOfDue, LHEMI, SUM(LHEMI) AS TotalEMIDue, 
SUM((Case when LHSundryBalance is null then 0 else LHSundryBalance end)) AS sunday,
((SUM(LHEMI)-SUM((Case when LHSundryBalance is null then 0 else LHSundryBalance end))) / LHEMI) AS Avgr
FROM dbo.TblLoanHistory WHERE (LHAcNo = @acno) AND 
(LHEMIPaid = 0 OR LHEMIPaid IS NULL)AND (LHFileStatus IS NULL) GROUP BY LHSundryBalance, LHEMI,LHFileStatus ) x
union
(SELECT     COUNT(LHID) AS NoOfDue, LHEMI, SUM(LHTotalOverDue) AS TotalEMIDue, 
SUM((Case when LHSundryBalance is null then 0 else LHSundryBalance end)) AS sunday,
((SUM(LHTotalOverDue)-SUM((Case when LHSundryBalance is null then 0 else LHSundryBalance end))) / LHEMI) AS Avgr
FROM dbo.TblLoanHistory WHERE (LHAcNo = @acno) AND (LHEMIPaid = 0 OR LHEMIPaid IS NULL) AND 
(LHFileStatus = 'BK')GROUP BY LHSundryBalance, LHEMI)) bb

end

My ASP.NET C# Code

我的ASP.NET C＃代码

private double NOofDueCount()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OptimaWebCustomerQueryCon"].ConnectionString))
        using (SqlCommand cmd = new SqlCommand("dbo.RECnoofDue", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@acno", SqlDbType.Int);
            cmd.Parameters.Add("@NoofDue", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters["@acno"].Value = hfLoanAcNo.Value;
            conn.Open();
            cmd.ExecuteNonQuery();
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                if (dr.Read())
                {
                    return Convert.ToDouble(cmd.Parameters["@NoofDue"].Value);
                    //int contractID = Convert.ToInt32(cmd.Parameters["@NoofDue"].Value);
                }
            }
            conn.Close();
            return 0;
        }
    }

1 个解决方案

#1

Try using a decimal (SqlDbType.Decimal) instead of an int, and make sure your @acno is a string, (SqlDbType.NVarChar)

尝试使用十进制（SqlDbType.Decimal）而不是int，并确保您的@acno是一个字符串，（SqlDbType.NVarChar）

cmd.Parameters.Add("@acno", SqlDbType.NVarChar);
cmd.Parameters.Add("@NoofDue", SqlDbType.Decimal).Direction = ParameterDirection.Output;

...

return Convert.ToDecimal(cmd.Parameters["@NoofDue"].Value);

Or possibly a double (SqlDbType.Float), which might have been the original intent since your method returns a double:

或者可能是double（SqlDbType.Float），由于您的方法返回double，因此可能是原始意图：

cmd.Parameters.Add("@acno", SqlDbType.NVarChar);
cmd.Parameters.Add("@NoofDue", SqlDbType.Float).Direction = ParameterDirection.Output;

...

double NoofDue = Convert.ToDouble(cmd.Parameters["@NoofDue"].Value);

#1