private void cSchool_SelectedIndexChanged(object sender, EventArgs e) { if (panel3.Visible == true && RadStudent.Checked) { LoadRealData(" RegisterStudent.SchoolID = " + MyModules.GetIt4Me(cGrp.Text, " - ")); } if (panel3.Visible == true && radStaff.Checked) { LoadRealData(" RegisterVendor.Category = '" + cGrp.Text + "'"); } }
private void RadStudent_CheckedChanged(object sender, EventArgs e) { panel3.Visible = true; if (RadStudent.Checked) { lblGrp.Text = "School"; } oLoadSchName(); { LoadRealData(" RegisterStudent.SchoolID = " + MyModules.GetIt4Me(cGrp.Text, " - ")); } }
private void TV_AfterSelect(object sender, TreeViewEventArgs e) { string strQry = ""; if (e.Node.Text == "ALL") { strQry = "<ALL>"; } else { switch (CmbCriteria.Text.ToString()) { case "State of Origin": strQry = " RegisterStudent.State = '" + e.Node.Text + "'"; break; case "Bank": strQry = " RegisterStudent.BankName = '" + e.Node.Text + "'"; break; case "Degree": strQry = " RegisterStudent.Degree = '" + e.Node.Text + "'"; break; case "EndYear": strQry = " RegisterStudent.EndYear = " + e.Node.Text; break; case "School": strQry = " RegisterStudent.SchoolID = " + MyModules.GetIt4Me(e.Node.Text, " - "); // str = "SELECT DISTINCT [SchoolID]+' - ' +[SchName] AS dText FROM RegisterStudent LEFT OUTER JOIN RegisterSchool ON RegisterStudent.SchoolID = RegisterSchool.Sn"; break; case "Category": strQry = " RegisterStudent.Category = '" + e.Node.Text + "'"; break; case "Region": strQry = " RegisterStudent.Region = '" + e.Node.Text + "'"; break; } } LoadLvList(strQry); }
private void LoadClientBalances() { try { SqlConnection cnSQL = new SqlConnection(MyModules.strConnect); SqlCommand cmSQL = new SqlCommand(); cmSQL.Connection = cnSQL; cnSQL.Open(); string strQry1 = ""; cmSQL.Parameters.Clear(); if (RadSchool.Checked == true) { cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='SCHOOL' GROUP BY RefNo, Name"; } else if (RadStudent.Checked == true) { if (cGrp.Text != "ALL") { strQry1 = strQry1 + (string.IsNullOrEmpty(strQry1) ? " " : " AND ") + " SchoolID =" + MyModules.GetIt4Me(cGrp.Text, " - "); } cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='STUDENT' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY RefNo, Name"; } else if (RadVendor.Checked == true) { cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='VENDOR' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY RefNo, Name"; } else { if (cGrp.Text != "ALL") { strQry1 = strQry1 + (string.IsNullOrEmpty(strQry1) ? " " : " AND ") + " RegisterVendor.Category ='" + cGrp.Text + "'"; } cmSQL.CommandText = "SELECT Ledger.RefNo, Ledger.Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger LEFT OUTER JOIN RegisterVendor ON Ledger.RefNo = RegisterVendor.Sn WHERE Ledger.Source='STAFF' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY Ledger.RefNo, Ledger.Name"; } cmSQL.CommandType = CommandType.Text; dataAdapterA = new SqlDataAdapter(cmSQL); SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapterA); DataTable tableA = new DataTable(); tableA.Locale = System.Globalization.CultureInfo.InvariantCulture; dataAdapterA.Fill(tableA); bindingSourceA.DataSource = tableA; DataGridViewCellStyle myStyle = new DataGridViewCellStyle(); DataGridViewCellStyle myStyle1 = new DataGridViewCellStyle(); myStyle.ForeColor = Color.Red; myStyle.Format = "N2"; myStyle1.Format = "N2"; myStyle.Alignment = DataGridViewContentAlignment.MiddleRight; myStyle1.ForeColor = Color.Black; myStyle1.Alignment = DataGridViewContentAlignment.MiddleRight; cnSQL.Close(); LoadDetails("-899", "sfsfsfs@@"); return; } catch (Exception ex) { MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void cBank_SelectedIndexChanged(object sender, EventArgs e) { tBank.Text = MyModules.GetIt4Me(cBank.Text, " - "); tBankCode.Text = MyModules.Mid(cBank.Text, Microsoft.VisualBasic.Strings.Len(tBank.Text) + 3, -1); }
private void cmdUpload_Click(object sender, EventArgs e) { try { if (RadStudent.Checked == true) { if (Dgrid.Columns.Count != DGridReal.Columns.Count - 3) { MessageBox.Show("Invalid Column Mapping", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } else { if (Dgrid.Columns.Count != DGridReal.Columns.Count - 2) { MessageBox.Show("Invalid Column Mapping", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } if (cGrp.Text == "" && RadStudent.Checked == true) { MessageBox.Show("Please select school", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (Dgrid.RowCount < 1) { MessageBox.Show("No record to upload", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); return; } SqlTransaction myTrans = null; SqlConnection cnSQL = new SqlConnection(MyModules.strConnect); SqlCommand cmSQL = new SqlCommand(); cmSQL.Connection = cnSQL; cnSQL.Open(); if (RadStudent.Checked == true) { myTrans = cnSQL.BeginTransaction(); cmSQL.Transaction = myTrans; for (int i = 0; i < Dgrid.RowCount - 1; i++) { cmSQL.Parameters.Clear(); cmSQL.CommandText = "InsertRegisterStudent"; cmSQL.CommandType = CommandType.StoredProcedure; cmSQL.Parameters.AddWithValue("@Sn", 0); cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@State", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[10, i].Value == null) ? "" : Dgrid[10, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[9, i].Value == null) ? "" : Dgrid[9, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[11, i].Value == null) ? "" : Dgrid[11, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[12, i].Value == null) ? "" : Dgrid[12, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[13, i].Value == null) ? "" : Dgrid[13, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[14, i].Value == null) ? "" : Dgrid[14, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@InActive", false); cmSQL.Parameters.AddWithValue("@SchoolID", MyModules.GetIt4Me(cGrp.Text, " - ")); cmSQL.Parameters.AddWithValue("@Degree", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@CourseOfStudy", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@StartYear", ((Dgrid[4, i].Value == null) ? "0" : Convert.ToInt16(Dgrid[4, i].Value).ToString())); cmSQL.Parameters.AddWithValue("@EndYear", ((Dgrid[5, i].Value == null) ? "0" : Convert.ToInt16(Dgrid[5, i].Value).ToString())); cmSQL.Parameters.AddWithValue("@LGA", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@SchName", MyModules.Mid(cGrp.Text, Microsoft.VisualBasic.Strings.Len(MyModules.GetIt4Me(cGrp.Text, " - ")) + 3, -1)); cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper()); cmSQL.ExecuteNonQuery(); } } if (RadSchool.Checked == true) { myTrans = cnSQL.BeginTransaction(); cmSQL.Transaction = myTrans; for (int i = 0; i < Dgrid.RowCount - 1; i++) { cmSQL.Parameters.Clear(); cmSQL.CommandText = "InsertRegisterSchool"; cmSQL.CommandType = CommandType.StoredProcedure; cmSQL.Parameters.AddWithValue("@Sn", 0); cmSQL.Parameters.AddWithValue("@SchName", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@SchAddress", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@State", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@InActive", false); cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper()); cmSQL.ExecuteNonQuery(); } } if (RadVendor.Checked == true) { myTrans = cnSQL.BeginTransaction(); cmSQL.Transaction = myTrans; for (int i = 0; i < Dgrid.RowCount - 1; i++) { cmSQL.Parameters.Clear(); cmSQL.CommandText = "InsertRegisterVendor"; cmSQL.CommandType = CommandType.StoredProcedure; cmSQL.Parameters.AddWithValue("@Sn", 0); cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@TINNo", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Source", "VENDOR"); cmSQL.Parameters.AddWithValue("@InActive", false); cmSQL.Parameters.AddWithValue("@Category", ""); cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper()); cmSQL.ExecuteNonQuery(); } } if (radStaff.Checked == true) { myTrans = cnSQL.BeginTransaction(); cmSQL.Transaction = myTrans; for (int i = 0; i < Dgrid.RowCount - 1; i++) { cmSQL.Parameters.Clear(); cmSQL.CommandText = "InsertRegisterVendor"; cmSQL.CommandType = CommandType.StoredProcedure; cmSQL.Parameters.AddWithValue("@Sn", 0); cmSQL.Parameters.AddWithValue("@Name", ((Dgrid[0, i].Value == null) ? "" : Dgrid[0, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Address", ((Dgrid[3, i].Value == null) ? "" : Dgrid[3, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@TINNo", ((Dgrid[2, i].Value == null) ? "" : Dgrid[2, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@EmailAddress", ((Dgrid[4, i].Value == null) ? "" : Dgrid[4, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Telephone", ((Dgrid[5, i].Value == null) ? "" : Dgrid[5, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankName", ((Dgrid[6, i].Value == null) ? "" : Dgrid[6, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAcctNo", ((Dgrid[7, i].Value == null) ? "" : Dgrid[7, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankCode", ((Dgrid[8, i].Value == null) ? "" : Dgrid[8, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@BankAddress", ((Dgrid[9, i].Value == null) ? "" : Dgrid[9, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Remark", ((Dgrid["Remark", i].Value == null) ? "Uploaded" : Dgrid["Remark", i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@Source", "STAFF"); cmSQL.Parameters.AddWithValue("@InActive", false); cmSQL.Parameters.AddWithValue("@Category", ((Dgrid[1, i].Value == null) ? "" : Dgrid[1, i].Value).ToString().ToUpper()); cmSQL.Parameters.AddWithValue("@IDNo", ((Dgrid["IDNo", i].Value == null) ? "" : Dgrid["IDNo", i].Value).ToString().ToUpper()); cmSQL.ExecuteNonQuery(); // str = "SELECT [Sn] AS [RefNo],[Name]0 ,[TINNo]1,[Address]2,[EmailAddress]3 AS [Email Address] // ,[Telephone]4 ,[BankName]5,[BankAcctNo]6,[BankCode]7 AS [Bank Code],[BankAddress] 8AS [Bank Address], //[Remark],[InActive] FROM RegisterVendor ORDER BY [Name]"; } } myTrans.Commit(); if (RadStudent.Checked) { LoadRealData(" RegisterStudent.SchoolID = " + MyModules.GetIt4Me(cGrp.Text, " - ")); } else { LoadRealData(""); } MessageBox.Show("Successfull!", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { if (ex.ToString().Contains("Violation of PRIMARY KEY constraint")) { MessageBox.Show("Duplicate record exist", MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); // myTrans.Rollback(); } else { MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); } } }