Students

70         private void btnAdd_Click(object sender, EventArgs e) {
71             string fname, mi, lname, ext, gender, cur, sy, lev, sec, adv;
72             cur = cmbCurriculum.Text.ToUpper();
73             sy = cmbSchoolYear.Text.ToUpper();
74             lev = cmbLevel.Text.ToUpper();
75             sec = cmbSection.Text.ToUpper();
76             adv = cmbAdviser.Text.ToUpper();
77
78             if ((cur.Length != 0) || (sy.Length != 0) || (lev.Length != 0) || (adv.Length != 0)) {
79                 frmStudent fs = new frmStudent();
80                 if (fs.ShowDialog() == DialogResult.OK) {
81                     fname = fs.fname;
82                     mi = fs.mi;
83                     lname = fs.lname;
84                     ext = fs.ext;
85
86                     gender = fs.gender;
87
88                     try {
89                         OleDbConnection con = new OleDbConnection(conString);
90
91                         OleDbCommand cmd = new OleDbCommand("INSERT INTO tblStudent(fname, mi, lname, ext, gender, curriculum, schoolyear, slevel, ssection, adviser) VALUES ('" + fname + "', '" + mi + "', '" + lname + "', '" + ext + "', '" + gender + "', '" + cur + "', '" + sy + "', '" + lev + "', '" + sec + "', '" + adv + "');", con);
92
93                         con.Open();
94                         cmd.ExecuteNonQuery();
95                         con.Close();
96                         add = true;
97                         loadStudents();
98                     } catch (Exception ex) {
99                         MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
100                     }
101                 }
102             } else {
103                 MessageBox.Show("All fields are required!", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
104             }
105         }

107         private void btnLoad_Click(object sender, EventArgs e) {
108             loadStudents();
109         }

111         private void loadStudents() {
112             lvStudents.Items.Clear();
113
114             OleDbCommand cmd;
115             OleDbDataReader reader;
116
117             string name, gender, cur, sy, lev, adv, sec;
118             cur = cmbCurriculum.Text.ToUpper();
119             sy = cmbSchoolYear.Text.ToUpper();
120             lev = cmbLevel.Text.ToUpper();
121             adv = cmbAdviser.Text.ToUpper();
122             sec = cmbSection.Text.ToUpper();
123
124             try {
125                 using (OleDbConnection cn = new OleDbConnection(conString)) {
126                     string sort = "";
127                     if (add) {
128                         sort = " ORDER BY id DESC";
129                     }
130                     string sql = "SELECT * FROM tblStudent WHERE (curriculum='" + cur + "' AND schoolyear='" + sy + "' AND slevel='" + lev + "' AND ssection='" + sec + "' AND adviser='" + adv + "')" + sort + ";";
131
132                     cmd = new OleDbCommand(sql, cn);
133                     cn.Open();
134                     reader = cmd.ExecuteReader();
135                     if (reader.HasRows) {
136                         while (reader.Read()) {
137                             string id = reader["ID"].ToString();
138                             string lname = reader["lname"].ToString();
139                             string ext = reader["ext"].ToString();
140                             if (ext.Length > 0) {
141                                 ext = " " + ext;
142                             } else {
143                                 ext = "";
144                             }
145                             string fname = ", " + reader["fname"].ToString();
146                             string mname = reader["mi"].ToString();
147                             if (mname.Length > 0) {
148                                 mname = " " + mname[0] + ".";
149                             } else {
150                                 mname = " ";
151                             }
152                             name = lname + ext + fname + mname;
153                             gender = reader["gender"].ToString();
154
155                             string[] row = { id, name, gender };
156                             ListViewItem item = new ListViewItem(row);
157                             lvStudents.Items.Add(item);
158                         }
159                     }
160                     cn.Close();
161                 }
162             } catch (Exception ex) {
163                 MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
164             }
165             txtResults.Text = "Result(s): " + lvStudents.Items.Count;
166         }

168         private void editToolStripMenuItem_Click(object sender, EventArgs e) {
169             try {
170                 string eid = lvStudents.FocusedItem.SubItems[0].Text;
171
172                 frmStudent fs = new frmStudent(eid);
173                 if (fs.ShowDialog() == DialogResult.OK) {
174                     loadStudents();
175                 }
176             } catch {}
177         }
178
179         private void refreshToolStripMenuItem_Click(object sender, EventArgs e) {
180             loadStudents();
181             showAll(cmbAdviser, "Adviser");
182             showAll(cmbCurriculum, "Curriculum");
183             showAll(cmbLevel, "SLevel");
184             showAll(cmbSchoolYear, "SchoolYear");
185             showAll(cmbSection, "SSection");
186         }
187
188         private void frmClass_Load(object sender, EventArgs e) {
189             if (edit) {
190                 OleDbConnection con = new OleDbConnection(conString);
191                 OleDbCommand cmd;
192                 OleDbDataAdapter da;
193                 DataTable dt;
194
195                 cmd = new OleDbCommand("SELECT * FROM tblStudent WHERE id=" + aid + ";", con);
196                 da = new OleDbDataAdapter(cmd);
197                 dt = new DataTable();
198                 da.Fill(dt);
199
200                 if (dt.Rows.Count > 0) {
201                     cmbCurriculum.Text = dt.Rows[0][6].ToString();
202                     cmbSchoolYear.Text = dt.Rows[0][7].ToString();
203                     cmbLevel.Text = dt.Rows[0][8].ToString();
204                     cmbSection.Text = dt.Rows[0][9].ToString();
205                     cmbAdviser.Text = dt.Rows[0][10].ToString();
206                     loadStudents();
207                 }
208             }
209         }
210     }

