WCF服务应用程序中的SQL

debugcn 发表于 Dev

无心

"SELECT SUM(Cost) FROM Repair WHERE RepairDate BETWEEN '" + startdate + "' AND '" + enddate + "'";

大家好，除了将其更改为参数化查询外，此sql还有什么问题吗？当我尝试执行ExecuteScalar时，它为我提供了条件表达式错误中的数据类型不匹配：

public int TotalRepairCost(DateTime startdate, DateTime enddate)
{
        int total;
        OleDbConnection oleConn = new OleDbConnection(connString);

        oleConn.Open();

        string sql = "SELECT SUM(Cost) FROM Repair WHERE RepairDate BETWEEN '" + startdate + "' AND '" + enddate + "'";

        OleDbCommand cmd = new OleDbCommand(sql, oleConn);

        total = (int)cmd.ExecuteScalar();

        oleConn.Close();
        return total;
}

在我的Windows窗体按钮中，单击

private void btnTotal_Click(object sender, EventArgs e)
{
        DateTime startdate = Convert.ToDateTime(txtStartDate.Text);
        DateTime enddate = Convert.ToDateTime(txtEndDate.Text);
        lblTotal.Text = client.TotalRepairCost(startdate, enddate).ToString();
}

在此处输入图片说明

克马特亚塞克

您应该使用命令参数（MSDN）和decimal对total类型。

public decimal TotalRepairCost(DateTime startdate, DateTime enddate)
{
    decimal total;
    OleDbConnection oleConn = new OleDbConnection(connString);

    oleConn.Open();
    string sql = "SELECT SUM(Cost) FROM Repair WHERE RepairDate BETWEEN @StartDate AND @EndDate";

    OleDbCommand cmd = new OleDbCommand(sql, oleConn);
    cmd.Parameters.Add("@StartDate", OleDbType.Date);
    cmd.Parameters["@StartDate"].Value = startdate;

    cmd.Parameters.Add("@EndDate", OleDbType.Date);
    cmd.Parameters["@EndDate"].Value = enddate;

    total = (decimal)cmd.ExecuteScalar();

    oleConn.Close();
    return total;
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。