//On change of index in the list box it will querey the report viers
// gets assets and populates list box with First and last name
private void getAssets()
{
if (assetsListBox.Items.Count > 0)
{
assetsListBox.Items.Clear();
}
try
{
sqlConnect newConn = new sqlConnect();
SqlConnection conn = new SqlConnection(newConn.connection());
conn.Open();
string sqlStatement = "select first_name,last_name,misc_name from users";
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlStatement, conn);
DataSet DS1 = new DataSet();
sqlAdapter.Fill(DS1, "tag");
foreach (DataRow row in DS1.Tables[0].Rows)
{
if (row[0].ToString() != "" && row[1].ToString() != "")
{
assetsListBox.Items.Add(row[0].ToString() + " " + row[1].ToString());
}
if (row[2].ToString() != "")
{
assetsListBox.Items.Add(row[2].ToString());
}
}
conn.Close();
}
catch (SqlException exx)
{
MessageBox.Show("Error {0}: " + exx);
}
}
private void realtimesearchassets()
{
sqlConnect newConn = new sqlConnect();
SqlConnection conn = new SqlConnection(newConn.connection());
conn.Open();
try
{
string sqlStatement = null;
assetsListBox.Items.Clear();
if (firstnameRB.Checked == true)
{
sqlStatement = @"SELECT users.first_name, users.last_name, users.misc_name, asset.tag
FROM asset INNER JOIN
asset_users ON asset.tag = asset_users.tag INNER JOIN
users ON asset_users.uid = users.uid
WHERE (users.first_name LIKE '%" + assetsearchtb.Text + "%')";
}
if (lastnameRB.Checked == true)
{
sqlStatement = @"SELECT users.first_name, users.last_name, users.misc_name, asset.tag
FROM asset INNER JOIN
asset_users ON asset.tag = asset_users.tag INNER JOIN
users ON asset_users.uid = users.uid
WHERE (users.last_name LIKE '%" + assetsearchtb.Text + "%')";
}
if (miscnamerb.Checked == true)
{
sqlStatement = @"SELECT users.first_name, users.last_name, users.misc_name, asset.tag
FROM asset INNER JOIN
asset_users ON asset.tag = asset_users.tag INNER JOIN
users ON asset_users.uid = users.uid
WHERE (users.misc_name LIKE '%" + assetsearchtb.Text + "%')";
}
if (serialrb.Checked == true)
{
sqlStatement = @"SELECT users.first_name, users.last_name, users.misc_name,asset.serial
FROM asset JOIN
asset_users ON asset.tag = asset_users.tag INNER JOIN
users ON asset_users.uid = users.uid
WHERE (asset.serial LIKE '%" + assetsearchtb.Text + "%')";
}
DataSet DS2 = new DataSet();
SqlDataAdapter sqlAdapter2 = new SqlDataAdapter(sqlStatement, conn);
sqlAdapter2.Fill(DS2, "tag");
assetsListBox.Items.Clear();
foreach (DataRow row in DS2.Tables[0].Rows)
{
if (serialrb.Checked == true)
{
if (row[0].ToString() != DBNull.Value.ToString())
{
assetsListBox.Items.Add(row[0].ToString() + " " + row[1].ToString());
}
if (row[2].ToString() != DBNull.Value.ToString())
{
assetsListBox.Items.Add(row[2].ToString());
}
}
if(firstnameRB.Checked == true)
{
assetsListBox.Items.Add(row[0].ToString() + " " + row[1].ToString());
}
if (lastnameRB.Checked == true)
{
assetsListBox.Items.Add(row[1].ToString() + " " + row[0].ToString());
}
if (miscnamerb.Checked == true)
{
assetsListBox.Items.Add(row[2].ToString());
}
}
conn.Close();
}
catch (SqlException MySQLEx)
{
MessageBox.Show("Error {0}:" + MySQLEx);
}
}
// real time search when possibly
private void searchAssetsListTB_TextChanged(object sender, EventArgs e)
{
try
{
sqlConnect newConn = new sqlConnect();
MySqlConnection conn = new MySqlConnection(newConn.connection());
conn.Open();
string sqlStatement = null;
if (firstnameRB.Checked == true)
{
sqlStatement = "select first_name,last_name from list where first_name like'" + assetsearchtb.Text + "%' order by first_name";
}
else if (serialRB.Checked == true)
{
sqlStatement = "select first_name,last_name from list where serial like '%" + assetsearchtb.Text + "%' order by first_name";
}
else if (lastnameRB.Checked == true)
{
sqlStatement = "select first_name,last_name from list where last_name like'" + assetsearchtb.Text + "%' order by last_name";
}
DataSet DS2 = new DataSet();
MySqlDataAdapter sqlAdapter2 = new MySqlDataAdapter(sqlStatement, conn);
sqlAdapter2.Fill(DS2, "tag");
assetsListBox.Items.Clear();
foreach (DataRow row in DS2.Tables[0].Rows)
{
if (assetsearchtb.Text != null)
{
if (firstnameRB.Checked == true || serialRB.Checked == true)
{
assetsListBox.Items.Add(row[0].ToString() + " " + row[1].ToString());
}
else if (lastnameRB.Checked == true)
{
assetsListBox.Items.Add(row[1].ToString() + " " + row[0].ToString());
}
}
else if (lastnameRB.Checked == true)
{
assetsListBox.Items.Clear();
getAssets();
}
else
{
assetsListBox.Items.Clear();
getAssets();
}
}
conn.Close();
}
catch (MySqlException MySQLEx)
{
MessageBox.Show("Error {0}:" + MySQLEx);
}
}
private void virustab_Click(object sender, EventArgs e)
{
sqlConnect newConn = new sqlConnect();
//connect to the MySQL database
MySqlConnection conn = new MySqlConnection(newConn.connection());
string sqlstatement = "select occurance from viruses where occurance > 0 and first_name='"+firstNameTB.Text+"'";
MySqlCommand cmd = new MySqlCommand(sqlstatement, conn);
try
{
conn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read());
{
if (Convert.ToInt32(reader[0]) > 0)
{
// viruslbl.Text="This user has been infected " +reader[0]+ " times!";
// noviruslbl.Hide();
}
else
{
// noviruslbl.Text = "no viruses for this user";
}
}
}
catch (MySqlException virusError)
{
MessageBox.Show("Error {0}: " +virusError);
}
}
private void saveBTN_Click(object sender, EventArgs e)
{
if (firstNameTB.Text != null)
{
string tagnum = tagAltlbl.Text;
string fname = firstNameTB.Text;
string lname = lastNameTB.Text;
string make = makeAltTB.Text;
string model = modelAltTB.Text;
string serial = serialAltTB.Text;
string dos = dosAltTB.Text;
string key = keyAltTB.Text;
string pcname = pcNameAltTB.Text;
string formfactor = editformfactortb.Text;
string price = editpricetb.Text;
string location = editlocationcb.Text;
string department = editdepartmentcb.Text;
string costcenter = editcostcentertb.Text;
sqlConnect newConn = new sqlConnect();
MySqlConnection conn = new MySqlConnection(newConn.connection());
string sqlStatement = "UPDATE list SET first_name='" + fname + "',last_name='" + lname + "',make='" + make + "',model='" + model + "',serial='" + serial + "',date_in_service='" + setEditDate(dos) + "',productkey='" + key + "',computer_name='" + pcname + "',form_factor='" + formfactor + "',price='" + price + "',location='" + location + "',department='" + department + "',cost_center='" + costcenter + "' where tag='" + Convert.ToInt32(tagnum) + "'";
MySqlCommand cmd = new MySqlCommand(sqlStatement, conn);
if (agreecheckbox.Checked == true)
{
try
{
conn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Successfully updated asset: " + fname + " " + lname);
getAssets();
agreecheckbox.Checked = false;
}
catch (MySqlException alterE)
{
MessageBox.Show("Error {0}: " + alterE);
}
}
else
{
MessageBox.Show("Please agree to the changes");
}
}
else
{
MessageBox.Show("Please select an asset from the list!");
}
}
// adds and asset to the database then will clear all text boxes
private void addAsset()
{
string tagNum = tagNumTB.Text;
string fname = fNameTB.Text;
string lname = lNameTB.Text;
string make = makeTB.Text;
string model = modelTB.Text;
string serial = serialTB.Text;
string dos = dateInServiceTB.Text;
string key = productKeyTB.Text;
string pcname = computerNameTB.Text;
string price = pricetb.Text;
string location = locationcb.Text;
string department = departmentcb.Text;
string costcenter = costcentertb.Text;
string formfactor = formfactorcb.Text;
if (fname.Contains(" "))
{
fname = fname.Replace(" ", "");
if (lname.Contains(" "))
{
lname = lname.Replace(" ", "");
}
}
string sqlStatement = "Insert into list(tag,first_name,last_name,make,model,serial,date_in_service,productkey,computer_name,price,location,department,cost_center,form_factor)" +
" values ('" + tagNum + "','" + fname + "','" + lname + "','" + make + "','" + model + "','" + serial + "','" + dos + "','" + key.Insert(5, "-") + "','" + pcname + "','" + price + "','" + location + "','" + department + "','" + costcenter + "','" + formfactor + "')";
sqlConnect connection = new sqlConnect();
MySqlConnection conn = new MySqlConnection(connection.connection());
MySqlCommand cmd = new MySqlCommand(sqlStatement, conn);
try
{
conn.Open();
if (tagNum != "" && fname != "" && lname != "" && make != "" && model != "" && serial != "" && dos != "" && key != "" && pcname != "")
{
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
MessageBox.Show("" + fname +
" " + lname + " was succesfully added too the database!");
tagNumTB.Clear();
fNameTB.Clear();
lNameTB.Clear();
makeTB.Clear();
modelTB.Clear();
serialTB.Clear();
dateInServiceTB.Clear();
productKeyTB.Clear();
computerNameTB.Clear();
getAssets();
}
else
{
MessageBox.Show("Unsuccessful attempt to add entry to database!");
}
}
else
{
MessageBox.Show("Please enter all information before addin an asset"
+ "If you do not know certain information please add 'xxx'");
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.Message);
}
finally
{
conn.Close();
}
getDate();
}
//loads tag number into add assets
private void getTagNumber()
{
// retrieve the tag# from the database then place it in the tag number text box
try
{
tagNumTB.Focus();
string sqlStatement = "Select tag from list order by tag desc limit 1";
sqlConnect connection = new sqlConnect();
MySqlConnection conn = new MySqlConnection(connection.connection());
MySqlCommand cmd = new MySqlCommand(sqlStatement, conn);
conn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int tagNum = Convert.ToInt32(reader[0]) + 1;
tagNumTB.Text = tagNum.ToString();
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.Message);
}
}
//loads assets information based on item index in List Box
private void editAsset()
{
int index = assetsListBox.SelectedIndex;
if (index != ListBox.NoMatches)
{
//split the string into first and last name
string[] split = assetsListBox.Items[index].ToString().Split(' ');
string firstname = split[0];
string lastname = split[1];
string sqlStatement = null;
try
{
sqlConnect newConn = new sqlConnect();
//connect to the MySQL database
MySqlConnection conn = new MySqlConnection(newConn.connection());
conn.Open();
if (serialRB.Checked == true)
{
sqlStatement = "select * from list where first_name ='" + firstname + "' and first_name='" + lastname + "'";
}
else if (firstnameRB.Checked == true);
{
sqlStatement = "select * from list where first_name ='" + firstname + "' and last_name='" + lastname + "'";
}
DataSet DS2 = new DataSet();
MySqlDataAdapter sqlAdapter2 = new MySqlDataAdapter(sqlStatement, conn);
sqlAdapter2.Fill(DS2, "tag");
foreach (DataRow row in DS2.Tables[0].Rows)
{
tagAltlbl.Text = row[0].ToString();
firstNameTB.Text = row[1].ToString();
lastNameTB.Text = row[2].ToString();
makeAltTB.Text = row[3].ToString();
modelAltTB.Text = row[4].ToString();
serialAltTB.Text = row[5].ToString();
dosAltTB.Text = changeDate(row[6].ToString());
keyAltTB.Text = row[7].ToString();
pcNameAltTB.Text = row[8].ToString();
editpricetb.Text = row[10].ToString();
editformfactortb.Text = row[9].ToString();
editlocationcb.Text = row[11].ToString();
editdepartmentcb.Text = row[12].ToString();
editcostcentertb.Text = row[13].ToString();
}
if (lastnameRB.Checked == true)
{
sqlStatement = "select * from list where last_name ='" + lastname + "' and first_name='" + firstname + "' order by first_name";
DataSet DS3 = new DataSet();
MySqlDataAdapter sqlAdapter3 = new MySqlDataAdapter(sqlStatement, conn);
sqlAdapter3.Fill(DS3, "tag");
foreach (DataRow row in DS3.Tables[0].Rows)
{
tagAltlbl.Text = row[0].ToString();
firstNameTB.Text = row[1].ToString();
lastNameTB.Text = row[2].ToString();
makeAltTB.Text = row[3].ToString();
modelAltTB.Text = row[4].ToString();
serialAltTB.Text = row[5].ToString();
dosAltTB.Text = setEditDate(row[6].ToString());
keyAltTB.Text = row[7].ToString();
pcNameAltTB.Text = row[8].ToString();
editpricetb.Text = row[10].ToString();
editformfactortb.Text = row[9].ToString();
editlocationcb.Text = row[11].ToString();
editdepartmentcb.Text = row[12].ToString();
editcostcentertb.Text = row[13].ToString();
}
}
}
// catch any mysql errors
catch (MySqlException sqle)
{
MessageBox.Show("Error {0}: " + sqle);
}
}
}
private void deleteAsset_Click(object sender, EventArgs e)
{
sqlConnect newConn = new sqlConnect();
MySqlConnection conn = new MySqlConnection(newConn.connection());
string statement = "DELETE from list where first_name ='" + firstNameTB.Text + "' and last_name='" + lastNameTB.Text + "'";
MySqlCommand cmd = new MySqlCommand(statement, conn);
try
{
conn.Open();
if (firstNameTB.Text != null && lastNameTB.Text != null)
{
if (deleteCheckBox.Checked == true)
{
cmd.ExecuteNonQuery();
deleteCheckBox.Checked = false;
firstNameTB.Text = "";
lastNameTB.Text = "";
makeAltTB.Text = "";
modelAltTB.Text = "";
serialAltTB.Text = "";
dosAltTB.Text = "";
keyAltTB.Text = "";
pcNameAltTB.Text = "";
editformfactortb.Text = "";
editpricetb.Text = "";
editlocationcb.Text = "";
editdepartmentcb.Text = "";
editcostcentertb.Text = "";
getAssets();
}
else
{
MessageBox.Show("Please confirm deleting!", "Confirm Delete", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Please select an asset!");
}
}
catch (MySqlException deleteEx)
{
MessageBox.Show("Unable to delete" + firstNameTB.Text + " " + lastNameTB.Text + "! Error {0}:" + deleteEx);
}
}