26         private void searchStudents() {
27             string stu = txtStudent.Text.ToUpper();
28
29             lvStudents.Items.Clear();
30
31             if (stu == "") {
32                 txtResults.Text = "Search field required!";
33                 return;
34             }
35
36             OleDbCommand cmd;
37             OleDbDataReader reader;
38
39             string id, name, gender, cur, sy, lev, adv, sec;
40
41             try {
42                 using (OleDbConnection cn = new OleDbConnection(conString)) {
43                     string sql = "";
44                     string order = " ORDER BY schoolyear, gender DESC, lname, fname, mi;";
45                     if (stu.Length == 0) {
46                         sql = "SELECT TOP 50 * FROM tblStudent" + order;
47                     } else {
48                         sql = "SELECT * FROM tblStudent WHERE (fname LIKE '%" + stu + "%' OR lname LIKE '%" + stu + "%' OR ext LIKE '%" + stu + "%')" + order;
49                     }
50
51                     cmd = new OleDbCommand(sql, cn);
52                     cn.Open();
53                     reader = cmd.ExecuteReader();
54                     if (reader.HasRows) {
55                         while (reader.Read()) {
56                             id = reader["id"].ToString();
57                             string lname = reader["lname"].ToString();
58                             string ext = reader["ext"].ToString();
59                             if (ext.Length > 0) {
60                                 ext = " " + ext;
61                             } else {
62                                 ext = "";
63                             }
64                             string fname = ", " + reader["fname"].ToString();
65                             string mname = reader["mi"].ToString();
66                             if (mname.Length > 0) {
67                                 mname = " " + mname[0] + ".";
68                             } else {
69                                 mname = " ";
70                             }
71                             name = lname + ext + fname + mname;
72                             gender = reader["gender"].ToString();
73                             cur = reader["curriculum"].ToString();
74                             sy = reader["schoolyear"].ToString();
75                             lev = reader["slevel"].ToString();
76                             adv = reader["adviser"].ToString();
77                             sec = reader["ssection"].ToString();
78                             string[] row = { id, name, gender, sy, sec, adv, cur, lev };
79                             ListViewItem item = new ListViewItem(row);
80                             lvStudents.Items.Add(item);
81                         }
82                     }
83                     cn.Close();
84                 }
85             } catch (Exception ex) {
86                 MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
87             }
88             txtResults.Text = "Result(s): " + lvStudents.Items.Count;
89         }

91         private void btnSearch_Click(object sender, EventArgs e) {
92             searchStudents();
93         }

99         private void refreshToolStripMenuItem_Click(object sender, EventArgs e) {
100             searchStudents();
101         }

103         private void editToolStripMenuItem_Click(object sender, EventArgs e) {
104             try {
105                 string eid = lvStudents.FocusedItem.SubItems[0].Text;
106
107                 frmStudent fs = new frmStudent(eid);
108                 if (fs.ShowDialog() == DialogResult.OK) {
109                     searchStudents();
110                 }
111             } catch { }
112         }
113
114         private void viewStudentClassToolStripMenuItem_Click(object sender, EventArgs e) {
115             try {
116                 string eid = lvStudents.FocusedItem.SubItems[0].Text;
117
118                 frmClass fs = new frmClass(eid);
119                 if (fs.ShowDialog() == DialogResult.OK) {
120                     searchStudents();
121                 }
122             } catch { }
123         }
124
125         private void lvStudents_SelectedIndexChanged(object sender, EventArgs e) {
126
127         }
128     }
129}