We help IT Professionals succeed at work.

C# Simple Math Calculation with DataSet Query

on
326 Views
Hi guys,

I want to try do something quite simple, but since I don't know how to do it, I need your help...

I need to:

Store the contents of "PurchaseDateTimePicker" into datePurchaseDateVar
Store the contents of "vehicleUIDTextBox" into intPurchaseDateVar

Run a query against the table "tblMOTHistory", to select the value in "Date" where "VehicleUID" is the same as intPurchaseDateVar AND "Date" is the most recent, and store it into datePreviousMOTVar

and then do this math logic:

string strDueDate;
if (PurchaseDate < "3 years")
{
strDueDate = PurchaseDate + "3 Years";
} else {
if exist ("Previous MOT")
{
if (("Previous MOT" + "1 Year") < "Today")
{
strDueDate = "Today"
} else {
strDueDate = "Previous MOT" + "1 Year";
}
} else {
strDueDate = "Today";
}
}

Could somebody help me out with this please? If it helps at all, this is an extention on this project:

http://www-new.experts-exchange.com/Programming/Languages/C_Sharp/Q_22131948.html

Just to clarify, it's an Access Database connected to the C# application (mentioned this as it probably changes the SQL code required).

If I need to be more clear, please let me know guys!

Cheers!
Comment
Watch Question

View Solutions Only

CERTIFIED EXPERT

Commented:
assuming purchase date is the date u metiond <Date" is the most recent>

DataTable tblMOTHistory = new DataTable();
int intPurchaseDateVar = 1;

tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now });
tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now.Subtract(new TimeSpan(1, 0, 0, 0, 0)) });
tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now.Subtract(new TimeSpan(3, 0, 0, 0, 0)) });
tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now.Subtract(new TimeSpan(4, 0, 0, 0, 0)) });
DataRow[] Rows =
tblMOTHistory.Select("VehicleUID = " + intPurchaseDateVar + "AND Date = MAX(Date)");
foreach (DataRow FRow in Rows)
{
DateTime DueDate = DateTime.MinValue;
DateTime FDate = (DateTime)FRow["Date"];
if (FDate.Year < 3)
{
}
else
{
}
Console.WriteLine(FRow["Date"].ToString());
}
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}

i dint understand the else condition
<
else {
if exist ("Previous MOT")
{
if (("Previous MOT" + "1 Year") < "Today")
{
strDueDate = "Today"
} else {
strDueDate = "Previous MOT" + "1 Year";
}
}
>

Commented:
Hi gauthampj,

Thank you for the reply, but it confused me a little bit...

"DataTable tblMOTHistory = new DataTable();"

Looks like you are creating a DataTable called tblMOTHistory, while what I wanted to do was extract data from the actual table in the connected database (MS Access File). I have a DataSet, BindingSources and TableAdaptors already, if that at all helps with this situation?

Cheers!
CERTIFIED EXPERT

Commented:
DataTable tblMOTHistory = new DataTable();
int intPurchaseDateVar = 1;

tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now });
tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now.Subtract(new TimeSpan(1, 0, 0, 0, 0)) });
tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now.Subtract(new TimeSpan(3, 0, 0, 0, 0)) });
tblMOTHistory.Rows.Add(new object[] { "1", DateTime.Now.Subtract(new TimeSpan(4, 0, 0, 0, 0)) });

i did this much for creating test data what actually matterts to u is from

DataRow[] Rows =
tblMOTHistory.Select("VehicleUID = " + intPurchaseDateVar + "AND Date = MAX(Date)");

onwards
:-)

Commented:
Ah ok, sorry about the confusion there! I'm very very new to C# so still learning a lot... Shall try that shortly and get back to you. :o)

Commented:
gauthampj,

I ammended your code to have the correct logic for the entire calculation, but I've got 4 errors, could possibly take a look for me please? The updated code is below:

DataRow[] Rows = tblMOTHistory.Select("VehicleUID = " + vehicleUID + "AND Date = MAX(Date)");
foreach (DataRow FRow in Rows)
{
DateTime DueDate = DateTime.MinValue;
DateTime FirstMOTDate = (DateTime)FRow["Date"];
DateTime PurchaseDate = vehicleUIDTextBox.Text;
if (FirstMOTDate)
{
// There is a previous MOT
if (FirstMOTDate.Year < 1)
{
// Previous MOT Not expired yet
}
else
{
// Previous MOT has expired
DueDate = DateTime.Today;
}
}
else
{
// There is not a previous MOT
if (PurchaseDate.Year < 3)
{
// Vehicle is younger than 3 years
}
else
{
// Vehicle is older than 3 years with not MOT
DueDate = DateTime.Today;
}
}
lblMOTDueDate.Text = FRow["Date"].ToString();
}

and the errors are:

Error      1      The name 'tblMOTHistory' does not exist in the current context      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      52      30      Vehicle Maintenance Manager
Error      2      Cannot implicitly convert type 'string' to 'System.DateTime'      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      57      41      Vehicle Maintenance Manager
Error      3      Cannot implicitly convert type 'System.DateTime' to 'bool'      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      58      21      Vehicle Maintenance Manager
Error      4      The name 'FDate' does not exist in the current context      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      64      35      Vehicle Maintenance Manager

Thank you! :o)

Commented:
The first error I am guessing I need to point to either a BindingSource or TableAdaptor?

Commented:
Ok removed all errors appart from the first one....

Here is what I got so far:

