Rick1990 Rick1990 - 22 days ago 8
MySQL Question

Trouble with my Query

I have made a simple database application however I am having trouble adding in one final Column. The amount column needs to be populated with the Yearly Premium from the PRODUCTS table and then divided by either 1, 12 or 26 depending on if it is payed Yearly(1), Monthly(12), or Fortnightly(26).

I assume I need to join the SALES and PRODUCTS tables using ProductID but cannot figure out how to populate the amount column depending on how they pay (F / M / Y).

Below is the two screen shots of each table in the program, and the code I am using to populate the Main Sales form at the moment.

Sales Screen where amount needs to be calculated.

The Yearly premium needs to come from here

private void DisplaySales()
{
string selectQuery;
selectQuery = "SELECT * FROM Sales";
List<Sale> saleList = new List<Sale>();
try
{
// Automatically open and close the connection
using (var conn = ConnectionManager.DatabaseConnection())
using (var cmd = new SqlCommand(selectQuery, conn))
using (var rdr = cmd.ExecuteReader())
{

while (rdr.Read())
{
//Define the list items
var sale = new Sale(
int.Parse(rdr["SaleID"].ToString()),
int.Parse(rdr["CustomerID"].ToString()),
int.Parse(rdr["ProductID"].ToString()),
rdr["Payable"].ToString(),
DateTime.Parse(rdr["StartDAte"].ToString()));

saleList.Add(sale);
}
dgvSales.DataSource = saleList;
}
}
catch (Exception ex)
{
MessageBox.Show("Unsuccessful" + ex);
}
}


Thank you heaps for any help regarding my question.

Answer Source

You're right about the join.

selectQuery = "SELECT * FROM Sales AS Sal INNER JOIN Product AS Pro ON Sal.ProductID = Pro.ProductID

Then have a statement that does the logic when you get the results in the while loop.

int amount;
switch(rdr["Payable"].ToString()) {
    case "M": amount =         rdr["YearlyPremium"] / 12
    break;
    case "F": amount =     rdr["YearlyPremium"] / 26
    break;
    default: amount =    rdr["YearlyPremium"];
    break;

}

Then add amount to the sale object and pass it into the Sale constructor. You could create your own enum or dictionary to avoid the switch statement. However I would suggest inserting the correct amount when the sale is inserted. Cheers

Make sure your Sale class has this