public string activateMember(Member member) { sqlConn.ConnectionString = conn; DataTable dt = new DataTable(); dt.Clear(); SqlCommand cmd = new SqlCommand(string.Format( @"UPDATE MEMBER SET STATUS=@STATUS, DATE_LAST_ACTIVATED=@DATE_LAST_ACTIVATED WHERE ID_MEMBER=@ID_MEMBER"), sqlConn); cmd.Parameters.AddWithValue("@ID_MEMBER", member.id); cmd.Parameters.AddWithValue("@STATUS", member.status); cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated); try { sqlConn.Open(); cmd.ExecuteNonQuery(); return "The Member has been activated sucessfully."; } catch (Exception e) { return e.Message.ToString(); } finally { sqlConn.Close(); } }
protected void removeMember(object sender, EventArgs e) { if (this.datepicker.Text.Equals("")) { this.errorDiv.Visible = true; this.errorMsg.Text = "Please select a date."; } else { Member member = new Member(); member.id = this.searchMembers.SelectedRow.Cells[6].Text; member.status = "INACTIVE"; member.dateRemoved = this.datepicker.Text; MemberDAO memberDAO = new MemberDAO(); this.removeMemberForm.Visible = false; this.responseForm.Visible = true; this.responseText.Text = memberDAO.removeMember(member); } }
protected void activateMember(object sender, EventArgs e) { if(this.datepicker.Text.Equals("")){ this.errorDiv.Visible = true; this.errorMsg.Text = "Please select the date of activation!"; } else { Member member = new Member(); MemberDAO memberDAO = new MemberDAO(); member.id = this.searchMembers.SelectedRow.Cells[6].Text; member.dateLastActivated = this.datepicker.Text; member.status = "ACTIVE"; this.mainForm.Visible = false; this.response.Visible = true; this.responseText.Text = memberDAO.activateMember(member); } }
public Member getMemberData(string idMember) { Member member = new Member(); sqlConn.ConnectionString = conn; DataTable dt = new DataTable(); dt.Clear(); //SELECT SQL, using the ID passed as an argument SqlCommand cmd = new SqlCommand(string.Format(@"SELECT * FROM MEMBER WHERE ID_MEMBER=@ID_MEMBER"), sqlConn); cmd.Parameters.AddWithValue("@ID_MEMBER", idMember); try { sqlConn.Open(); SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); //Sets the member's parameters using the reader.. member.id = reader.GetValue(0).ToString(); member.idGroup = reader.GetValue(1).ToString(); member.accountNumber = reader.GetValue(2).ToString(); member.name = reader.GetValue(3).ToString(); member.dateJoined = reader.GetValue(4).ToString(); member.status = reader.GetValue(5).ToString(); member.doctor = reader.GetValue(6).ToString(); member.address = reader.GetValue(7).ToString(); member.city = reader.GetValue(8).ToString(); member.province = reader.GetValue(9).ToString(); member.region = reader.GetValue(10).ToString(); member.postalCode = reader.GetValue(11).ToString(); member.website = reader.GetValue(12).ToString(); member.emailAddress = reader.GetValue(13).ToString(); member.phoneNumber = reader.GetValue(14).ToString(); member.faxNumber = reader.GetValue(15).ToString(); member.contactPerson = reader.GetValue(16).ToString(); reader.Close(); } catch (Exception) { //MessageBox.Show("Some error has occured. Sorry =("); } finally { sqlConn.Close(); } return member; }
public string updateMember(Member member) { sqlConn.ConnectionString = conn; DataTable dt = new DataTable(); dt.Clear(); if (member.idGroup == null || member.idGroup.Equals("") || member.idGroup.Equals(" ")) { SqlCommand cmd = new SqlCommand(string.Format(@"UPDATE MEMBER SET ACCOUNT_NUMBER=@ACCOUNT_NUMBER, NAME=@NAME, DOCTOR=@DOCTOR, DATE_JOINED=@DATE_JOINED, DATE_LAST_ACTIVATED=@DATE_LAST_ACTIVATED, ADDRESS=@ADDRESS, CITY=@CITY, PROVINCE=@PROVINCE, REGION=@REGION, POSTAL_CODE=@POSTAL_CODE, WEBSITE=@WEBSITE, EMAIL_ADDRESS=@EMAIL_ADDRESS, PHONE_NUMBER=@PHONE_NUMBER, FAX=@FAX, CONTACT_PERSON=@CONTACT_PERSON WHERE ID_MEMBER=@ID_MEMBER"), sqlConn); cmd.Parameters.AddWithValue("@ID_MEMBER", member.id); cmd.Parameters.AddWithValue("@ACCOUNT_NUMBER", member.accountNumber); cmd.Parameters.AddWithValue("@NAME", member.name); cmd.Parameters.AddWithValue("@DOCTOR", member.doctor); cmd.Parameters.AddWithValue("@DATE_JOINED", member.dateJoined); cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated); cmd.Parameters.AddWithValue("@ADDRESS", member.address); cmd.Parameters.AddWithValue("@CITY", member.city); cmd.Parameters.AddWithValue("@PROVINCE", member.province); cmd.Parameters.AddWithValue("@REGION", member.region); cmd.Parameters.AddWithValue("@POSTAL_CODE", member.postalCode); cmd.Parameters.AddWithValue("@WEBSITE", member.website); cmd.Parameters.AddWithValue("@EMAIL_ADDRESS", member.emailAddress); cmd.Parameters.AddWithValue("@PHONE_NUMBER", member.phoneNumber); cmd.Parameters.AddWithValue("@FAX", member.faxNumber); cmd.Parameters.AddWithValue("@CONTACT_PERSON", member.contactPerson); try { sqlConn.Open(); cmd.ExecuteNonQuery(); return "The Member has been updated sucessfully."; } catch (Exception e) { return e.Message.ToString(); } finally { sqlConn.Close(); } } else { SqlCommand cmd = new SqlCommand(string.Format(@"UPDATE MEMBER SET ID_GROUP=@ID_GROUP, ACCOUNT_NUMBER=@ACCOUNT_NUMBER, NAME=@NAME, DOCTOR=@DOCTOR, DATE_JOINED=@DATE_JOINED, DATE_LAST_ACTIVATED=@DATE_LAST_ACTIVATED, ADDRESS=@ADDRESS, CITY=@CITY, PROVINCE=@PROVINCE, REGION=@REGION, POSTAL_CODE=@POSTAL_CODE, WEBSITE=@WEBSITE, EMAIL_ADDRESS=@EMAIL_ADDRESS, PHONE_NUMBER=@PHONE_NUMBER, FAX=@FAX, CONTACT_PERSON=@CONTACT_PERSON WHERE ID_MEMBER=@ID_MEMBER"), sqlConn); cmd.Parameters.AddWithValue("@ID_MEMBER", member.id); cmd.Parameters.AddWithValue("@ID_GROUP", member.idGroup); cmd.Parameters.AddWithValue("@ACCOUNT_NUMBER", member.accountNumber); cmd.Parameters.AddWithValue("@NAME", member.name); cmd.Parameters.AddWithValue("@DOCTOR", member.doctor); cmd.Parameters.AddWithValue("@DATE_JOINED", member.dateJoined); cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated); cmd.Parameters.AddWithValue("@ADDRESS", member.address); cmd.Parameters.AddWithValue("@CITY", member.city); cmd.Parameters.AddWithValue("@PROVINCE", member.province); cmd.Parameters.AddWithValue("@REGION", member.region); cmd.Parameters.AddWithValue("@POSTAL_CODE", member.postalCode); cmd.Parameters.AddWithValue("@WEBSITE", member.website); cmd.Parameters.AddWithValue("@EMAIL_ADDRESS", member.emailAddress); cmd.Parameters.AddWithValue("@PHONE_NUMBER", member.phoneNumber); cmd.Parameters.AddWithValue("@FAX", member.faxNumber); cmd.Parameters.AddWithValue("@CONTACT_PERSON", member.contactPerson); try { sqlConn.Open(); cmd.ExecuteNonQuery(); return "The Member has been updated sucessfully."; } catch (Exception e) { return e.Message.ToString(); } finally { sqlConn.Close(); } } }
public string removeMember(Member member) { sqlConn.ConnectionString = conn; DataTable dt = new DataTable(); dt.Clear(); string inactive = "INACTIVE"; SqlCommand cmd = new SqlCommand(string.Format( @"UPDATE MEMBER SET STATUS=@STATUS, DATE_REMOVED=@DATE_REMOVED WHERE ID_MEMBER=@ID_MEMBER"), sqlConn); cmd.Parameters.AddWithValue("@ID_MEMBER", member.id); cmd.Parameters.AddWithValue("@STATUS", inactive); cmd.Parameters.AddWithValue("@DATE_REMOVED", member.dateRemoved); try { sqlConn.Open(); cmd.ExecuteNonQuery(); return "The Member has been removed sucessfully from the list of Active members."; } catch (Exception e) { return e.Message.ToString(); } finally { sqlConn.Close(); } }
public string insertNewMember(Member member) { sqlConn.ConnectionString = conn; DataTable dt = new DataTable(); dt.Clear(); SqlCommand cmd = new SqlCommand(string.Format(@"INSERT INTO MEMBER (ACCOUNT_NUMBER, NAME, DATE_JOINED, STATUS, DOCTOR, ADDRESS, CITY, PROVINCE, REGION, POSTAL_CODE, WEBSITE, EMAIL_ADDRESS, PHONE_NUMBER, FAX, CONTACT_PERSON, DATE_LAST_ACTIVATED, DATE_MODIFIED, MODIFIED_BY, DATE_CREATED) VALUES (@ACCOUNT_NUMBER, @NAME, @DATE_JOINED, @STATUS, @DOCTOR, @ADDRESS, @CITY, @PROVINCE, @REGION, @POSTAL_CODE, @WEBSITE, @EMAIL_ADDRESS, @PHONE_NUMBER, @FAX, @CONTACT_PERSON, @DATE_LAST_ACTIVATED, @DATE_MODIFIED, @MODIFIED_BY, @DATE_CREATED)"), sqlConn); cmd.Parameters.AddWithValue("@ACCOUNT_NUMBER", member.accountNumber); cmd.Parameters.AddWithValue("@NAME", member.name); cmd.Parameters.AddWithValue("@DATE_JOINED", member.dateJoined); cmd.Parameters.AddWithValue("@STATUS", "ACTIVE"); cmd.Parameters.AddWithValue("@DOCTOR", member.doctor); cmd.Parameters.AddWithValue("@ADDRESS", member.address); cmd.Parameters.AddWithValue("@CITY", member.city); cmd.Parameters.AddWithValue("@PROVINCE", member.province); cmd.Parameters.AddWithValue("@REGION", member.region); cmd.Parameters.AddWithValue("@POSTAL_CODE", member.postalCode); cmd.Parameters.AddWithValue("@WEBSITE", member.website); cmd.Parameters.AddWithValue("@EMAIL_ADDRESS", member.emailAddress); cmd.Parameters.AddWithValue("@PHONE_NUMBER", member.phoneNumber); cmd.Parameters.AddWithValue("@FAX", member.faxNumber); cmd.Parameters.AddWithValue("@CONTACT_PERSON", member.contactPerson); cmd.Parameters.AddWithValue("@DATE_LAST_ACTIVATED", member.dateLastActivated); cmd.Parameters.AddWithValue("@DATE_MODIFIED", member.dateModified); cmd.Parameters.AddWithValue("@MODIFIED_BY", member.modifiedBy); cmd.Parameters.AddWithValue("@DATE_CREATED", member.dateCreated); try { sqlConn.Open(); cmd.ExecuteNonQuery(); return "The Member has been added to the Database sucessfully."; } catch (Exception e) { return e.Message.ToString(); } finally { sqlConn.Close(); } }
protected void updateMember(object sender, EventArgs e) { this.enableFieldsBtt.Visible = false; Member member = new Member(); string databaseResponse = ""; MemberDAO memberDAO = new MemberDAO(); if (this.searchMembers.SelectedIndex == -1) { this.errorMsg.Text = "Select one member to be updated."; this.errorDiv.Visible = true; } else { if (accountNumber.Text.ToString() == "" || memberName.Text.ToString() == "" || this.datepicker.Text.Equals("") || address.Text.ToString() == "" || city.Text.ToString() == "" || province.SelectedValue.ToString() == "Select..." || postalCode.Text.ToString() == "") { this.errorMsg.Text = "One or more of the required fields are blank!"; this.errorDiv.Visible = true; } else { if (memberDAO.alreadyExists(this.accountNumber.Text)) { this.errorMsg.Text = "This account number already exists. Please choose another one."; } else { //Loads the form content into the member object member.id = this.searchMembers.SelectedRow.Cells[6].Text; member.idGroup = this.searchMembers.SelectedRow.Cells[7].Text; member.accountNumber = accountNumber.Text.ToString().Trim(); member.dateJoined = this.datepicker.Text; member.doctor = doctorName.Text.ToString().Trim(); member.name = memberName.Text.ToString().Trim(); member.address = address.Text.ToString().Trim(); member.city = city.Text.ToString().Trim(); member.province = province.SelectedItem.ToString(); member.region = region.Text.ToString(); member.postalCode = postalCode.Text.ToString(); member.website = website.Text.ToString().Trim(); member.emailAddress = emailAddress.Text.ToString().Trim(); member.phoneNumber = phoneNumber.Text.ToString(); member.faxNumber = faxNumber.Text.ToString(); member.contactPerson = contactPerson.Text.ToString().Trim(); member.dateLastActivated = this.datepicker.Text; //Sends the member object to the updateMember method in MemberDAO.. databaseResponse = memberDAO.updateMember(member); clearAllFields(); this.responseText.Text = databaseResponse; this.editMemberForm.Visible = false; this.response.Visible = true; } } } }