CODEWITHSUNDEEP
c#sqlwcfoledboledbcommand
Mindless
Mindless

Reputation: 2357

SQL in my WCF Service Application

"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 enter image description here

enter image description here

Upvotes: 0

Views: 136

Answers (1)

kmatyaszek
kmatyaszek

Reputation: 19296

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

Upvotes: 2

Related Questions