// Fill lblAddress and lblContact based on the node selected in the TreeView
private void FillLabels(ref myTreeNode N)
{
// If node is a Manager display the Manager's information
if (N.Tag.ToString() == "Manager")
{
DisplayManagerInfo(N);
// Else if Property display the Property's information
}
else if (N.Tag.ToString() == "Property")
{
DisplayPropertyInfo(N);
// Else if Assembly display the Assembly's information
}
else if (N.Tag.ToString() == "Assembly")
{
DisplayAssemblyInfo(N);
}
lblLeft.Text = strLabelLeft;
lblRight.Text = strLabelRight;
lblBottom.Text = strLabelBottom;
llblEmail.Text = Email;
grpDetails.Text = N.Tag.ToString();
}
private void DisplayManagerInfo(myTreeNode N)
{
// Create SQL Connection
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
// Displays the image next to the label information
lblImage.ImageIndex = 0;
// Displays the buttons in the label area
btnMap.Visible = true;
btnPhoneOrTest.Text = "Phonebook";
try
{
// Open the DataConnection
cn.Open();
// Select the Manager information where the Manager No. is equal to the Manager No.
// stored in the myTreeNode of the Selected Node.
cmd.CommandText = "SELECT manName, manStrtAdd, manCity, manState, manZip, " + "manCntct, manPhone, manFax, manEmail FROM Managers Where manNo = " + ((myTreeNode) TreeView1.SelectedNode).ID;
// Run the query; get the DataReader object.
SqlDataReader dr = cmd.ExecuteReader();
// string str;
if (dr.Read())
{
// Null + Empty = Empty
ComName = dr["manName"].ToString() + "";
Street = dr["manStrtAdd"].ToString() + "";
City = dr["manCity"].ToString() + "";
State = dr["manState"].ToString() + "";
Zip = dr["manZip"].ToString() + "";
Contact = dr["manCntct"].ToString() + "";
Phone = dr["manPhone"].ToString() + "";
Fax = dr["manFax"].ToString() + "";
Email = dr["manEmail"].ToString() + "";
}
dr.Close();
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
string msg = "";
// string msg;
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlErr = ex.Errors[i];
msg = "Message = " + sqlErr.Message + ControlChars.CrLf;
msg += "Source = " + sqlErr.Source + ControlChars.CrLf;
}
MessageBox.Show(msg);
}
catch (Exception ex)
{
// A generic exception has occured
MessageBox.Show(ex.Message);
}
finally
{
// Close the connection
cn.Close();
}
// Set the Managers info to the following strings
strLabelLeft = ComName + ControlChars.CrLf + Street + ControlChars.CrLf + City + ", " + State + " " + Zip;
strLabelRight = "Contact: " + Contact + ControlChars.CrLf + "Phone: " + Phone + ControlChars.CrLf + "Fax: " + Fax;
strLabelBottom = "";
}
private void DisplayPropertyInfo(myTreeNode N)
{
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
// Displays the image next to the label information
lblImage.ImageIndex = 1;
// Displays the buttons in the label area
btnMap.Visible = true;
btnPhoneOrTest.Text = "Phonebook";
try
{
cn.Open(); // Open connection
// Get the Property Information based on the Type and ID provided in the Selected Node (myTreeNode)
cmd.CommandText = "SELECT propName, propStrt, propCity, propState, propZip, propCon, propPhone, " + "propFax, propEmail FROM Properties Where propNo = " + N.ID;
// Run the query; get the DataReader object.
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
// Null + Empty = Empty
ComName = dr["propName"].ToString() + "";
Street = dr["propStrt"].ToString() + "";
City = dr["propCity"].ToString() + "";
State = dr["propState"].ToString() + "";
Zip = dr["propZip"].ToString() + "";
Contact = dr["propCon"].ToString() + "";
Phone = dr["propPhone"].ToString() + "";
Fax = dr["propFax"].ToString() + "";
Email = dr["propEmail"].ToString() + "";
}
dr.Close(); // Close DataReader
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
string msg = "";
// string msg;
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlErr = ex.Errors[i];
msg = "Message = " + sqlErr.Message + ControlChars.CrLf;
msg += "Source = " + sqlErr.Source + ControlChars.CrLf;
}
MessageBox.Show(msg);
}
catch (Exception ex)
{
// A generic exception has occured
MessageBox.Show(ex.Message);
}
finally
{
cn.Close(); // Close the connection
}
// Set the Property's information to the following strings
strLabelLeft = ComName + ControlChars.CrLf + Street + ControlChars.CrLf + City + ", " + State + " " + Zip;
strLabelRight = "Contact: " + Contact + ControlChars.CrLf + "Phone: " + Phone + ControlChars.CrLf + "Fax: " + Fax;
strLabelBottom = "";
}
private void DisplayAssemblyInfo(myTreeNode N)
{
string Model = "";
string Serial = "";
string Usage = "";
string InstallDate;
string Location = "";
string Type = "";
string Manufacturer = "";
string Size = "";
string LastTest = "";
// If the Selected Node is an Assembly then get the Assembly's info
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
lblImage.ImageIndex = 2;
btnMap.Visible = false;
btnPhoneOrTest.Text = "Add Test";
try
{
cn.Open(); // Open connection
cmd.CommandText = "SELECT Assemblies.assemMod, Assemblies.assemSerial, Assemblies.assemUsage, " + "Assemblies.assemInstDt, Assemblies.assemLoc, Assemblies.assemType, " + "Assemblies.AssemMan, AssemblySizes.AssemSize " + "FROM Assemblies " + "LEFT JOIN AssemblySizes ON AssemblySizes.AssemSizeNo = Assemblies.assemSizeNo " + "LEFT JOIN Tests ON Tests.assemNo = Assemblies.assemNo " + "WHERE Assemblies.assemNo = " + N.ID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
// Null + Empty = Empty
Model = dr["assemMod"].ToString() + "";
Serial = dr["assemSerial"].ToString() + "";
Usage = dr["assemUsage"].ToString() + "";
InstallDate = dr["assemInstDt"].ToString() + "";
Location = dr["assemLoc"].ToString() + "";
Type = dr["assemType"].ToString() + "";
Manufacturer = dr["AssemMan"].ToString() + "";
Size = dr["AssemSize"].ToString() + "";
}
Email = "";
dr.Close(); // Close the DataReader
cmd.CommandText = "SELECT MAX(Tests.testDate) FROM Tests " + "LEFT JOIN Assemblies ON Assemblies.assemNo = Tests.assemNo " + "WHERE Assemblies.assemNo = " + N.ID;
if (Information.IsDBNull(cmd.ExecuteScalar()))
{
LastTest = null;
}
else
{
LastTest = cmd.ExecuteScalar().ToString();
}
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
string msg = "";
// string msg;
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlErr = ex.Errors[i];
msg = "Message = " + sqlErr.Message + ControlChars.CrLf;
msg += "Source = " + sqlErr.Source + ControlChars.CrLf;
}
MessageBox.Show(msg);
}
catch (Exception ex)
{
// A generic exception has occured
MessageBox.Show(ex.Message);
}
finally
{
// Close the connection
cn.Close();
}
// Set the Assembly's information to the following strings
strLabelLeft = "Serial: " + Serial + ControlChars.CrLf + "Type: " + Type + ControlChars.CrLf + "Manufacturer: " + Manufacturer + ControlChars.CrLf + "Model: " + Model;
strLabelRight = "Size: " + Size + ControlChars.CrLf + "Usage: " + Usage + ControlChars.CrLf + "Last Test Date: " + LastTest + ControlChars.CrLf;
strLabelBottom = "Location: " + Location;
}
// Deleted records remain in the database for reporting and history
private void DeleteNode()
{
myTreeNode N = (myTreeNode) TreeView1.SelectedNode;
if ((!(N == null)) && MessageBox.Show("Are you sure you want to delete " + ControlChars.CrLf + "the " + N.Tag.ToString() + " " + N.Text + "?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
// Establish the connection
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
try
{
cn.Open(); // Open the connection
// If Manager is "deleted" then set the Properties and Assemblies to "deleted" as well
if (N.Tag.ToString() == "Manager")
{
// Set the Manager's manDeleted to True
cmd.CommandText = "UPDATE Managers SET manDeleted = 1 WHERE manNo = " + N.ID;
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE Properties SET propDeleted = 1 WHERE manNo = " + N.ID;
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE Assemblies SET assemDeleted = 1 " + "FROM Managers, Properties, Assemblies " + "WHERE Managers.manNo = Properties.manNo And " + "Properties.propNo = Assemblies.propNo And Managers.manNo = " + N.ID;
cmd.ExecuteNonQuery();
}
// If the Property is "deleted" then set the Assemlies to "deleted" as well
if (N.Tag.ToString() == "Property")
{
cmd.CommandText = "UPDATE Properties SET propDeleted = 1 WHERE propNo = " + ((myTreeNode) TreeView1.SelectedNode).ID;
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE Assemblies SET assemDeleted = 1 WHERE propNo = " + ((myTreeNode) TreeView1.SelectedNode).ID;
cmd.ExecuteNonQuery();
if (TheCutNode != null)
{
if (N.ID == TheCutNode.ID)
{
TheCutNode = null;
}
}
}
// If the Assembly is "deleted" just mark it as "deleted"
if (N.Tag.ToString() == "Assembly")
{
cmd.CommandText = "UPDATE Assemblies SET assemDeleted = 1 WHERE assemNo = " + ((myTreeNode) TreeView1.SelectedNode).ID;
cmd.ExecuteNonQuery();
if (TheCutNode != null)
{
if (N.Tag.ToString() == TheCutNode.Tag.ToString() && N.ID == TheCutNode.ID)
{
TheCutNode = null;
}
}
}
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
string msg = "";
// string msg;
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlErr = ex.Errors[i];
msg = "Message = " + sqlErr.Message + ControlChars.CrLf;
msg += "Source = " + sqlErr.Source + ControlChars.CrLf;
}
MessageBox.Show(msg);
// Catch ex As Exception
// A generic exception has occured
// MessageBox.Show(ex.Message)
}
finally
{
// Close the connection
cn.Close();
}
myTreeNode tn = (myTreeNode) TreeView1.SelectedNode; // reference the selected node
TreeView1.Nodes.Remove(tn); // remove the selected node
}
}
private void CutNode()
{
// Save the currently selected node to the holder, CutNode.
TheCutNode = (myTreeNode) TreeView1.SelectedNode;
mnuCut.Enabled = false;
tbbCut.Enabled = false;
}
private void PropertyToManager()
{
int ManNo = 0;
int PropNo = 0;
string Name = "";
string Street = "";
string City = "";
string State = "";
string Zip = "";
string Contact = "";
string Phone = "";
string Fax = "";
string Email = "";
if (TreeView1.SelectedNode.Tag.ToString() == "Property")
{
SqlConnection cn = new SqlConnection(connectionString);
try
{
string sqlStatement = "SELECT * FROM Properties WHERE propNo = " + ((myTreeNode) TreeView1.SelectedNode).ID;
SqlCommand cmd = new SqlCommand(sqlStatement, cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
// Null + Empty = Empty
PropNo = System.Convert.ToInt32(dr["propNo"]);
Name = dr["propName"].ToString() + "";
Street = dr["propStrt"].ToString() + "";
City = dr["propCity"].ToString() + "";
State = dr["propState"].ToString() + "";
Zip = dr["propZip"].ToString() + "";
Contact = dr["propCon"].ToString() + "";
Phone = dr["propPhone"].ToString() + "";
Fax = dr["propFax"].ToString() + "";
Email = dr["propEmail"].ToString() + "";
ManNo = System.Convert.ToInt32(dr["manNo"]);
}
dr.Close();
cn.Open();
cmd.CommandText = "UPDATE Properties SET propPrevManNo = " + ManNo + " WHERE propNo = " + PropNo;
cmd.ExecuteNonQuery();
string strSQL;
strSQL = "INSERT INTO Managers (manName, manStrtAdd, manCity, manState, " + "manZip, manCntct, manPhone, manFax, manEmail, manSchemeNo) VALUES ( ";
strSQL += "\'" + Name.Replace("\'", "\'\'") + "\', ";
strSQL += "\'" + Street + "\', ";
strSQL += "\'" + City + "\', ";
strSQL += "\'" + State + "\', ";
strSQL += "\'" + Zip + "\', ";
strSQL += "\'" + Contact + "\', ";
strSQL += "\'" + Phone + "\', ";
strSQL += "\'" + Fax + "\', ";
strSQL += "\'" + Email + "\', ";
strSQL += "1)";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT MAX(manNo) FROM Managers";
int manNumber = System.Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = "UPDATE Properties SET manNo = " + manNumber + " WHERE propNo = " + PropNo;
cmd.ExecuteNonQuery();
myTreeNode M = new myTreeNode(Name, "Manager", manNumber, true, 0, 0);
myTreeNode P = (myTreeNode) TreeView1.SelectedNode;
P.Remove();
M.Nodes.Add(P);
TreeView1.Nodes.Add(M);
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
string msg = "";
// string msg;
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlErr = ex.Errors[i];
msg = "Message = " + sqlErr.Message + ControlChars.CrLf;
msg += "Source = " + sqlErr.Source + ControlChars.CrLf;
}
MessageBox.Show(msg + "this is it");
}
catch (Exception ex)
{
// A generic exception has occured
MessageBox.Show(ex.Message);
}
finally
{
// Close the connection
cn.Close();
}
}
}
private void PasteNode()
{
myTreeNode N;
N = (myTreeNode) TreeView1.SelectedNode;
if (N.Tag.ToString() == "Manager" && TheCutNode.Tag.ToString() == "Property")
{
SqlConnection cn = new SqlConnection(connectionString);
try
{
string sql = "SELECT manNo FROM Properties WHERE propNo = " + TheCutNode.ID;
SqlCommand cmd = new SqlCommand(sql, cn);
cn.Open();
intPrevMan = System.Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = "UPDATE Properties SET manNo = " + N.ID + " WHERE propNo = " + TheCutNode.ID;
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE Properties SET propPrevManNo = " + intPrevMan + " WHERE propNo = " + TheCutNode.ID;
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
string msg = "";
// string msg;
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlErr = ex.Errors[i];
msg = "Message = " + sqlErr.Message + ControlChars.CrLf;
msg += "Source = " + sqlErr.Source + ControlChars.CrLf;
}
MessageBox.Show(msg);
}
catch (Exception ex)
{
// A generic exception has occured
MessageBox.Show(ex.Message);
}
finally
{
// Close the connection
cn.Close();
// Remove Node from previous manager
TheCutNode.Remove();
// Add Node to the new manager
TheCutNode.ForeColor = Color.Black;
N.Nodes.Add(TheCutNode);
TheCutNode = null;
tbbPaste.Enabled = false;
mnuPaste.Enabled = false;
}
}
else if (N.Tag.ToString() == "Property" && TheCutNode.Tag.ToString() == "Assembly")
{
SqlConnection cn = new SqlConnection(connectionString);
try
{
string sql = "SELECT propNo FROM Assemblies WHERE assemNo = " + TheCutNode.ID;
SqlCommand cmd = new SqlCommand(sql, cn);
cn.Open();
intPrevProp = System.Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = "UPDATE Assemblies SET propNo = " + N.ID + " WHERE assemNo = " + TheCutNode.ID;
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
string msg = "";
// string msg;
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlErr = ex.Errors[i];
msg = "Message = " + sqlErr.Message + ControlChars.CrLf;
msg += "Source = " + sqlErr.Source + ControlChars.CrLf;
}
MessageBox.Show(msg);
}
catch (Exception ex)
{
// A generic exception has occured
MessageBox.Show(ex.Message);
}
finally
{
// Close the connection
cn.Close();
// Remove Node from previous manager
TheCutNode.Remove();
// Add Node to the new property
TheCutNode.ForeColor = Color.Black;
N.Nodes.Add(TheCutNode);
TheCutNode = null;
tbbPaste.Enabled = false;
mnuPaste.Enabled = false;
}
}
}
