Jahangir Khan

Jahangir Khan

  • NA
  • 27
  • 5.9k

Funtion's for ComboBox

May 17 2016 8:20 AM
This program uses fileds there is no data grid. what I am struggling with or you can say I want to add some smart functions to this is that. 
 
  • If a user enters ID "1234" and this data already exists in the SQL Database instead of the program crashing or through exception, shows a pop up alert that the data already exists. 

  • The filed Year is a combo box instead of a text box,  where we can see years like in a drop down list and if user chooses ID "1234" and chooses 2016 for this only the corresponding amount should appear in the amount text box, or if there are other years for this user ID can be checked.
    1. using System;  
    2. using System.Windows.Forms;  
    3. using System.Data.SqlClient;  
    4.   
    5. namespace dss  
    6. {  
    7.     public partial class Form1 : Form  
    8.   
    9.     {  
    10.         SqlConnection con = new SqlConnection("Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True");  
    11.   
    12.         public Form1()  
    13.         {  
    14.             InitializeComponent();  
    15.         }  
    16.         private void Form1_Load(object sender, EventArgs e)  
    17.         {  
    18.             this.paymentsTableAdapter.Fill(this.jG_TestDataSet2.Payments);  
    19.             cmbYear.SelectedIndex = -1;  
    20.   
    21.         }  
    22.   
    23.         private void btnSearch_Click(object sender, EventArgs e)  
    24.         {  
    25.   
    26.   
    27.             if (String.IsNullOrEmpty(tbID.Text))  
    28.             {  
    29.                 MessageBox.Show("Enter ID.""Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);  
    30.   
    31.             }  
    32.   
    33.             SqlDataReader reader;  
    34.             SqlCommand cmd = new SqlCommand();  
    35.             try  
    36.             {  
    37.                 string sql = "SELECT * FROM Members WHERE MemberId = '" + tbID.Text + "' ";  
    38.                 cmd.Connection = con;  
    39.                 cmd.CommandText = sql;  
    40.                 con.Open();  
    41.                 reader = cmd.ExecuteReader();  
    42.                 while (reader.Read())  
    43.                 {  
    44.                     tbID.Text = reader["MemberId"].ToString();  
    45.                     tbName.Text = reader["Name"].ToString();  
    46.                     tbCellular.Text = reader["Cellular"].ToString();  
    47.                     tbEmail.Text = reader["Email"].ToString();  
    48.                     tbAddress.Text = reader["Address"].ToString();  
    49.                 }  
    50.   
    51.   
    52.                 con.Close();  
    53.                 sql = "SELECT * FROM Payments WHERE MemberId  = '" + tbID.Text + "' ";  
    54.                 cmd.Connection = con;  
    55.                 cmd.CommandText = sql;  
    56.                 con.Open();  
    57.                 reader = cmd.ExecuteReader();  
    58.                 while (reader.Read())  
    59.                 {  
    60.                     tbID.Text = reader["MemberId"].ToString();  
    61.                     cmbYear.Text = reader["Year"].ToString();  
    62.                     tbAmount.Text = reader["Amount"].ToString();  
    63.   
    64.                 }  
    65.                 con.Close();  
    66.             }  
    67.             catch (Exception ex)  
    68.             {  
    69.                 MessageBox.Show(ex.Message.ToString());  
    70.             }  
    71.   
    72.         }  
    73.   
    74.         private void btnAdd_Click(object sender, EventArgs e)  
    75.         {  
    76.             {  
    77.                 con.Open();  
    78.   
    79.                 string Sql = "INSERT INTO Members ( MemberId, Name, Cellular, Email, Address ) VALUES " + " (@Id, @name, @cell, @email, @address)";  
    80.   
    81.                 using (SqlCommand cmd = new SqlCommand(Sql, con))  
    82.   
    83.                 {  
    84.                     cmd.CommandText = Sql;  
    85.                     cmd.Parameters.AddWithValue("@Id", tbID.Text);  
    86.                     cmd.Parameters.AddWithValue("@name", tbName.Text);  
    87.                     cmd.Parameters.AddWithValue("@cell", tbCellular.Text);  
    88.                     cmd.Parameters.AddWithValue("@email", tbEmail.Text);  
    89.                     cmd.Parameters.AddWithValue("@address", tbAddress.Text);  
    90.                     cmd.ExecuteNonQuery();  
    91.   
    92.                     Sql = "INSERT INTO Payments ( MemberId, [Year], [Amount] ) VALUES " + "(@id, @year, @amount)";  
    93.   
    94.                     cmd.Parameters.Clear();  
    95.                     cmd.CommandText = Sql;  
    96.                     cmd.Parameters.AddWithValue("@Id", tbID.Text);  
    97.                     cmd.Parameters.AddWithValue("@year", cmbYear.Text);  
    98.                     cmd.Parameters.AddWithValue("@amount", tbAmount.Text);  
    99.                     cmd.ExecuteNonQuery();  
    100.   
    101.                     MessageBox.Show("Data Added");  
    102.   
    103.                     tbID.Clear(); tbName.Clear(); tbCellular.Clear(); tbEmail.Clear(); tbAddress.Clear(); /*cmbYear.Clear();*/ tbAmount.Clear();  
    104.                     con.Close();  
    105.                 }  
    106.             }  
    107.         }  
    108.   
    109.         private void btnUpdate_Click(object sender, EventArgs e)  
    110.         {  
    111.             try  
    112.             {  
    113.                 SqlCommand cmd = new SqlCommand();  
    114.                 string Sql = "UPDATE Members SET MemberId = '" + tbID.Text + "', Name = '" + tbName.Text + "', Cellular = '" + tbCellular.Text + "', Email = '" + tbEmail.Text + "', Address = '" + tbAddress.Text + "' WHERE MemberId = '" + tbID.Text + "' ";  
    115.                 cmd.CommandText = Sql;  
    116.                 cmd.Connection = con;  
    117.                 con.Open();  
    118.                 cmd.ExecuteNonQuery();  
    119.                 con.Close();  
    120.   
    121.                 Sql = "UPDATE Payments SET MemberId = '" + tbID.Text + "', Year = '" + cmbYear.Text + "', Amount = '" + tbAmount.Text + "' WHERE MemberId = '" + tbID.Text + "' ";  
    122.                 cmd.CommandText = Sql;  
    123.                 cmd.Connection = con;  
    124.                 con.Open();  
    125.                 cmd.ExecuteNonQuery();  
    126.                 con.Close();  
    127.                 MessageBox.Show("Data Updated");  
    128.                 tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbCellular.Clear(); tbEmail.Clear(); /*cmbYear.Clear();*/ tbAmount.Clear();  
    129.                   
    130.             }  
    131.             catch (Exception error)  
    132.             {  
    133.                 MessageBox.Show(error.ToString());  
    134.             }  
    135.         }  
    136.   
    137.         private void btnDelete_Click(object sender, EventArgs e)  
    138.         {  
    139.             try  
    140.             {  
    141.                 SqlCommand cmd = new SqlCommand();  
    142.                 string Sql = "DELETE FROM Members WHERE MemberId = '" + tbID.Text + "' ";  
    143.                 cmd.CommandText = Sql;  
    144.                 cmd.Connection = con;  
    145.                 con.Open();  
    146.                 cmd.ExecuteNonQuery();  
    147.                 con.Close();  
    148.   
    149.                 Sql = "DELETE  FROM Payments WHERE MemberId = '" + tbID.Text + "' ";  
    150.                 cmd.CommandText = Sql;  
    151.                 cmd.Connection = con;  
    152.                 con.Open();  
    153.                 cmd.ExecuteNonQuery();  
    154.                 tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbCellular.Clear(); tbEmail.Clear(); /*cmbYear.Clear();*/ tbAmount.Clear();  
    155.                 MessageBox.Show("Data Deleted");  
    156.                   
    157.                 con.Close();  
    158.             }  
    159.             catch (Exception error)  
    160.             {  
    161.                 MessageBox.Show(error.ToString());  
    162.             }  
    163.   
    164.         }  
    165.   
    166.         private void btnReset_Click(object sender, EventArgs e)  
    167.         {  
    168.              tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbCellular.Clear(); tbEmail.Clear(); /*tbYear.Clear();*/ tbAmount.Clear();  
    169.         }  
    170.   
    171.         private void btnExit_Click(object sender, EventArgs e)  
    172.         {  
    173.             Application.Exit();  
    174.         }  
    175.   
    176.         private void cmbYear_SelectedIndexChanged(object sender, EventArgs e)  
    177.         {  
    178.   
    179.         }  
    180.     }  
    181. }  

Answers (2)