STUDENT

39   public DataSet GetLoad(string sortfield)
40   {
41
42    System.Data.OleDb.OleDbConnection conn = GetConnection();
43    DataSet ds = new DataSet();
44
45    try
46    {
47
48     // Dim sql As String = "SELECT ID data1 as [Firstname],data2 as [Lastname],data3 as [20],data4 as [EG],data5 as [1st-10%],data6 as [50pt],data7 as [EG1],data8 as [2nd-10%],data9 as [50pts],data10 as [EG2],data11 as [20%],data12 as [100pt],data13 as [EG3],data14 as [30%],data15 as [100],data16 as [EG4],data17 as [2nd-30%],data18 as [PrelimGrade],data19 as [Remarks]FROM Table1 order by " + sortfield
49     string sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID";
50
51     System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
52     try
53     {
54      da.Fill(ds, "grade1");
55     }
56     finally
57     {
58      da.Dispose();
59     }
60     return ds;
61    }
62    finally
63    {
64     conn.Close();
65     conn.Dispose();
66    }
67   }

68   public void RefreshDGV()
69   {
70
71    System.Data.OleDb.OleDbConnection conn = GetConnection();
72    string sql = default(string);
73    sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID";
74
75    System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
76    DataTable dt = new DataTable("grade1");
77    da.Fill(dt);
78    Form1.Default.DataGridView1.DataSource = dt;
79   }

110   public DataSet SaveItem(string sortfield)
111   {
112
113    System.Data.OleDb.OleDbConnection conn = GetConnection();
114
115    try
116    {
117
118     string SQL = default(string);
119
120     SQL = "INSERT INTO TABLE1 (STUDENTID, DATA1, DATA2, DATA3, DATA4, DATA5,DATA6, DATA7," + "DATA8, DATA9, DATA10, DATA11, DATA12, DATA13, DATA14, DATA15, DATA16, DATA17, DATA18," + "DATA19) VALUES(@STUDENTID, @DATA1, @DATA2, @DATA3, @DATA4, @DATA5,@DATA6, @DATA7,DATA8, @DATA9," + "@DATA10, @DATA11, @DATA12, @DATA13, @DATA14, @DATA15, @DATA16, @DATA17, @DATA18, @DATA19)";
121     System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(SQL, conn);
122     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@STUDENTID", Form1.Default.STUDID.Text));
123     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA1", Form1.Default.TextBox1.Text));
124     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA2", Form1.Default.TextBox2.Text));
125     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA3", Form1.Default.TextBox3.Text));
126     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA4", Form1.Default.TextBox4.Text));
127     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA5", Form1.Default.TextBox5.Text));
128     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA6", Form1.Default.TextBox6.Text));
129     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA7", Form1.Default.TextBox7.Text));
130     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA8", Form1.Default.TextBox8.Text));
131     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA9", Form1.Default.TextBox9.Text));
132     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA10", Form1.Default.TextBox10.Text));
133     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA11", Form1.Default.TextBox11.Text));
134     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA12", Form1.Default.TextBox12.Text));
135     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA13", Form1.Default.TextBox13.Text));
136     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA14", Form1.Default.TextBox14.Text));
137     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA15", Form1.Default.TextBox15.Text));
138     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA16", Form1.Default.TextBox16.Text));
139     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA17", Form1.Default.TextBox17.Text));
140     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA18", Form1.Default.TextBox18.Text));
141     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA19", Form1.Default.TextBox19.Text));
142
143     conn.Open();
144     cmd.ExecuteNonQuery();
145     conn.Close();
146     MessageBox.Show("New data is Added!");
147
148     Form1.Default.TextBox1.Text = "";
149     Form1.Default.TextBox2.Text = "";
150     Form1.Default.TextBox3.Text = "";
151     Form1.Default.TextBox4.Text = "";
152     Form1.Default.TextBox5.Text = "";
153     Form1.Default.TextBox6.Text = "";
154     Form1.Default.TextBox7.Text = "";
155     Form1.Default.TextBox8.Text = "";
156     Form1.Default.TextBox9.Text = "";
157     Form1.Default.TextBox10.Text = "";
158     Form1.Default.TextBox11.Text = "";
159     Form1.Default.TextBox12.Text = "";
160     Form1.Default.TextBox13.Text = "";
161     Form1.Default.TextBox14.Text = "";
162     Form1.Default.TextBox15.Text = "";
163     Form1.Default.TextBox16.Text = "";
164     Form1.Default.TextBox17.Text = "";
165     Form1.Default.TextBox18.Text = "";
166     Form1.Default.TextBox19.Text = "";
167     Form1.Default.STUDID.Text = "";
168
169     RefreshDGV();
170
171    }
172    finally
173    {
174     conn.Close();
175     conn.Dispose();
176    }
177
178    return default(DataSet);
179   }

