예제 #1
0
        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;
            }
        }
예제 #2
0
        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;
            }
        }