private int Get_NewCID(database datab) { int CID; string CIDquerey = @"select max(CID) as CID from Customer"; DataTable tmptbl = new DataTable(); datab.query(CIDquerey); tmptbl.Load(datab.myReader); CID = Convert.ToInt32(tmptbl.Rows[0]["CID"]); CID += 1; return(CID); }
private void VINDropBox_Enter(object sender, EventArgs e) // get list of cars available for date range selected // 20190610TAG { string start = DateOutPicker.Value.ToString("yyyy-MM-dd"); string end = DateExpectedInPicker.Value.ToString("yyyy-MM-dd"); string command = "select car.VIN " + "from car " + "where " + "NOT EXISTS " + "(select carid " + "from rentaltransaction " + "where car.vin = rentaltransaction.carid and '" + start + "' BETWEEN rentaltransaction.dateout and rentaltransaction.dateexpectedin) " + "and " + "NOT EXISTS " + "(select carid " + "from rentaltransaction " + "where car.vin = rentaltransaction.carid and '" + end + "' BETWEEN rentaltransaction.dateout and rentaltransaction.dateexpectedin)" + "and " + "NOT EXISTS " + "(select carid " + "from rentaltransaction " + "where car.vin = rentaltransaction.carid and '" + start + "' < rentaltransaction.dateout and '" + end + "' > rentaltransaction.dateexpectedin)"; try { datab.query(command); VINDropBox.Items.Clear(); while (datab.myReader.Read()) { VINDropBox.Items.Add(datab.myReader[0]); } if (VINDropBox.Items.Count == 0) { VINDropBox.Items.Add("No cars available - try different dates"); } datab.myReader.Close(); } catch (Exception e3) { MessageBox.Show(e3.ToString(), "Error"); } }
private void determineMemberStatus() { // get current customer rental transaction for past year, check count int count = 0; string command = "SELECT count(rID) " + "FROM RentalTransaction, " + "(SELECT * FROM Customer WHERE NOT EXISTS (SELECT cID FROM MemberStatus WHERE MemberStatus.cID = Customer.cID)) as C " + "WHERE C.cID = RentalTransaction.customerID and " + "C.name = '" + CustomerIDDropBox.Text + "' and " + "RentalTransaction.dateOut > '" + DateTime.Now.AddDays(-365) + "';"; try { datab.query(command); while (datab.myReader.Read()) { count = int.Parse(datab.myReader[0].ToString()); } datab.myReader.Close(); } catch (Exception e2) { MessageBox.Show(e2.ToString(), "Error"); } // update to gold member status if (count >= 3) { try { string updateCommand = "INSERT into MemberStatus values(" + "(SELECT cID FROM Customer WHERE name = '" + CustomerIDDropBox.Text + "')" + ",'" + DateTime.Now + "');"; datab.insert(updateCommand); main.TransactionDGV_LoadAll(datab); } catch (Exception e2) { MessageBox.Show(e2.ToString(), "Error"); } } }