Jahangir Khan

Jahangir Khan

  • NA
  • 27
  • 5.9k

Changes in my program

May 24 2016 8:21 AM
Now I have 2 combo boxes "Year" & "Amount"  on the top of them I do get values for user info, because there are text boxes when called with user ID text boxes fill up with correct data. 
The 2 combo boxes are also filled with correct data but I have to manually select year and the amount corresponding to it.
 
I need help in when I call the data "Year" & "Amount" should appear visable in the combo box and when I lets say select a year then the amount should change accordingly, lat but not the least my reset is not clearing the combo boxes.
 
Hope you can help, the code is as follows:-
  1. using System;  
  2. using System.Windows.Forms;  
  3. using System.Data.SqlClient;  
  4. using System.Data;  
  5.   
  6. namespace dss  
  7. {  
  8.     public partial class Form1 : Form  
  9.   
  10.     {  
  11.         SqlConnection con = new SqlConnection("Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True");  
  12.   
  13.         public Form1()  
  14.         {  
  15.             InitializeComponent();  
  16.   
  17.         }  
  18.   
  19.         private void btnSearch_Click(object sender, EventArgs e)  
  20.         {  
  21.   
  22.             cmbYear.Items.Clear();  
  23.             string sql = "";  
  24.             con.Open();  
  25.             SqlCommand cmd = new SqlCommand();  
  26.   
  27.             try  
  28.             {  
  29.                 sql += "SELECT m.MemberId, m.Name, m.Address, m.Cellular, m.Email, p.PaymentId, p.Year, p.Amount from Members as m";  
  30.                 sql += " INNER JOIN Payments as p ON m.MemberId = p.MemberId";  
  31.                 sql += " WHERE m.MemberId = '" + tbID.Text + "' ORDER BY p.Year ASC";  
  32.   
  33.             cmd.Connection = con;  
  34.             cmd.CommandText = sql;  
  35.               
  36.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  37.                 DataTable dt = new DataTable();  
  38.                 da.Fill(dt);  
  39.                 con.Close();  
  40.                 if(dt.Rows.Count >0)  
  41.                 {  
  42.                     for(int i = 0; i<=dt.Rows.Count -1;i++)  
  43.                     {  
  44.                         tbID.Text = dt.Rows[i]["MemberId"].ToString();  
  45.                         tbName.Text = dt.Rows[i]["Name"].ToString();  
  46.                         tbCellular.Text = dt.Rows[i]["Cellular"].ToString();  
  47.                         tbEmail.Text = dt.Rows[i]["Email"].ToString();  
  48.                         tbAddress.Text = dt.Rows[i]["Address"].ToString();  
  49.   
  50.                         cmbAmount.Items.Add(dt.Rows[i]["Amount"].ToString());  
  51.                         cmbYear.Items.Add(dt.Rows[i]["Year"].ToString());  
  52.   
  53.                     }  
  54.                 }  
  55.             }  
  56.             catch (Exception ex)  
  57.             {  
  58.                 MessageBox.Show(ex.Message.ToString());  
  59.             }  
  60.         }  
  61.   
  62.         //This part displaying og the existing data from all the fileds corrssponding within the database//  
  63.         private void btnAdd_Click(object sender, EventArgs e)  
  64.         {  
  65.             {  
  66.                 con.Open();  
  67.   
  68.                 string Sql = "INSERT INTO Members ( MemberId, Name, Cellular, Email, Address ) VALUES " + " (@Id, @name, @cell, @email, @address)";  
  69.   
  70.                 using (SqlCommand cmd = new SqlCommand(Sql, con))  
  71.   
  72.                 {  
  73.                     cmd.CommandText = Sql;  
  74.                     cmd.Parameters.AddWithValue("@Id", tbID.Text);  
  75.                     cmd.Parameters.AddWithValue("@name", tbName.Text);  
  76.                     cmd.Parameters.AddWithValue("@cell", tbCellular.Text);  
  77.                     cmd.Parameters.AddWithValue("@email", tbCellular.Text);  
  78.                     cmd.Parameters.AddWithValue("@address", tbAddress.Text);  
  79.                     cmd.ExecuteNonQuery();  
  80.   
  81.                     Sql = "INSERT INTO Payments ( MemberId, [Year], [Amount] ) VALUES " + " (@Id, Amount, Year)";  
  82.   
  83.                     cmd.Parameters.Clear();  
  84.                     cmd.CommandText = Sql;  
  85.                     cmd.Parameters.AddWithValue("@Id", tbID.Text);  
  86.                     cmd.Parameters.AddWithValue("@year", cmbYear.Text);  
  87.                     cmd.Parameters.AddWithValue("@amount", cmbAmount.Text);  
  88.                     cmd.ExecuteNonQuery();  
  89.   
  90.                     MessageBox.Show("Data Added");  
  91.   
  92.                     tbID.Clear(); tbName.Clear(); tbCellular.Clear(); tbEmail.Clear(); tbAddress.Clear(); cmbYear.Items.Clear(); cmbAmount.Items.Clear();  
  93.                     con.Close();  
  94.                 }  
  95.             }  
  96.         }  
  97.   
  98.         //This part represents adding of  new input data from all the fileds into the database//  
  99.         private void btnUpdate_Click(object sender, EventArgs e)  
  100.         {  
  101.             try  
  102.             {  
  103.                 SqlCommand cmd = new SqlCommand();  
  104.                 string Sql = "UPDATE Members SET MemberId = '" + tbID.Text + "', Name = '" + tbName.Text + "', Cellular = '" + tbCellular.Text + "', Email = '" + tbEmail.Text + "', Address = '" + tbAddress.Text + "' WHERE MemberId = '" + tbID.Text + "' ";  
  105.                 cmd.CommandText = Sql;  
  106.                 cmd.Connection = con;  
  107.                 con.Open();  
  108.                 cmd.ExecuteNonQuery();  
  109.                 con.Close();  
  110.   
  111.                 Sql = "UPDATE Payments SET MemberId = '" + tbID.Text + "', Year = '" + cmbYear.Text + "', Amount = '" + cmbAmount.Text + "' WHERE MemberId = '" + tbID.Text + "' ";  
  112.                 cmd.CommandText = Sql;  
  113.                 cmd.Connection = con;  
  114.                 con.Open();  
  115.                 cmd.ExecuteNonQuery();  
  116.                 con.Close();  
  117.                 MessageBox.Show("Data Updated");  
  118.                 tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbCellular.Clear(); tbEmail.Clear(); cmbYear.Items.Clear(); cmbAmount.Items.Clear();  
  119.   
  120.             }  
  121.             catch (Exception error)  
  122.             {  
  123.                 MessageBox.Show(error.ToString());  
  124.             }  
  125.         }  
  126.   
  127.         //This part represents deleteing of input data from all the fileds into the database//  
  128.         private void btnDelete_Click(object sender, EventArgs e)  
  129.         {  
  130.             try  
  131.             {  
  132.                 SqlCommand cmd = new SqlCommand();  
  133.                 string Sql = "DELETE FROM Members WHERE MemberId = '" + tbID.Text + "' ";  
  134.                 cmd.CommandText = Sql;  
  135.                 cmd.Connection = con;  
  136.                 con.Open();  
  137.                 cmd.ExecuteNonQuery();  
  138.                 con.Close();  
  139.   
  140.                 Sql = "DELETE  FROM Payments WHERE MemberId = '" + tbID.Text + "' ";  
  141.                 cmd.CommandText = Sql;  
  142.                 cmd.Connection = con;  
  143.                 con.Open();  
  144.                 cmd.ExecuteNonQuery();  
  145.                 tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbCellular.Clear(); tbEmail.Clear(); cmbYear.Items.Clear(); cmbAmount.Items.Clear();  
  146.                 MessageBox.Show("Data Deleted");  
  147.   
  148.                 con.Close();  
  149.             }  
  150.             catch (Exception error)  
  151.             {  
  152.                 MessageBox.Show(error.ToString());  
  153.             }  
  154.   
  155.         }  
  156.   
  157.         //This part represents clearing of input data from all the fileds//  
  158.         private void btnReset_Click(object sender, EventArgs e)  
  159.         {  
  160.             tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbCellular.Clear(); tbEmail.Clear(); cmbYear.Items.Clear(); cmbAmount.Items.Clear();  
  161.         }  
  162.   
  163.         //This part represents shuting down the application//  
  164.         private void btnExit_Click(object sender, EventArgs e)  
  165.         {  
  166.             Application.Exit();  
  167.         }  
  168.   
  169.         }  
  170.     }  
  171.