Funtion's for ComboBox

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;
5. namespace dss
6. {
7. public partial class Form1 : Form
9. {
10. SqlConnection con = new SqlConnection("Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True");
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;
21. }
23. private void btnSearch_Click(object sender, EventArgs e)
24. {
27. if (String.IsNullOrEmpty(tbID.Text))
28. {
29. MessageBox.Show("Enter ID.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
31. }
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. }
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();
64. }
65. con.Close();
66. }
67. catch (Exception ex)
68. {
69. MessageBox.Show(ex.Message.ToString());
70. }
72. }
74. private void btnAdd_Click(object sender, EventArgs e)
75. {
76. {
77. con.Open();
79. string Sql = "INSERT INTO Members ( MemberId, Name, Cellular, Email, Address ) VALUES " + " (@Id, @name, @cell, @email, @address)";
81. using (SqlCommand cmd = new SqlCommand(Sql, con))
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();
92. Sql = "INSERT INTO Payments ( MemberId, [Year], [Amount] ) VALUES " + "(@id, @year, @amount)";
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();
101. MessageBox.Show("Data Added");
103. tbID.Clear(); tbName.Clear(); tbCellular.Clear(); tbEmail.Clear(); tbAddress.Clear(); /*cmbYear.Clear();*/ tbAmount.Clear();
104. con.Close();
105. }
106. }
107. }
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();
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();
130. }
131. catch (Exception error)
132. {
133. MessageBox.Show(error.ToString());
134. }
135. }
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();
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");
157. con.Close();
158. }
159. catch (Exception error)
160. {
161. MessageBox.Show(error.ToString());
162. }
164. }
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. }
171. private void btnExit_Click(object sender, EventArgs e)
172. {
173. Application.Exit();
174. }
176. private void cmbYear_SelectedIndexChanged(object sender, EventArgs e)
177. {
179. }
180. }
181. }

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jahangir Khan10y ago
Please have a look at this video so you can have a better over view of the program .
https://drive.google.com/folderview?id=0BxTO_36oXWNKbTk0cVNTblNTSXc&usp=sharing
Amit Gupta10y ago
Hi There is a solution where you can check that id 1234 is present or not. You can use offline architecture called dataset or datatable Where you can match from datatable instead of sql server. Datatable.Select(expression); It returns no of rows that matches your expression