adding values/difference between values in Access DB using vb.net

Question

0.00/5 (No votes)

See more:

Hello Guys,

I am new to vb.net trying to struggle ... i am doing a small project for my business and i got stuck with this 2 weeks ago and still no progress.

I have an access db table "store" that has columns: (filldate,brand,model,plateno,mileage,litres) where i am storing these info each time a car in my company fills gas. For example:(8/25/2011,Renault,Megane,5487844,3943,20).

What i want to do now is to display in a datagrid the total consumption of gas (litres) with the kilometers driven (mileage) between 2 dates. (to have a report with total consumption)

Any help appreciated ... Thank u guys !!

Posted 28-Aug-11 22:52pm

Jad Jadallah

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shameel · Answer 1 · 2011-08-28T23:11:00

Solution 1

Use this query:

SQL

SELECT SUM(Litres) AS TotalLitres, SUM(Mileage) AS TotalMileage
FROM Store
WHERE FillDate BETWEEN #MM/DD/YYYY# AND #MM/DD/YYYY#

Replace MM/DD/YYYY with actual dates.

Posted 28-Aug-11 23:11pm

Shameel

Comments

Wayne Gaylard 29-Aug-11 5:35am

If you use SUM(Mileage) you will get all the mileages added up. I think the OP wanted the total mileage used in the period.

[no name] 29-Aug-11 10:11am

You mean mileage here means "average" rather than total distance?

Wayne Gaylard 30-Aug-11 5:13am

No, when you enter the mileage of a vehicle, you tend to enter the total mileage of the vehicle. So, say you fill up with 20 L fuel and the vehicle has done 20000 miles, you enter 20000. Then you travel a week and refill with 20 L fuel, the vehicles mileage may now be 21000, and so in total you traveled 1000(21000 - 20000) miles with the initial 20 L. In your case you are getting the SUM of the mileage which would give you 41000 miles for 20 L.

Wayne Gaylard · Answer 2 · 2011-08-28T23:33:00

I would use this function:

C#

OleDbConnection con = new OleDbConnection(@"yourConString");
            OleDbCommand cmd = new OleDbCommand("SELECT MAX(Mileage) - MIN(Mileage), SUM(Litres) FROM Store WHERE PlateNo = @PlateNo AND Filldate BETWEEN @StartDate AND @EndDate",conEzstock);
            cmd.Parameters.AddWithValue("@PlateNo", 5487844);
            cmd.Parameters.AddWithValue("@StartDate", new DateTime(2011,8,1));
            cmd.Parameters.AddWithValue("@EndDate", new DateTime(2011,8,3));
            conEzstock.Open();
            OleDbDataReader dbr = cmd.ExecuteReader();
            int mileage;
            double litresUsed;
            while (dbr.Read())
            {
                mileage = (int)dbr[0];
                litresUsed = (double)dbr[1];
            }
            dbr.Close();
            con.Close();

Hope this helps