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'. } }