private void PopulateMunicipalityList()
{
SqlConnection cn = new SqlConnection(connectionString);
string sql = "SELECT munName, munNo FROM Municipalities WHERE munDeleted = 0 ORDER BY munName";
SqlCommand cmd = new SqlCommand(sql, cn);
// lvMunicipalities.Items.Clear()
lvMunicipalities.Clear();
// Set to details view
ColumnHeader columnheader; // Used for creating column headers.
ListViewItem listviewitem; // Used for creating ListView items.
// Make sure that the view is set to show details.
lvMunicipalities.View = View.Details;
try
{
cn.Open();
// Run the query; get the DataReader object.
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
// Create some ListView items consisting of first and last names.
listviewitem = new myListViewItem((dr.item["munName"].ToString() + ""), "Municipality", System.Convert.ToInt32(dr.item["munNo"]), 1);
this.lvMunicipalities.Items.Add(listviewitem);
}
// lstMunicipalities.SetSelected(0, True)
dr.Close();
}
catch (SqlException ex)
{
//A SqlException has occured - display details
int i;
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();
}
// Create some column headers for the data.
columnheader = new ColumnHeader();
columnheader.Text = "Municipality";
this.lvMunicipalities.Columns.Add(columnheader);
// Loop through and size each column header to fit the column header text.
foreach (ColumnHeader tempLoopVar_columnheader in this.lvMunicipalities.Columns)
{
columnheader = tempLoopVar_columnheader;
columnheader.Width = - 2;
}
}
private void Search()
{
ListView1.Clear();
ProgressBar1.Value = 15;
// Set to details view
ColumnHeader columnheader; // Used for creating column headers.
ListViewItem listviewitem; // Used for creating ListView items.
// Make sure that the view is set to show details.
ListView1.View = View.Details;
int intManNo;
string strManager;
string strProperty;
int intPropNo;
int intAssemNo;
string strAssembly;
SqlConnection cn = new SqlConnection(connectionString);
string sql = "SELECT manNo, manName FROM Managers WHERE ( manName LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR manStrtAdd LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR manSuite LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR manCity LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR manZip LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR manCntct LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + ") AND manDeleted = 0";
SqlCommand cmd = new SqlCommand(sql, cn);
ProgressBar1.Value = 30;
try
{
cn.Open();
// Run the query; get the DataReader object.
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
strManager = dr["manName"].ToString() + "";
intManNo = System.Convert.ToInt32(dr["manNo"]);
// Create some ListView items consisting of first and last names.
listviewitem = new myListViewItem(strManager, "Manager", intManNo, 0);
listviewitem.SubItems.Add(strManager);
this.ListView1.Items.Add(listviewitem);
}
dr.Close();
ProgressBar1.Value = 45;
cmd.CommandText = "SELECT propNo, propName, storeNo, manName FROM Properties " + "LEFT JOIN Managers ON Managers.manNo = Properties.manNo WHERE ( " + "propName LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR storeNo LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR propCon LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR propStrt LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR propCity LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR propZip LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + ") AND propDeleted = 0";
dr = cmd.ExecuteReader();
while (dr.Read())
{
intPropNo = System.Convert.ToInt32(dr["propNo"]);
// strProperty = dr.Item("propName") & ""
strManager = dr["manName"].ToString() + "";
if (Information.IsDBNull(dr["storeNo"]))
{
strProperty = dr["propName"].ToString() + "";
}
else if (dr["storeNo"].ToString() == "")
{
strProperty = dr["propName"].ToString() + "";
}
else
{
strProperty = dr["propName"].ToString() + " - " + dr["storeNo"].ToString();
}
// Create some ListView items consisting of first and last names.
listviewitem = new myListViewItem(strProperty, "Property", intPropNo, 1);
listviewitem.SubItems.Add(strManager + " \\ " + strProperty);
this.ListView1.Items.Add(listviewitem);
}
dr.Close();
ProgressBar1.Value = 60;
cmd.CommandText = "SELECT assemNo, assemSerial, manName, propName, storeNo FROM Assemblies " + "LEFT JOIN Properties ON Properties.propNo = Assemblies.propNo " + "LEFT JOIN Managers ON Managers.manNo = Properties.manNo WHERE ( " + "assemSerial LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + "OR assemMod LIKE \'%" + txtSearch.Text.Replace("\'", "\'\'") + "%\' " + ") AND assemDeleted = 0";
dr = cmd.ExecuteReader();
while (dr.Read())
{
intAssemNo = System.Convert.ToInt32(dr["assemNo"]);
strAssembly = dr["assemSerial"].ToString() + "";
// strProperty = dr.Item("propName") & ""
strManager = dr["manName"].ToString() + "";
if (Information.IsDBNull(dr["storeNo"]))
{
strProperty = dr["propName"].ToString() + "";
}
else if (dr["storeNo"].ToString() == "")
{
strProperty = dr["propName"].ToString() + "";
}
else
{
strProperty = dr["propName"].ToString() + " - " + dr["storeNo"].ToString();
}
// Create some ListView items consisting of first and last names.
listviewitem = new myListViewItem(strAssembly, "Assembly", intAssemNo, 2);
listviewitem.SubItems.Add(strManager + " \\ " + strProperty + " \\ " + strAssembly);
this.ListView1.Items.Add(listviewitem);
}
dr.Close();
ProgressBar1.Value = 75;
// Create some column headers for the data.
columnheader = new ColumnHeader();
columnheader.Text = "Item";
this.ListView1.Columns.Add(columnheader);
columnheader = new ColumnHeader();
columnheader.Text = "Location";
this.ListView1.Columns.Add(columnheader);
// Loop through and size each column header to fit the column header text.
foreach (ColumnHeader tempLoopVar_columnheader in this.ListView1.Columns)
{
columnheader = tempLoopVar_columnheader;
columnheader.Width = - 2;
}
ProgressBar1.Value = 100;
}
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();
ProgressBar1.Value = 0;
}
}