CODEWITHSUNDEEP
c#winformsms-access
Ali Raza
Ali Raza

Reputation: 1241

Total revenue between two dates not working c# ms access database

I am creating a C# project with an MS Access as the database back-end. I want to show the sum of prices between two dates. I have create the following query method:

public DataTable selectDates(string dateFrom,string dateTo)
{
    initilize();
    _conn.ConnectionString = _cs;
    _cmd.Connection = _conn;

    _cmd.CommandText = "Select Sum(tPrice) AS Price from [tblInventory] where Date_Of_Installation BETWEEN '" + dateFrom + "' AND '" + dateTo + "'";
    _da.SelectCommand = _cmd;
    _da.Fill(_dt);
    return _dt;
}

On the form end I have two time pickers and a button:

private void btnTotalRevenue_Click(object sender, EventArgs e)
{
    dt = db.selectDates((pickerDateFrom.Text).ToString().Trim(), (pickerDateTo).ToString().Trim());
    string a = dt.Rows[0]["Price"].ToString();
    MessageBox.Show(a);   
}

but the messagebox is showing an empty string: .

Upvotes: 0

Views: 135

Answers (3)

Abdellah OUMGHAR
Abdellah OUMGHAR

Reputation: 3745

You can use DateTime parameters in methode selectDates like this :

public DataTable selectDates(DateTime dateFrom, DateTime dateTo)
{
    initilize();
    _conn.ConnectionString = _cs;
    _cmd.Connection = _conn;
    string query = string.Format("Select Sum(tPrice) AS Price from [tblInventory] where Date_Of_Installation BETWEEN #{0}# AND #{1}#", dateFrom.ToString("yyyy/MM/dd"), dateTo.ToString("yyyy/MM/dd"));
    _cmd.CommandText = query;
    _da.SelectCommand = _cmd;
    _da.Fill(_dt);
    return _dt;
}

and call method wiyhout casing value of DateTimePicker to string like this :

void btnTotalRevenue_Click(object sender, EventArgs e)
{
    dt = db.selectDates(pickerDateFrom.Value, pickerDateTo.Value);
    string a = dt.Rows[0]["Price"].ToString();
    MessageBox.Show(a);
}

But, I advice you to use the Parameter to use DbType.DateTime and then pass the DateTime directly to the parameter (do not need to convert) , and also avoid SQL injections , like this :

public DataTable selectDates(DateTime dateFrom, DateTime dateTo)
{
    initilize();
    _conn.ConnectionString = _cs;
    string query = "Select Sum(tPrice) AS Price from [tblInventory] where Date_Of_Installation BETWEEN @startDate AND @endDate";
    OleDbCommand _cmd = new OleDbCommand(query, _conn);
    cmd.Parameters.AddWithValue("@startDate ", DbType.DateTime).Value = dateFrom;
    cmd.Parameters.AddWithValue("@endDate ", DbType.DateTime).Value = dateTo;
    _da.SelectCommand = _cmd;
    _da.Fill(_dt);
    return _dt;
}

Upvotes: -1

CathalMF
CathalMF

Reputation: 10055

In MS Access you need to format your dates and wrap them in # symbols.

example. 

SELECT Sum(tPrice) AS Price from [tblInventory] 
WHERE Date_Of_Installation BETWEEN #2016/01/01# AND #2016/02/01#

So your C# should become. 

private void btnTotalRevenue_Click(object sender, EventArgs e)
{
    dt = db.selectDates(pickerDateFrom.SelectedDate.Value.ToString("#yyyy/MM/dd#"), pickerDateTo.SelectedDate.Value.ToString("#yyyy/MM/dd#"));
    string a = dt.Rows[0]["Price"].ToString();
    MessageBox.Show(a);   
}

Upvotes: 4

Beldi Anouar
Beldi Anouar

Reputation: 2180

Try to change your query to :

 _cmd.CommandText = "Select Sum(tPrice) AS Price from [tblInventory] where
 Date_Of_Installation BETWEEN '" + dateFrom.ToString("yyyy-MM-dd")  + "' AND '" + 
 dateTo.ToString("yyyy-MM-dd")  + "'";

Upvotes: 0

Related Questions