private void Lilie_Rates_Load(object sender, EventArgs e)
{
dateTimePicker1.MaxDate = DateTime.Today;
try{
String month = dateTimePicker1.Value.ToString("MM-yyyy");
List<String> dates = new List<string>();
List<DateRatePair> lstPairs = new List<DateRatePair>();
String date1;
cmd.Connection = conn;
conn.Open();
cmd.CommandText = ("SELECT Lilie_Date,Rate FROM lilie_master WHERE Lilie_Date LIKE '%" + month + "%' ORDER BY Lilie_Date ASC");
//OleDbDataReader rd = cmd.ExecuteReader();
SqlCeDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
date1 = rd["Lilie_Date"].ToString();
if (dates.Contains(date1))
{
continue;
}
else
{
dates.Add(date1);
DateRatePair aPair = new DateRatePair();
aPair.Date = date1;
String b = aPair.Rate = rd["Rate"].ToString();
lstPairs.Add(aPair);
}
}
conn.Close();
DataTable dt = ListToDataTable(lstPairs);
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
for (int i = 0; i <= dataGridView1.Rows.Count-1 ; i++)
{
if (dataGridView1.Rows[i].Cells[1].Value.ToString() == "")
{
String dts=dataGridView1.Rows[i].Cells[0].Value.ToString();
cmd.Connection = conn;
conn.Open();
int s = 0;
cmd.CommandText = ("UPDATE lilie_master SET Rate='" + s + "' WHERE Lilie_Date='" + dataGridView1.Rows[i].Cells[0].Value.ToString() + "'");
cmd.ExecuteNonQuery();
conn.Close();
}
}
ds.Namespace = "rate_dataset";
dt.TableName = "Rate_tbl";
ds.Tables.Add(dt);
//ds.WriteXmlSchema("Rate_View.xml");
ds.WriteXmlSchema("Rates.xsd");
if (dataGridView1.Rows.Count > 0)
{
button3.Enabled = true;
}
else if (dataGridView1.Rows.Count <= 0)
{
button3.Enabled = false;
MessageBox.Show("Add lili owners first from : \nLilie Owners > Add Owner ", "Add Owners", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("Microsoft Sans Serif", 12F, FontStyle.Bold, GraphicsUnit.Pixel);
dataGridView1.ColumnHeadersDefaultCellStyle.BackColor = Color.PaleVioletRed;
dataGridView1.EnableHeadersVisualStyles = false;
}
catch (Exception ex) {
MessageBox.Show(ex.ToString());
}
}
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
try{
String month = dateTimePicker1.Value.ToString("MM-yyyy");
List<String> dates = new List<string>();
List<DateRatePair> lstPairs = new List<DateRatePair>();
String date1;
cmd.Connection = conn;
conn.Open();
cmd.CommandText = ("SELECT Lilie_Date,Rate FROM lilie_master WHERE Lilie_Date LIKE '%" + month + "%' ORDER BY Lilie_Date ASC");
//OleDbDataReader rd = cmd.ExecuteReader();
SqlCeDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
date1 = rd["Lilie_Date"].ToString();
if (dates.Contains(date1))
{
continue;
}
else
{
dates.Add(date1);
DateRatePair aPair = new DateRatePair();
aPair.Date = date1;
String b=aPair.Rate = rd["Rate"].ToString();
lstPairs.Add(aPair);
}
}
conn.Close();
dt = ListToDataTable(lstPairs);
dataGridView1.DataSource = dt;
dataGridView1.Refresh();
for (int i = 0; i <=dataGridView1.Rows.Count-1; i++) {
if (dataGridView1.Rows[i].Cells[1].Value.ToString() == "") {
cmd.Connection = conn;
conn.Open();
int s=0;
cmd.CommandText = ("UPDATE lilie_master SET Rate='" + s + "' WHERE Lilie_Date='" + dataGridView1.Rows[i].Cells[0].Value.ToString()+ "'");
cmd.ExecuteNonQuery();
conn.Close();
}
}
ds.Namespace = "rate_dataset";
dt.TableName = "Rate_tbl";
ds.Tables.Add(dt);
ds.WriteXmlSchema("Rates.xsd");
if (dataGridView1.Rows.Count > 0)
{
button3.Enabled = true;
}
else if (dataGridView1.Rows.Count <= 0)
{
button3.Enabled = false;
// MessageBox.Show("Add lili owners first from : \nLilie Owners > Add Owner ", "Add Owners", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}