时间戳

首页 > 解决方案 > SQL 查询无法计算

c# - SQL 查询无法计算

问题描述

我的 SQL 查询无法计算出准确的OTAmount. 我试图向 SQL Server 查询它，查询看起来很好。我唯一改变的是HRate变量。

decimal DR=0;
decimal HRate = 0;

if (rdSal.HasRows)
{
     rdSal.Read();
     DR = Convert.ToDecimal(rdSal.GetString(2));
     HRate = DR / 8;
}

SqlCommand cmd3 = new SqlCommand();
cmd3.Connection = con;
cmd3.CommandType = CommandType.Text;
cmd3.CommandText = "Insert Into Overtime ([EMPID],[Date],[OTMins], 
[OTAmount],[DayofWeek])Select UserID,Date,DateDiff(minute,TimeIn,TimeOut), 
(DateDiff(minute,TimeIn,TimeOut)/60.00)*1.25*"+HRate+",DayofWeek from 
UserLog where DayofWeek='Saturday' and USERID='" + val + "'AND DATE>='" + 
codatebegintxt.Text + "'AND DATE<='" + codateendtxt.Text + "'";

cmd3.ExecuteNonQuery();
con.Close();

标签： c#sql-server

解决方案

您可以将您的 DR 更改为：

DR = Convert.ToDecimal(rdSal.GetString(2), System.Globalization.CultureInfo.CurrentCulture);

而HRate = DR / 8.0不是HRate = DR / 8.

否则如评论中所述，您可以将其更改为：

string query = "Insert Into Overtime (EMPID, Date, OTMins, 
OTAmount, DayofWeek) Select UserID, Date, DateDiff (minute,TimeIn,TimeOut), 
(DateDiff(minute,TimeIn,TimeOut)/60.00) * 1.25 * @HRate, DayofWeek from 
UserLog where DayofWeek = 'Saturday' and USERID = @UserId AND DATE >= 
@DateBegin AND DATE <= @DateEnd";

cmd.Parameters.AddWithValue("HRate", HRate); // can specify precision and scale with .Precision and .Scale.
cmd.Parameters.AddWithValue("UserId", val);
cmd.Parameters.AddWithValue("DateBegin", codatebegintxt.Text);
cmd.Parameters.AddWithValue("DateEnd", codateendtxt.Text);

并通过在变量前添加 @ 来替换命令文本。（希望它不会混淆）。

推荐阅读