225   public DataSet deldata(string sortfield)
226   {
227
228    System.Data.OleDb.OleDbConnection conn = GetConnection();
229    DataSet ds = new DataSet();
230
231    try
232    {
233
234     int inc;
235     int MaxRows;
236
237     //Dim sql As String = "select * from table1"
238     //Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)
239
240     try
241     {
242
243      string sqldelete = default(string);
244      sqldelete = "DELETE * FROM table1 WHERE STUDENTID=\'" + Form1.Default.STUDID.Text + "\'";
245      System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqldelete, conn);
246
247      // Gets the records from the table and fills our adapter with those.
248      DataTable dt = new DataTable("grade1");
249      da.Fill(dt);
250      MessageBox.Show("Data has been deleted");
251      clearall();
252
253      Form1.Default.DataGridView1.DataSource = dt;
254
255      RefreshDGV();
256
257     }
258     finally
259     {
260      //da.Dispose()
261     }
262
263     return ds;
264    }
265    finally
266    {
267     conn.Close();
268     conn.Dispose();
269    }
270   }

276   public DataSet updtedata(string sortfield)
277   {
278
279    System.Data.OleDb.OleDbConnection conn = GetConnection();
280    DataSet ds = new DataSet();
281
282    try
283    {
284
285     string sqlupdate = default(string);
286
287     sqlupdate = "UPDATE Table1 set STUDENTID=@STUDENTID, data1=@data1, data2=@data2, data3=@data3, data4=@data4," + "data5=@data5, data6=@data6, data7=@data7, data8=@data8, data9=@data9, data10=@data10," + "data11=@data11, data12=@data12, data13=@data13, data14=@data14, data15=@data15, data16=@data16," + "data17=@data17, data18=@data18, data19=@data19 where STUDENTID=\'" + Form1.Default.STUDID.Text + "\'";
288     System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sqlupdate, conn);
289
290     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@STUDENTID", Form1.Default.STUDID.Text));
291     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA1", Form1.Default.TextBox1.Text));
292     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA2", Form1.Default.TextBox2.Text));
293     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA3", Form1.Default.TextBox3.Text));
294     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA4", Form1.Default.TextBox4.Text));
295     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA5", Form1.Default.TextBox5.Text));
296     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA6", Form1.Default.TextBox6.Text));
297     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA7", Form1.Default.TextBox7.Text));
298     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA8", Form1.Default.TextBox8.Text));
299     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA9", Form1.Default.TextBox9.Text));
300     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA10", Form1.Default.TextBox10.Text));
301     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA11", Form1.Default.TextBox11.Text));
302     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA12", Form1.Default.TextBox12.Text));
303     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA13", Form1.Default.TextBox13.Text));
304     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA14", Form1.Default.TextBox14.Text));
305     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA15", Form1.Default.TextBox15.Text));
306     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA16", Form1.Default.TextBox16.Text));
307     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA17", Form1.Default.TextBox17.Text));
308     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA18", Form1.Default.TextBox18.Text));
309     cmd.Parameters.Add(new System.Data.OleDb.OleDbParameter("@DATA19", Form1.Default.TextBox19.Text));
310
311     conn.Open();
312     cmd.ExecuteNonQuery();
313     conn.Close();
314     MessageBox.Show("Data Updated in the Database");
315     RefreshDGV();
316
317    }
318    finally
319    {
320    }
321    return ds;
322   }

44         private void showAll(ComboBox cmb, string col) {
45             cmb.Items.Clear();
46
47             OleDbCommand cmd;
48             OleDbDataReader reader;
49
50             try {
51                 using (OleDbConnection cn = new OleDbConnection(conString)) {
52                     cmd = new OleDbCommand("SELECT DISTINCT " + col + " FROM tblstudent ORDER BY " + col + ";", cn);
53                     cn.Open();
54                     reader = cmd.ExecuteReader();
55
56                     if (reader.HasRows) {
57                         while (reader.Read()) {
58                             string item = reader[col].ToString();
59
60                             cmb.Items.Add(item);
61                         }
62                     }
63                     cn.Close();
64                 }
65             } catch (Exception ex) {
66                 MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
67             }
68         }

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     }