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