private void btnMemDelete_Click(object sender, EventArgs e)
{
if (txtMemNum.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a Member Number.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemNum.Focus();
return;
}
else
{
Member objMemberNum = MemberDB.GetMember(Convert.ToInt16(txtMemNum.Text.Trim()));
if (objMemberNum == null)
{
MessageBox.Show("Member Number " + txtMemNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtMemNum.Clear();
txtMemNum.Focus();
return;
}
}
Member objMember = new Member();
objMember.MemberNumber = txtMemNum.Text.Trim();
try
{
bool status = MemberDB.DeleteMember(objMember);
if (status) //You can use this syntax as well..if (status ==true)
{
MessageBox.Show("Member deleted from the database.", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearMemberFields();
MemberListLoad();
}
else
{
MessageBox.Show("Member was not deleted from the database.", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static Member GetMember(int memNumber)
{
string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";
string memberSQL = "SELECT member_number, joindate, firstname, lastname, address, city, state," +
" zipcode, phone, member_status, password, employee FROM Member" +
" WHERE member_number = @member_number";
SqlCommand objMemCommand = null;
SqlConnection objMemConn = null;
SqlDataReader memReader = null;
Member objMember = null;
try
{
using (objMemConn = new SqlConnection(connectionString))
{
//Open the connection to the datbase
objMemConn.Open();
//Create a command object with the SQL statement
using (objMemCommand = new SqlCommand(memberSQL, objMemConn))
{
//Set command parameter
objMemCommand.Parameters.AddWithValue("@member_number", memNumber);
//Execute the SQL and return a DataReader
using ((memReader = objMemCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (memReader.Read())
{
objMember = new Member();
//Fill the customer object if found
objMember.MemberNumber = memReader["member_number"].ToString();
objMember.JoinDate = memReader["joindate"].ToString();
objMember.FirstName = memReader["firstname"].ToString();
objMember.LastName = memReader["lastname"].ToString();
objMember.Address = memReader["address"].ToString();
objMember.City = memReader["city"].ToString();
objMember.State = memReader["state"].ToString();
objMember.Zipcode = memReader["zipcode"].ToString();
objMember.Phone = memReader["phone"].ToString();
objMember.Status = memReader["member_status"].ToString();
objMember.Password = memReader["password"].ToString();
objMember.Employee = memReader["employee"].ToString();
}
}
}
return objMember;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objMemConn != null)
{
objMemConn.Close();
}
}
}
private void btnMemUpdate_Click(object sender, EventArgs e)
{
if (txtMemNum.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a Member Number.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemNum.Focus();
return;
}
else
{
Member objMemberNum = MemberDB.GetMember(Convert.ToInt16(txtMemNum.Text.Trim()));
if (objMemberNum == null)
{
MessageBox.Show("Member Number " + txtMemNum.Text.Trim() + " not found in database.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtMemNum.Clear();
txtMemNum.Focus();
return;
}
}
if (txtMemJoin.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a Join Date.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemJoin.Focus();
return;
}
if (txtMemFName.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a First Name.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemFName.Focus();
return;
}
if (txtMemLName.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a Last Name.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemLName.Focus();
return;
}
if (txtMemAddress.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter an Address.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemAddress.Focus();
return;
}
if (txtMemCity.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a City.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemCity.Focus();
return;
}
if (txtMemState.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a State.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemState.Focus();
return;
}
if (txtMemZip.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a Zipcode.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemZip.Focus();
return;
}
if (txtMemPhone.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a Phone Number.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemPhone.Focus();
return;
}
if (txtMemStatus.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter Member's Status.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemStatus.Focus();
return;
}
if (txtMemPassword.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter a Password.", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemPassword.Focus();
return;
}
if (txtMemEmploy.Text.Trim() == String.Empty)
{
MessageBox.Show("Please indicate if an Employee(1) or a Member(0).", "",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemEmploy.Focus();
return;
}
Member objMember = new Member();
objMember.MemberNumber = txtMemNum.Text.Trim();
objMember.JoinDate = txtMemJoin.Text.Trim();
objMember.FirstName = txtMemFName.Text.Trim();
objMember.LastName = txtMemLName.Text.Trim();
objMember.Address = txtMemAddress.Text.Trim();
objMember.City = txtMemCity.Text.Trim();
objMember.State = txtMemState.Text.Trim();
objMember.Zipcode = txtMemZip.Text.Trim();
objMember.Phone = txtMemPhone.Text.Trim();
objMember.Status = txtMemStatus.Text.Trim();
objMember.Password = txtMemPassword.Text.Trim();
objMember.Employee = txtMemEmploy.Text.Trim();
try
{
bool status = MemberDB.UpdateAdminMember(objMember);
if (status) //You can use this syntax as well..if (status ==true)
{
MessageBox.Show("Member updated in the database.", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearMemberFields();
MemberListLoad();
}
else
{
MessageBox.Show("Member was not updated in the database.", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static bool DeleteMember(Member objMember)
{
string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";
int rowsAffected = 0;
string memberSQL;
SqlCommand objMemCommand = null;
SqlConnection objMemConn = null;
try
{
using (objMemConn = new SqlConnection(connectionString))
{
//Open the connection to the datbase
objMemConn.Open();
memberSQL = "DELETE Member WHERE member_number = @member_number";
//Create a command object with the SQL statement
using (objMemCommand = new SqlCommand(memberSQL, objMemConn))
{
//Use the command parameters method to set the paramater values of the SQL Insert statement
objMemCommand.Parameters.AddWithValue("@member_number", objMember.MemberNumber);
//Execute the SQL and return the number of rows affected
rowsAffected = objMemCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objMemConn != null)
{
objMemConn.Close();
}
}
}
public static bool AddMember(Member objMember)
{
string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";
int rowsAffected = 0;
string memberSQL;
SqlCommand objMemCommand = null;
SqlConnection objMemConn = null;
try
{
using (objMemConn = new SqlConnection(connectionString))
{
//Open the connection to the datbase
objMemConn.Open();
memberSQL = "INSERT into Member VALUES (@member_number, @joindate, @firstname, @lastname, @address," +
" @city, @state, @zipcode, @phone, @member_status, @password, @employee)";
//Create a command object with the SQL statement
using (objMemCommand = new SqlCommand(memberSQL, objMemConn))
{
//Use the command parameters method to set the paramater values of the SQL Insert statement
objMemCommand.Parameters.AddWithValue("@member_number", Convert.ToInt16(objMember.MemberNumber));
objMemCommand.Parameters.AddWithValue("@joindate", objMember.JoinDate);
objMemCommand.Parameters.AddWithValue("@firstName", objMember.FirstName);
objMemCommand.Parameters.AddWithValue("@lastName", objMember.LastName);
objMemCommand.Parameters.AddWithValue("@address", objMember.Address);
objMemCommand.Parameters.AddWithValue("@city", objMember.City);
objMemCommand.Parameters.AddWithValue("@state", objMember.State);
objMemCommand.Parameters.AddWithValue("@zipcode", objMember.Zipcode);
objMemCommand.Parameters.AddWithValue("@phone", objMember.Phone);
objMemCommand.Parameters.AddWithValue("@member_status", objMember.Status);
objMemCommand.Parameters.AddWithValue("@password", objMember.Password);
objMemCommand.Parameters.AddWithValue("@employee", Convert.ToInt16(objMember.Employee));
//Execute the SQL and return the number of rows affected
rowsAffected = objMemCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objMemConn != null)
{
objMemConn.Close();
}
}
}
public static bool UpdateAdminMember(Member objMember)
{
string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";
int rowsAffected = 0;
string memberSQL;
SqlCommand objUpCommand = null;
SqlConnection objUpConn = null;
try
{
using (objUpConn = new SqlConnection(connectionString))
{
//Open the connection to the datbase
objUpConn.Open();
memberSQL = "UPDATE Member " + Environment.NewLine +
" SET member_number = @MemberNumber, " + Environment.NewLine +
" joindate = @Joindate, " + Environment.NewLine +
" firstname = @FirstName, " + Environment.NewLine +
" lastname = @LastName, " + Environment.NewLine +
" address = @Address, " + Environment.NewLine +
" city = @City, " + Environment.NewLine +
" state = @State, " + Environment.NewLine +
" zipcode = @Zipcode, " + Environment.NewLine +
" phone = @Phone, " + Environment.NewLine +
" member_status = @Status, " + Environment.NewLine +
" password = @Password, " + Environment.NewLine +
" employee = @Employee " + Environment.NewLine +
" WHERE member_number = @MemberNumber ";
//Create a command object with the SQL statement
using (objUpCommand = new SqlCommand(memberSQL, objUpConn))
{
//Use the command parameters method to set the paramater values of the SQL Insert statement
objUpCommand.Parameters.AddWithValue("@MemberNumber", Convert.ToInt16(objMember.MemberNumber));
objUpCommand.Parameters.AddWithValue("@Joindate", objMember.JoinDate);
objUpCommand.Parameters.AddWithValue("@FirstName", objMember.FirstName);
objUpCommand.Parameters.AddWithValue("@LastName", objMember.LastName);
objUpCommand.Parameters.AddWithValue("@Address", objMember.Address);
objUpCommand.Parameters.AddWithValue("@City", objMember.City);
objUpCommand.Parameters.AddWithValue("@State", objMember.State);
objUpCommand.Parameters.AddWithValue("@Zipcode", objMember.Zipcode);
objUpCommand.Parameters.AddWithValue("@Phone", objMember.Phone);
objUpCommand.Parameters.AddWithValue("@Status", objMember.Status);
objUpCommand.Parameters.AddWithValue("@Password", objMember.Password);
objUpCommand.Parameters.AddWithValue("@Employee", Convert.ToInt16(objMember.Employee));
//Execute the SQL and return the number of rows affected
rowsAffected = objUpCommand.ExecuteNonQuery();
}
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objUpConn != null)
{
objUpConn.Close();
}
}
}
public static List<Member> GetMember()
{
string connectionString = "Server=198.209.220.125;Database=cram;User Id=louis;Password=lou15;";
List<Member> memberList = new List<Member>();
string memberSQL = "SELECT member_number, joindate, firstname, lastname, address, city, state," +
" zipcode, phone, member_status, password, employee FROM Member";
SqlCommand objMemCommand = null;
SqlConnection objMemConn = null;
SqlDataReader memReader = null;
try
{
using (objMemConn = new SqlConnection(connectionString))
{
//Opens the connection to the database
objMemConn.Open();
//Command object created with the SQL statement
using (objMemCommand = new SqlCommand(memberSQL, objMemConn))
{
//SQL executes and returns a DataReader
using ((memReader = objMemCommand.ExecuteReader(CommandBehavior.CloseConnection)))
{
while (memReader.Read())
{
Member objMember = new Member();
objMember.MemberNumber = memReader["member_number"].ToString();
objMember.JoinDate = memReader["joindate"].ToString();
objMember.FirstName = memReader["firstname"].ToString();
objMember.LastName = memReader["lastname"].ToString();
objMember.Address = memReader["address"].ToString();
objMember.City = memReader["city"].ToString();
objMember.State = memReader["state"].ToString();
objMember.Zipcode = memReader["zipcode"].ToString();
objMember.Phone = memReader["phone"].ToString();
objMember.Status = memReader["member_status"].ToString();
objMember.Password = memReader["password"].ToString();
objMember.Employee = memReader["employee"].ToString();
//Add Member to collection
memberList.Add(objMember);
}
}
}
return memberList;
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
if (objMemConn != null)
{
objMemConn.Close();
}
}
}
private void btnMemUpdate_Click(object sender, EventArgs e)
{
if (txtMFname.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your First Name.", "First Name Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMFname.Focus();
return;
}
if (txtMLname.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your Last Name.", "Last Name Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMLname.Focus();
return;
}
if (txtMAddress.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your Street Adress.", "Address Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMAddress.Focus();
return;
}
if (txtMCity.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter the name of your city/town.", "City Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMCity.Focus();
return;
}
if (txtMState.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your State with its 2 letter designation.", "State Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMState.Focus();
return;
}
if (txtMZip.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your Zipcode.", "Zipcode Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMZip.Focus();
return;
}
if (txtMPhone.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your Phone Number.", "Phone Number Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMPhone.Focus();
return;
}
if (txtMPass.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your new Password.", "Password Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMPass.Focus();
return;
}
if (txtMemId.Text.Trim() == String.Empty)
{
MessageBox.Show("Please enter your Member ID Number.", "Member ID Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
txtMemId.Focus();
return;
}
Member objMember = new Member();
objMember.MemberNumber = txtMemId.Text.Trim();
objMember.FirstName = txtMFname.Text.Trim();
objMember.LastName = txtMLname.Text.Trim();
objMember.Address = txtMAddress.Text.Trim();
objMember.City = txtMCity.Text.Trim();
objMember.State = txtMState.Text.Trim();
objMember.Zipcode = txtMZip.Text.Trim();
objMember.Phone = txtMPhone.Text.Trim();
objMember.Password = txtMPass.Text.Trim();
try
{
bool status = MemberDB.UpdateMemMember(objMember);
if (status) //You can use this syntax as well..if (status ==true)
{
MessageBox.Show("Member information updated in the database.", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
MemClear();
}
else
{
MessageBox.Show("Member was not updated in the database.", "",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}