public void loadDepartments() { SqlConnection conn = ConnectDB.GetConnection(); string selectDept = "Select DepartmentID, DepartmentName,Description from Departments"; SqlCommand cmd = new SqlCommand(selectDept, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Departments"); DataTable dt = ds.Tables["Departments"]; dtGrdDepartment.DataSource = dt; dtGrdDepartment.Columns["DepartmentID"].Visible = false; dtGrdDepartment.Columns[1].HeaderText = "Department Name"; conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message + " From Load Departments Function"); } finally { conn.Close(); } }
private void ViewDepartments_Load(object sender, EventArgs e) { SqlConnection conn = ConnectDB.GetConnection(); string strSelectDept = "Select DepartmentName, Description from Departments"; SqlCommand cmdDept = new SqlCommand(strSelectDept, conn); SqlDataAdapter da = new SqlDataAdapter(cmdDept); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Departments"); DataTable dt = ds.Tables["Departments"]; dtGrdDept.DataSource = dt; lblTotalRecord.Text = "No. of Records: " + dt.Rows.Count; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadMembersIntoList() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "select MemberID,FileNo,Title + ' ' + LastName + ' ' + MiddleName + ' ' + FirstName as FullName from Members order by FileNo"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); int counter = 0; lstMembers.Items.Clear(); while (reader.Read()) { lstMembers.Items.Add(reader["FileNo"] + " - " + reader["FullName"]); counter++; } lblRecordNo.Text = "No. of Records : " + counter; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadStates() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select StateID, State from States order by State"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "States"); DataTable dt = ds.Tables["States"]; DataRow row = dt.NewRow(); row["State"] = "-- Select State --"; dt.Rows.InsertAt(row, 0); cboState.DisplayMember = "State"; cboState.ValueMember = "StateID"; cboState.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadSavingsType() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select SavingsTypeID,SavingsName, Description from SavingsType order by SavingsName"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "SavingsType"); DataTable dt = ds.Tables["SavingsType"]; cboOtherSavings.DisplayMember = "SavingsName"; cboOtherSavings.ValueMember = "SavingsTypeID"; DataRow row = dt.NewRow(); row["SavingsName"] = "-- Add Savings Type --"; dt.Rows.InsertAt(row, 0); cboOtherSavings.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadMemberFileNoIntoCboFileNo() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select MemberID, FileNo from Members order by FileNo"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "FileNo"); DataTable dt = ds.Tables["FileNo"]; DataRow row = dt.NewRow(); row["FileNo"] = "-- Select a File No. --"; dt.Rows.InsertAt(row, 0); cboFileNo.DisplayMember = "FileNo"; cboFileNo.ValueMember = "MemberID"; cboFileNo.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadBanks() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select BankName, Description from Banks"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); banks = new Hashtable(); SqlDataReader reader = cmd.ExecuteReader(); lstBank.Items.Clear(); int countRecords = 0; while (reader.Read()) { banks.Add(reader["BankName"].ToString(), reader["Description"].ToString()); lstBank.Items.Add(reader["BankName"].ToString()); countRecords++; } lblRecordNo.Text = "No. of Records: " + countRecords; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void btnSearch_Click(object sender, EventArgs e) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select SavingsName, Description from SavingsType where SavingsName LIKE '%" + txtSearch.Text + "%'"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); lstSavings.Items.Clear(); savings = new Hashtable(); int countRecords = 0; while (reader.Read()) { lstSavings.Items.Add(reader["SavingsName"].ToString()); savings.Add(reader["SavingsName"].ToString(), reader["Description"].ToString()); countRecords++; } lblRecord.Text = "No. of Records: " + countRecords; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void performEditFunc() { string selSavings = lstSavings.SelectedItem.ToString(); SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Update SavingsType set SavingsName=@Name, Description=@Description where SavingsName=@selSavings"; SqlCommand cmd = new SqlCommand(strQuery, conn); cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50, "SavingsName"); cmd.Parameters["@Name"].Value = txtName.Text.Trim(); cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 200, "Description"); cmd.Parameters["@Description"].Value = txtDescription.Text.Trim(); cmd.Parameters.Add("@selSavings", SqlDbType.NVarChar, 50); cmd.Parameters["@selsavings"].Value = selSavings; try { conn.Open(); int rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected > 0) { MessageBox.Show("Savings Item '" + selSavings + "' has been successfully updated", "Edit Savings Type", MessageBoxButtons.OK, MessageBoxIcon.Information); btnCancel_Click(null, null); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void memberPersonalProfile(string memberID) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select m.MemberID 'ID', m.FileNo as [File No.], m.Title + ' ' + m.LastName + ' ' + m.FirstName + ' ' + m.MiddleName as 'Full Name', " + "m.Gender,m.Address, m.LGA, s.State, m.Phone, m.Email, d.DepartmentName [Department], b.BankName [Bank], m.AccountNo [Account], m.NOKFullName [Next of Kin]," + "m.NOKPhone [Kin Phone], m.NOKAddress [NOK Addr.] from Members m " + "left join States s on m.StateID = s.StateID " + "left join Departments d on m.DepartmentID=d.DepartmentID " + "left join Banks b on m.BankID=b.BankID " + "where m.MemberID='" + memberID + "'"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Members"); DataTable dt = ds.Tables["Members"]; dtGrdPersonalProfile.DataSource = dt; dtGrdPersonalProfile.Columns["ID"].Width = 60; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadSavingsRecords() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select SavingsName,Description from SavingsType"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "SavingsType"); conn.Close(); DataTable dt = ds.Tables["SavingsType"]; grdSavingsType.DataSource = dt; grdSavingsType.Columns[0].HeaderText = "Savings Name"; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void getSelectedDetailsTotalSavings(string strFilter) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select SUM(Amount) as Total from Savings " + strFilter; SqlCommand cmd = new SqlCommand(strQuery, conn); //MessageBox.Show(strQuery); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { if (reader.Read()) { lblSelectedSavingsTotal.Text = "Total Savings: " + CheckForNumber.formatCurrency2(reader["Total"].ToString()); } } } catch (Exception ex) { MessageBox.Show("Total Savings: " + ex.Message); } finally { conn.Close(); } }
private void ShowActivityLog_Load(object sender, EventArgs e) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select l.ActivityLogID as [ActivityLog ID],l.UserID,u.LastName + ' ' + u.FirstName as [FullName],u.Role,l.ActivityType,l.Description " + "from ActivityLog l left join Users u"; SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "ActivityLog"); DataTable dt = ds.Tables["ActivityLog"]; datGrdViewActivityLog.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadCreatedLoanTypes() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select c.Name as [Loan Category], t.Type, t.Duration as [Duration (Months)], t.InterestRate as [Interest Rate (%)], t.Description,t.DateCreated as [Date Created] " + "from LoanType t left join LoanCategory c on c.LoanCategoryID=t.LoanCategoryID"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "LoanType"); DataTable dt = ds.Tables["LoanType"]; grdLoanType.DataSource = dt; lblRecord.Text = "No. of Records: " + dt.Rows.Count; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void getPreviousDeductionDate() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select d.DeductionID, m.Month + ' ' + d.Year as DeductionMonthYear, d.DatePosted from Deductions d left join MonthByName m" + " on d.Month=m.MonthID order by DeductionID desc"; SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult); if (reader.HasRows) { reader.Read(); lblPreviousPosting.Text = "Previous Deductions: " + reader["DeductionMonthYear"].ToString() + Environment.NewLine + "Posted " + reader["DatePosted"].ToString(); } else { lblPreviousPosting.Text = "No Previous Deductions"; } reader.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void getSavingStatus() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select SUM(Amount) as TotalSavings from Savings where MemberID=" + memberID; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); lblFinancialStatus.Text = "Savings: " + CheckForNumber.formatCurrency2(reader["TotalSavings"].ToString()); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } lblFinancialStatus.Visible = true; }
private void getDeductionList() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select m.Month, d.Year from DeductionDates d left join MonthByName m " + "on m.MonthID=d.Month order by DeductionDateID"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { lstVwDeductionList.Items.Clear(); while (reader.Read()) { String[] row = { reader["Month"].ToString(), reader["Year"].ToString() }; ListViewItem item = new ListViewItem(row); lstVwDeductionList.Items.Add(item); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void removeSelectedSavingsType() { SqlConnection conn = ConnectDB.GetConnection(); string selSavingsAcctID = lstVSavings.SelectedItems[0].SubItems[0].Text; string selSavingsName = lstVSavings.SelectedItems[0].SubItems[1].Text; string strQuery = "Delete from MemberSavingsTypeAcct where SavingsAcctID=@SavingsAcctID"; SqlCommand cmd = new SqlCommand(strQuery, conn); cmd.Parameters.Add("@SavingsAcctID", System.Data.SqlDbType.Int); cmd.Parameters["@SavingsAcctID"].Value = Convert.ToInt16(selSavingsAcctID); try { conn.Open(); int rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected > 0) { MessageBox.Show("Savings Type [" + selSavingsName + "] has been Successfully Removed\n from Member's Account.", "SetUp Member Savings", MessageBoxButtons.OK, MessageBoxIcon.Information); loadMemberRegisteredSavingsType(); } else { MessageBox.Show("An Error Occurred Removing [" + selSavingsName + "] Savings Type from Member's Account", "SetUp Member Savings", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void btnSearch_Click(object sender, EventArgs e) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select BankName, Description from Banks where BankName LIKE '%" + txtSearch.Text + "%'"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); banks = new Hashtable(); int countRecords = 0; lstBank.Items.Clear(); while (reader.Read()) { banks.Add(reader["BankName"].ToString(), reader["Description"].ToString()); string[] row = { reader["BankName"].ToString() }; ListViewItem item = new ListViewItem(row); lstBank.Items.Add(item); countRecords++; } lblRecordNo.Text = "No. of Records: " + countRecords; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void DeleteSelectedAppRecord(string applicationID) { SqlConnection conn = ConnectDB.GetConnection(); string strDelete = "Delete from LoanApplication where LoanApplicationID=" + applicationID; SqlCommand cmd = new SqlCommand(strDelete, conn); try { conn.Open(); int rowAffected = cmd.ExecuteNonQuery(); if (rowAffected > 0) { //MessageBox.Show("The Selected Record has been Deleted", "Loan Application", MessageBoxButtons.OK, MessageBoxIcon.Information); strFilterMonth = string.Empty; strFilterYear = string.Empty; loadAllLoanApplicationRecords(strFilterMonth, strFilterYear); string userId = this.MdiParent.Controls["lblLoggedInUserID"].Text; ActivityLog.logActivity(userId, "Delete Loan Application", "Loan Application with ID - " + applicationID + " was deleted."); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadCategory() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select LoanCategoryID, Name from LoanCategory"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "LoanCategory"); DataTable dt = ds.Tables["LoanCategory"]; cboCategory.DisplayMember = "Name"; cboCategory.ValueMember = "LoanCategoryID"; DataRow row = dt.NewRow(); row["Name"] = ""; dt.Rows.InsertAt(row, 0); cboCategory.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadBankList() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select BankName, Description from Banks"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); lstBank.Items.Clear(); while (reader.Read()) { string[] row = { reader["BankName"].ToString(), reader["Description"].ToString() }; ListViewItem item = new ListViewItem(row); lstBank.Items.Add(item); } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void loadDepartments() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select DepartmentID, DepartmentName, Description from Departments order by DepartmentName"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Departments"); DataTable dt = ds.Tables["Departments"]; cboDepartment.DisplayMember = "DepartmentName"; cboDepartment.ValueMember = "DepartmentID"; DataRow row = dt.NewRow(); row["DepartmentName"] = "-- Select Department --"; dt.Rows.InsertAt(row, 0); cboDepartment.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private bool bankNameAlreadyExist() { int rowsCount = 0; SqlConnection conn = ConnectDB.GetConnection(); string bankName = txtName.Text; bankName = bankName.Trim(); string strQuery = "Select count(*) from Banks where BankName='" + bankName + "'"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); rowsCount = (int)cmd.ExecuteScalar(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } if (rowsCount > 0) { return(true); } else { return(false); } }
//load Departments private void loadDepartments() { deptDetails = new Hashtable(); SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select DepartmentName, Description from Departments"; SqlCommand cmd = new SqlCommand(strQuery, conn); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); int countRecords = 0; lstDepartments.Items.Clear(); if (reader.HasRows) { while (reader.Read()) { deptDetails.Add(reader["DepartmentName"].ToString(), reader["Description"].ToString()); lstDepartments.Items.Add(reader["DepartmentName"].ToString()); countRecords++; } } lblRecordNumber.Text = "No of Records: " + countRecords; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void getDeductionMonthIntoCombo() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select distinct(d.Month) as id, m.Month as Mon from Deductions d " + "inner join MonthByName m on d.Month=m.MonthID order by id asc"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Month"); DataTable dt = ds.Tables["Month"]; DataRow row = dt.NewRow(); row["Mon"] = "--Select Month--"; dt.Rows.InsertAt(row, 0); cboMonth.DisplayMember = "Mon"; cboMonth.ValueMember = "id"; cboMonth.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
public static void logActivity(string UserID, string ActivityType, string Description) { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Insert into ActivityLog(UserID,ActivityType,Description)" + "Values(@UserID,@ActivityType,@Description)"; SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = strQuery; #region cmd parameters cmd.Parameters.Add("@UserID", SqlDbType.Int); cmd.Parameters["@UserID"].Value = Convert.ToInt16(UserID); cmd.Parameters.Add("@ActivityType", SqlDbType.NVarChar, 50); cmd.Parameters["@ActivityType"].Value = ActivityType.ToString(); cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 600); cmd.Parameters["@Description"].Value = Description.ToString(); #endregion try { conn.Open(); int rowsAffected = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw (ex); } finally { conn.Close(); } }
private void getDeductionYearIntoCombo() { SqlConnection conn = ConnectDB.GetConnection(); string strQuery = "Select distinct(Year) as yearField from Deductions"; SqlCommand cmd = new SqlCommand(strQuery, conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds, "Year"); DataTable dt = ds.Tables["Year"]; cboYear.DisplayMember = "yearField"; cboYear.ValueMember = "yearField"; cboYear.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void lstMembers_SelectedIndexChanged(object sender, EventArgs e) { if (grpMemberInfo.Visible == false) { grpMemberInfo.Visible = true; } //Clear lstVSavingsType of Existing Information lstVSavings.Items.Clear(); int memberID = 0; string recordIdentifier = lstMembers.SelectedItem.ToString(); string selectedRecordFileNo = recordIdentifier.Substring(0, recordIdentifier.IndexOf("-")).Trim(); SqlConnection conn = ConnectDB.GetConnection(); //Get Member Profile Info memberID = fetchMemberProfileData(memberID, selectedRecordFileNo, conn); //Get Member Shares fetchMemberSharesInfo(memberID, conn); fetchMemberSavingsAcct(memberID, conn); }
private bool checkDeptExist(string departmentName) { int deptAlreadyExist = 0; SqlConnection conn = ConnectDB.GetConnection(); string checkDept = "Select count(*) from Departments where DepartmentName=@DepartmentName"; SqlCommand cmd = new SqlCommand(checkDept, conn); cmd.Parameters.Add("@DepartmentName", SqlDbType.NVarChar, 100); cmd.Parameters["@DepartmentName"].Value = departmentName; try{ conn.Open(); deptAlreadyExist = Convert.ToInt16(cmd.ExecuteScalar().ToString()); }catch (Exception ex) { MessageBox.Show(ex.Message + "from CheckDeptExist function"); }finally{ conn.Close(); } if (deptAlreadyExist > 0) { return(true); } else { return(false); } }