DataRow[] Rows = tblMOTHistory.Select("VehicleUID = " + vehicleUID + "AND Date = MAX(Date)");
foreach (DataRow FRow in Rows)
{
DateTime DueDate = DateTime.MinValue;
DateTime FirstMOTDate = (DateTime)FRow["Date"];
DateTime PurchaseDate = (DateTime)(TypeDescriptor.GetConverter(new DateTime(1990, 5, 6)).ConvertFrom(vehicleUIDTextBox.Text));
if (FirstMOTDate.ToString() != "")
{
// There is a previous MOT
if (FirstMOTDate.Year < 1)
{
// Previous MOT Not expired yet
}
else
{
// Previous MOT has expired
DueDate = DateTime.Today;
}
}
else
{
// There is not a previous MOT
if (PurchaseDate.Year < 3)
{
// Vehicle is younger than 3 years
}
else
{
// Vehicle is older than 3 years with not MOT
DueDate = DateTime.Today;
}
}
lblMOTDueDate.Text = FRow["Date"].ToString();
}

So what should "tblMOTHistory" be pointing towards? I have tried both BindingSource and TableAdaptor, not sure what else I need to try...
CERTIFIED EXPERT

Commented:
error 1
tblMOTHistory is a datatable rt. where have u decalred it .this error comes wher u havent decalred it or it's not accesble

error 2

DateTime PurchaseDate = vehicleUIDTextBox.Text //trying to assign test to a datetime

u should have dome

DateTime PurchaseDate = DateTime.Parse(vehicleUIDTextBox.Text)

1 dbt then data here 'vehicleUIDTextBox' will be datetime rt.

error 3

uhave used 'FDate'  somewhere which u havent decalred

if(FirstMOTDate) // u cant do that

it should be for example

if(FirstMOTDate > DateTime.Now)

Error 4

Fdate is undeclared or not accesible where have u decalred it and where are u usinf it
CERTIFIED EXPERT

Commented:
what is the BindingSource of the grdi is it a datatbel

it should be binding source
can u post the code where u bind the data

Commented:
Ok, this seems to be what I needed:

DataRow[] Rows = vehicle_Maintenance_ManagerDataSet.tblMotHistory.Select("VehicleUID = " + vehicleUID + "AND Date = MAX(Date)");

But on on testing it, it outputs this everytime:

12/12/2006 00:00:00

Any ideas on what could be wrong?

Here is the current code:

DataRow[] Rows = vehicle_Maintenance_ManagerDataSet.tblMotHistory.Select("VehicleUID = " + vehicleUID + "AND Date = MAX(Date)");
foreach (DataRow FRow in Rows)
{
DateTime DueDate = DateTime.MinValue;
DateTime FirstMOTDate = (DateTime)FRow["Date"];
DateTime PurchaseDate = (DateTime)(TypeDescriptor.GetConverter(new DateTime(1990, 5, 6)).ConvertFrom(purchaseDateDateTimePicker.Text));
if (FirstMOTDate.ToString() != "")
{
// There is a previous MOT
if (FirstMOTDate.Year < 1)
{
// Previous MOT Not expired yet
}
else
{
// Previous MOT has expired
DueDate = DateTime.Today;
}
}
else
{
// There is not a previous MOT
if (PurchaseDate.Year < 3)
{
// Vehicle is younger than 3 years
}
else
{
// Vehicle is older than 3 years with not MOT
DueDate = DateTime.Today;
}
}
lblMOTDueDate.Text = FRow["Date"].ToString();
}
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
Whoah, amazed you're up this early, only just noticed that you are replying! :o)

The code you just posted gave me the following errors:

Error      1      Newline in constant      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      59      39      Vehicle Maintenance Manager
Error      2      ) expected      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      59      57      Vehicle Maintenance Manager
Error      3      ; expected      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      59      57      Vehicle Maintenance Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
That seems to have done it, but could I ask you how to fix one thing please?

I know my actual question is completed, but do you think you could tell me how to remove the:

00:00:00

at the end of the returned string please? Sorry, for the hassle...
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
Thank you very much for all your patience and help on this one! I know it was quite a big question, and should of been split up into a few little questions, and shall try not to do that again, but thank you none the less! :o)
CERTIFIED EXPERT

Commented:
:o)

Commented:
Sorry man, I just noticed I got this error with that little ToString() code:

Error      1      No overload for method 'ToString' takes '1' arguments      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      93      38      Vehicle Maintenance Manager
CERTIFIED EXPERT

Commented:
DateTime.Parse(FRow["Date"]).ToString("dd/MM/yyyy")

next time u award points dont split it against a single expert like what u just did

u selected three of my comments as answers and awarded points but actually only a part comes to me here my points tally would added by 300*4  b'coz of the split

i dont know if it's a bug in EE.

so next time if u are awardingpoints to a single expert just select one of his answers and do what u did now when multiple experts are involved

Commented:
gauthampj,

I am so sorry about that, I'll tell the admins about it, and have them give you all your points. But in future, I will do as you said.

Commented:
The code now gave out:

Error      1      The best overloaded method match for 'System.DateTime.Parse(string)' has some invalid arguments      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      93      38      Vehicle Maintenance Manager
Error      2      Argument '1': cannot convert from 'object' to 'string'      C:\Documents and Settings\Justin Nel\My Documents\My Databases\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\Vehicle Maintenance Manager\frmMain.cs      93      53      Vehicle Maintenance Manager

Should I create a new question for this one?
CERTIFIED EXPERT

Commented:
no need
my mistake sorry

DateTime.Parse(FRow["Date"].ToString()).ToString("dd/MM/yyyy")

CERTIFIED EXPERT

Commented:
or better

((DateTime)FRow["Date"]).ToString("dd/MM/yyyy")

Commented:
That did the trick, thank you!
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.