SQL in my WCF Service Application

Question

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

Hi all, besides changing it to parameterized queries, it there anything wrong with this sql? when i try to ExecuteScalar, it gives me Data type mismatch in criteria expression error:

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;
}

In my windows form button click

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();
}

enter image description here

@germi thanks germi, my data format for RepairDate column in my access database is Date/Time, and when i input my dates i inserted like this "10/10/2012" for startdate and "10/10/2013" for enddate, i also tried other options like "10/10/2012 10:00:00AM" — Mindless, Oct 27 '13 at 13:04
@yzwboy try using this query: `string sql = "SELECT SUM(Cost) FROM Repair WHERE RepairDate BETWEEN '" + startdate.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + enddate.ToString("yyyy-MM-dd HH:mm:ss") + "'";` — kmatyaszek, Oct 27 '13 at 13:05

score 2 · Accepted Answer · answered Oct 27 '13 at 13:41

You should use command parameters (msdn) and decimal for total type.

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;
}

Thank you so much :) all fixed – Mindless Oct 27 '13 at 13:56 — Mindless, Oct 27 '13 at 13:56

SQL in my WCF Service Application

1 Answers1