/// <summary> /// Method to bind lower comboboxes. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dgvSearch_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e) { //Queries specifically for Features and Statuses comboboxes string feature = @"SELECT DISTINCT FEATURE_TYPES.FEAT_TYPE AS 'Feature Type' FROM FEATURE_TYPES ORDER BY 'Feature Type' ASC"; string status = @"SELECT DISTINCT NAME_PLACES.STATUS_CODE AS 'Status' FROM NAME_PLACES ORDER BY 'Status' ASC"; cboFear.DataSource = ConnectionClass.getSQLData(feature).Tables[0]; // In this combo box, for each value from the source // We display the value of feature type cboFear.DisplayMember = cboFear.ValueMember = "Feature Type"; if (dgvSearch.SelectedRows.Count > 0) { cboFear.SelectedIndex = cboFear.FindStringExact(dgvSearch.CurrentRow.Cells["Feature Type"].Value.ToString()); } cboStatus.DataSource = ConnectionClass.getSQLData(status).Tables[0]; cboStatus.DisplayMember = cboStatus.ValueMember = "Status"; if (dgvSearch.SelectedRows.Count > 0) { cboStatus.SelectedIndex = cboStatus.FindStringExact(dgvSearch.CurrentRow.Cells["Status"].Value.ToString()); } }
private void dgvSearch_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e) { cboFear.DataSource = ConnectionClass.getSQLData("SELECT DISTINCT * FROM FEATURE_TYPES ORDER BY FEAT_TYPE ASC").Tables[0]; cboFear.DisplayMember = cboFear.ValueMember = "FEAT_TYPE"; cboStatus.DataSource = ConnectionClass.getSQLData("SELECT DISTINCT STATUS_CODE FROM NAME_PLACES ORDER BY STATUS_CODE ASC").Tables[0]; cboStatus.DisplayMember = cboStatus.ValueMember = "STATUS_CODE"; }
private void cboFeature_SelectedIndexChanged(object sender, EventArgs e) { string basicQuery = @"SELECT NAMES.NAME_ACTUAL AS 'Geographical Name', NAME_PLACES.FEATURE_ID AS 'Unique National Identifier', NAME_PLACES.DATE_CH AS 'Date Changed', NAME_PLACES.STATUS_CODE AS 'Status', NAMES.CASUALTY AS 'Casualty', NAME_PLACES.ACT_FROM AS 'Act From', NAME_PLACES.ACT_TO AS 'Act To', PLACES.FEAT_CODE AS 'Feature Code', PLACES.MS250 AS 'NTS 250000 Map Sheet', PLACES.MS50 AS 'NTS 50000 Submap Sheet', PLACES.LAT_DEG AS 'LATITUDE Degrees', PLACES.LAT_MIN AS 'LATITUDE Minutes', PLACES.LAT_SEC AS 'LATITUDE Seconds', PLACES.LONG_DEG AS 'LONGITUDE Degrees', PLACES.LONG_MIN AS 'LONGITUDE Minutes', PLACES.LONG_SEC AS 'LONGITUDE Seconds', CASUALTIES.COMMUNITY AS 'Casualty Hometown', CASUALTIES.REG_NO AS 'Casualty Regimental Number', CASUALTIES.RANK_CASUALTY AS 'Casualty Rank', CASUALTIES.SURNAME AS 'Casualty Surname', CASUALTIES.GIVNAME AS 'Casualty Given Name', CASUALTIES.DATE_DECEASED AS 'Casualty Date of Death', CASUALTIES.SERVED AS 'Casualty Regiment', CASUALTIES.BURIED AS 'Casualty Place of Burial', FEATURE_TYPES.FEAT_TYPE AS 'Feature Type', FEATURE_TYPES.DESCR AS 'Feature Type Description', PLACES.LONGITUDE, PLACES.LATITUDE FROM NAMES JOIN NAME_PLACES ON NAMES.NAME_ID = NAME_PLACES.NAME_ID JOIN PLACES ON PLACES.PLACE_ID = NAME_PLACES.PLACE_ID LEFT JOIN CASUALTIES ON NAMES.CASUALTY_ID = CASUALTIES.CASUALTY_ID LEFT JOIN FEATURE_TYPES ON PLACES.FEAT_CODE = FEATURE_TYPES.FEAT_CODE "; if (radFeature.Checked) { basicQuery += " WHERE FEATURE_TYPES.FEAT_TYPE = @feattype"; ConnectionClass.AddParam("feattype", cboFeature.SelectedValue.ToString()); } //dgvSearch.DataSource = ConnectionClass.getSQLData(basicQuery).Tables[0]; bds = ConnectionClass.getSQLData(basicQuery).Tables[0]; dgvSearch.DataSource = bds; for (int i = 1; i < dgvSearch.ColumnCount; i++) { dgvSearch.Columns[i].Visible = false; } dgvSearch.Columns[0].Width = dgvSearch.Width; }
private void btnSearch_Click(object sender, EventArgs e) { string enteredName = txtSearch.Text; txtSearch.SelectAll(); txtSearch.Focus(); string basicQuery = @"SELECT NAMES.NAME_ACTUAL AS 'Geographical Name', NAME_PLACES.FEATURE_ID AS 'Unique National Identifier', NAME_PLACES.DATE_CH AS 'Date Changed', NAME_PLACES.STATUS_CODE AS 'Status', NAMES.CASUALTY AS 'Casualty', NAME_PLACES.ACT_FROM AS 'Act From', NAME_PLACES.ACT_TO AS 'Act To', PLACES.FEAT_CODE AS 'Feature Code', PLACES.MS250 AS 'NTS 250000 Map Sheet', PLACES.MS50 AS 'NTS 50000 Submap Sheet', PLACES.LAT_DEG AS 'LATITUDE Degrees', PLACES.LAT_MIN AS 'LATITUDE Minutes', PLACES.LAT_SEC AS 'LATITUDE Seconds', PLACES.LONG_DEG AS 'LONGITUDE Degrees', PLACES.LONG_MIN AS 'LONGITUDE Minutes', PLACES.LONG_SEC AS 'LONGITUDE Seconds', CASUALTIES.COMMUNITY AS 'Casualty Hometown', CASUALTIES.REG_NO AS 'Casualty Regimental Number', CASUALTIES.RANK_CASUALTY AS 'Casualty Rank', CASUALTIES.SURNAME AS 'Casualty Surname', CASUALTIES.GIVNAME AS 'Casualty Given Name', CASUALTIES.DATE_DECEASED AS 'Casualty Date of Death', CASUALTIES.SERVED AS 'Casualty Regiment', CASUALTIES.BURIED AS 'Casualty Place of Burial', FEATURE_TYPES.FEAT_TYPE AS 'Feature Type', FEATURE_TYPES.DESCR AS 'Feature Type Description', PLACES.LONGITUDE, PLACES.LATITUDE FROM NAMES JOIN NAME_PLACES ON NAMES.NAME_ID = NAME_PLACES.NAME_ID JOIN PLACES ON PLACES.PLACE_ID = NAME_PLACES.PLACE_ID LEFT JOIN CASUALTIES ON NAMES.CASUALTY_ID = CASUALTIES.CASUALTY_ID LEFT JOIN FEATURE_TYPES ON PLACES.FEAT_CODE = FEATURE_TYPES.FEAT_CODE "; if (radMaps.Checked) { MakeSpecificRadioAvailabel(sender, e); } if (radMS250.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " WHERE PLACES.MS250 = @ms250"; ConnectionClass.AddParam("ms250", enteredName); } else if (radMS50.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " WHERE PLACES.MS250 = @ms250" + " AND PLACES.MS50 = @ms50"; ConnectionClass.AddParam("ms250".ToString(), enteredName.ToString()); ConnectionClass.AddParam("ms50".ToString(), enteredName.ToString()); } else if (radLocation.Checked && !String.IsNullOrWhiteSpace(txtLatDeg.Text) && !String.IsNullOrWhiteSpace(txtLatMin.Text) && !String.IsNullOrWhiteSpace(txtLatSec.Text) && !String.IsNullOrWhiteSpace(txtLongDeg.Text) && !String.IsNullOrWhiteSpace(txtLongMin.Text) && !String.IsNullOrWhiteSpace(txtLongSec.Text)) { basicQuery += " WHERE PLACES.LAT_DEG = @LatDeg"; basicQuery += " AND PLACES.LAT_MIN = @LatMin"; basicQuery += " AND PLACES.LAT_SEC = @LatSec"; basicQuery += " AND PLACES.LONG_DEG = @LongDeg"; basicQuery += " AND PLACES.LONG_MIN = @LongMin"; basicQuery += " AND PLACES.LONG_SEC = @LongSec"; ConnectionClass.AddParam("LatDeg", txtLatDeg.Text); ConnectionClass.AddParam("LatMin", txtLatMin.Text); ConnectionClass.AddParam("LatSec", txtLatSec.Text); ConnectionClass.AddParam("LongDeg", txtLongDeg.Text); ConnectionClass.AddParam("LongMin", txtLongMin.Text); ConnectionClass.AddParam("LongSec", txtLongSec.Text); } else if (radName.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " WHERE NAMES.NAME_ACTUAL LIKE @name + '%'"; ConnectionClass.AddParam("name", enteredName); } else if (radFID.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " WHERE NAME_PLACES.FEATURE_ID = @ident"; ConnectionClass.AddParam("ident", enteredName); } else if (radStatus.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " WHERE NAME_PLACES.STATUS_CODE = @statuscode"; ConnectionClass.AddParam("statuscode", enteredName); } bds = ConnectionClass.getSQLData(basicQuery).Tables[0]; dgvSearch.DataSource = bds; for (int i = 1; i < dgvSearch.ColumnCount; i++) { dgvSearch.Columns[i].Visible = false; } dgvSearch.Columns[0].Width = dgvSearch.Width; }
/// <summary> /// Method to handle the "Search" button. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { string enteredName = txtSearch.Text; txtSearch.SelectAll(); txtSearch.Focus(); //A block to build a virtual combotable to be used by labels and gridview. string basicQuery = @"SELECT NAMES.NAME_ACTUAL AS 'Geographical Name', NAME_PLACES.FEATURE_ID AS 'Unique National Identifier', NAME_PLACES.DATE_CH AS 'Date Changed', NAME_PLACES.STATUS_CODE AS 'Status', NAMES.CASUALTY AS 'Casualty', NAME_PLACES.ACT_FROM AS 'Act From', NAME_PLACES.ACT_TO AS 'Act To', NAME_PLACES.ACT_TO AS 'Act To', PLACES.FEAT_CODE AS 'Feature Code', PLACES.MS250 AS 'NTS 250000 Map Sheet', PLACES.MS50 AS 'NTS 50000 Submap Sheet', PLACES.LAT_DEG AS 'LATITUDE Degrees', PLACES.LAT_MIN AS 'LATITUDE Minutes', PLACES.LAT_SEC AS 'LATITUDE Seconds', PLACES.LONG_DEG AS 'LONGITUDE Degrees', PLACES.LONG_MIN AS 'LONGITUDE Minutes', PLACES.LONG_SEC AS 'LONGITUDE Seconds', CASUALTIES.COMMUNITY AS 'Casualty Hometown', CASUALTIES.REG_NO AS 'Casualty Regimental Number', CASUALTIES.RANK_CASUALTY AS 'Casualty Rank', CASUALTIES.SURNAME AS 'Casualty Surname', CASUALTIES.GIVNAME AS 'Casualty Given Name', CASUALTIES.DATE_DECEASED AS 'Casualty Date of Death', CASUALTIES.SERVED AS 'Casualty Regiment', CASUALTIES.BURIED AS 'Casualty Place of Burial', FEATURE_TYPES.FEAT_TYPE AS 'Feature Type', FEATURE_TYPES.DESCR AS 'Feature Type Description', PLACES.LONGITUDE, PLACES.LATITUDE FROM NAMES JOIN NAME_PLACES ON NAMES.NAME_ID = NAME_PLACES.NAME_ID JOIN PLACES ON PLACES.PLACE_ID = NAME_PLACES.PLACE_ID LEFT JOIN CASUALTIES ON NAMES.CASUALTY_ID = CASUALTIES.CASUALTY_ID LEFT JOIN FEATURE_TYPES ON PLACES.FEAT_CODE = FEATURE_TYPES.FEAT_CODE"; //Message box brought about and alternative SELECT run. if (chkInactive.Checked) { string message = "Are you sure you want to see INACTIVE NAMES"; string caption = "WARNING!"; MessageBoxButtons buttons = MessageBoxButtons.OKCancel; MessageBoxIcon icon = MessageBoxIcon.Warning; DialogResult result; result = MessageBox.Show(message, caption, buttons, icon); if (result == System.Windows.Forms.DialogResult.OK) { basicQuery += " WHERE NAME_PLACES.ACT_TO != '12/31/2200' "; } else { basicQuery += " WHERE NAME_PLACES.ACT_TO = '12/31/2200' "; } } else { basicQuery += " WHERE NAME_PLACES.ACT_TO = '12/31/2200' "; } //Radiobutton Maps is checked. if (radMaps.Checked == true && !String.IsNullOrWhiteSpace(enteredName)) { string m250 = enteredName.Substring(0, 3); basicQuery += " AND PLACES.MS250 = @ms250"; ConnectionClass.AddParam("ms250", m250); dgvSearch_SelectionChanged(sender, e); } //Radiobutton Maps is checked and more than 3 characters entered. if (radMaps.Checked == true && !String.IsNullOrWhiteSpace(enteredName.Substring(3))) { string m50 = enteredName.Substring(3); basicQuery += " AND PLACES.MS50 = @ms50"; ConnectionClass.AddParam("ms50", m50); } //Radiobutton Location is checked. if (radLocation.Checked && !String.IsNullOrWhiteSpace(txtLatDeg.Text) && !String.IsNullOrWhiteSpace(txtLatMin.Text) && !String.IsNullOrWhiteSpace(txtLatSec.Text) && !String.IsNullOrWhiteSpace(txtLongDeg.Text) && !String.IsNullOrWhiteSpace(txtLongMin.Text) && !String.IsNullOrWhiteSpace(txtLongSec.Text)) { basicQuery += " AND PLACES.LAT_DEG = @LatDeg"; basicQuery += " AND PLACES.LAT_MIN = @LatMin"; basicQuery += " AND PLACES.LAT_SEC = @LatSec"; basicQuery += " AND PLACES.LONG_DEG = @LongDeg"; basicQuery += " AND PLACES.LONG_MIN = @LongMin"; basicQuery += " AND PLACES.LONG_SEC = @LongSec"; ConnectionClass.AddParam("LatDeg", txtLatDeg.Text); ConnectionClass.AddParam("LatMin", txtLatMin.Text); ConnectionClass.AddParam("LatSec", txtLatSec.Text); ConnectionClass.AddParam("LongDeg", txtLongDeg.Text); ConnectionClass.AddParam("LongMin", txtLongMin.Text); ConnectionClass.AddParam("LongSec", txtLongSec.Text); dgvSearch_SelectionChanged(sender, e); } //Radiobutton Name is checked. if (radName.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " AND NAMES.NAME_ACTUAL LIKE @name + '%'"; ConnectionClass.AddParam("name", enteredName); dgvSearch_SelectionChanged(sender, e); } //Radiobutton FID is checked. if (radFID.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " AND NAME_PLACES.FEATURE_ID = @ident"; ConnectionClass.AddParam("ident", enteredName); dgvSearch_SelectionChanged(sender, e); } //Radiobutton Status is checked. if (radStatus.Checked && !String.IsNullOrWhiteSpace(enteredName)) { basicQuery += " AND NAME_PLACES.STATUS_CODE = @statuscode"; ConnectionClass.AddParam("statuscode", enteredName); dgvSearch_SelectionChanged(sender, e); } //poulate the "Virtual" table according to the choice of radiobutton and entered "parameter" bds = ConnectionClass.getSQLData(basicQuery).Tables[0]; //Set the gridview's datasource. dgvSearch.DataSource = bds; //Make only name column visible for (int i = 1; i < dgvSearch.ColumnCount; i++) { dgvSearch.Columns[i].Visible = false; } //Set the Column's size matching to a gridview. dgvSearch.Columns[0].Width = dgvSearch.Width; }
/// <summary> /// Method for Features Combobox. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void cboFeature_SelectedIndexChanged(object sender, EventArgs e) { try { //A block to build a virtual combotable to be used by labels and gridview. string basicQuery = @"SELECT NAMES.NAME_ACTUAL AS 'Geographical Name', NAME_PLACES.FEATURE_ID AS 'Unique National Identifier', NAME_PLACES.DATE_CH AS 'Date Changed', NAME_PLACES.STATUS_CODE AS 'Status', NAMES.CASUALTY AS 'Casualty', NAME_PLACES.ACT_FROM AS 'Act From', NAME_PLACES.ACT_TO AS 'Act To', NAME_PLACES.ACT_TO AS 'Act To', PLACES.FEAT_CODE AS 'Feature Code', PLACES.MS250 AS 'NTS 250000 Map Sheet', PLACES.MS50 AS 'NTS 50000 Submap Sheet', PLACES.LAT_DEG AS 'LATITUDE Degrees', PLACES.LAT_MIN AS 'LATITUDE Minutes', PLACES.LAT_SEC AS 'LATITUDE Seconds', PLACES.LONG_DEG AS 'LONGITUDE Degrees', PLACES.LONG_MIN AS 'LONGITUDE Minutes', PLACES.LONG_SEC AS 'LONGITUDE Seconds', CASUALTIES.COMMUNITY AS 'Casualty Hometown', CASUALTIES.REG_NO AS 'Casualty Regimental Number', CASUALTIES.RANK_CASUALTY AS 'Casualty Rank', CASUALTIES.SURNAME AS 'Casualty Surname', CASUALTIES.GIVNAME AS 'Casualty Given Name', CASUALTIES.DATE_DECEASED AS 'Casualty Date of Death', CASUALTIES.SERVED AS 'Casualty Regiment', CASUALTIES.BURIED AS 'Casualty Place of Burial', FEATURE_TYPES.FEAT_TYPE AS 'Feature Type', FEATURE_TYPES.DESCR AS 'Feature Type Description', PLACES.LONGITUDE, PLACES.LATITUDE FROM NAMES JOIN NAME_PLACES ON NAMES.NAME_ID = NAME_PLACES.NAME_ID JOIN PLACES ON PLACES.PLACE_ID = NAME_PLACES.PLACE_ID LEFT JOIN CASUALTIES ON NAMES.CASUALTY_ID = CASUALTIES.CASUALTY_ID LEFT JOIN FEATURE_TYPES ON PLACES.FEAT_CODE = FEATURE_TYPES.FEAT_CODE"; //Message box brought about and alternative SELECT run. if (chkInactive.Checked) { string message = "Are you sure you want to see INACTIVE NAMES"; string caption = "WARNING!"; MessageBoxButtons buttons = MessageBoxButtons.OKCancel; MessageBoxIcon icon = MessageBoxIcon.Warning; DialogResult result; result = MessageBox.Show(message, caption, buttons, icon); if (result == System.Windows.Forms.DialogResult.OK) { basicQuery += " WHERE NAME_PLACES.ACT_TO != '12/31/2200' "; } else { basicQuery += " WHERE NAME_PLACES.ACT_TO = '12/31/2200' "; } } else { basicQuery += " WHERE NAME_PLACES.ACT_TO = '12/31/2200' "; } //Radiobutton Feature selected. if (radFeature.Checked) { basicQuery += " AND FEATURE_TYPES.FEAT_TYPE = @feattype"; ConnectionClass.AddParam("feattype", cboFeature.SelectedValue.ToString()); } //Set the gridview's datasource. bds = ConnectionClass.getSQLData(basicQuery).Tables[0]; dgvSearch.DataSource = bds; for (int i = 1; i < dgvSearch.ColumnCount; i++) { dgvSearch.Columns[i].Visible = false; } dgvSearch.Columns[0].Width = dgvSearch.Width; } catch (Exception) { //Always one exception, 'no pointer reference on form closing'. } }