ASP.NET

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Xml;

namespace HIV
{
/// <summary>
/// Summary description for run_specific_query.
/// </summary>
public class run_specific_query : System.Web.UI.Page
{
protected HIV.Controls.NavMenu navMenu;
protected HIV.Controls.NavSubMenu navSubMenu;
protected System.Web.UI.HtmlControls.HtmlGenericControl message;
protected System.Web.UI.WebControls.Label resultsLabel;
protected System.Web.UI.WebControls.DropDownList Specific_Query_DDL;
protected System.Web.UI.WebControls.DropDownList mlno_DDL;
protected System.Web.UI.WebControls.TextBox date_TB;
protected System.Web.UI.WebControls.CompareValidator dateValidator;
protected System.Web.UI.WebControls.DataGrid resultsDatagrid;
protected System.Web.UI.WebControls.Button submitButton;
protected System.Web.UI.WebControls.Label data_src;
protected System.Web.UI.WebControls.LinkButton exportLinkbutton;

private void Page_Load(object sender, System.EventArgs e)
{
navMenu.SelectedMainItem = HIV.Controls.NavMenu.MainItems.QUERY;
navSubMenu.SelectedMainItem = HIV.Controls.NavMenu.MainItems.QUERY;
navSubMenu.SelectedSubItem = HIV.Controls.NavSubMenu.SubItems.RUN_SPECIFIC_QUER Y;

getMLNO();

//if (!this.IsPostBack)
//LoadQueryLinks();
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
BuildQueryForm(); [This function/method is calling the submit button function]
InitializeComponent();

base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
//this.submitButton.Click += new System.EventHandler(this.submitButton_Click);
//this.Load += new System.EventHandler(this.Page_Load);
this.exportLinkbutton.Click += new System.EventHandler(this.exportLinkbutton_Click);
this.Load += new System.EventHandler(this.Page_Load); [after the data is pulled from the DB the export to excel button should show up, so I have implemented this here]

}
#endregion

protected void BuildQueryForm()
{
try
{
this.submitButton.Click += new System.EventHandler(this.submitButton_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
catch(Exception e)
{
message.InnerHtml = "Exception: " + e.ToString();
}
}

private void getMLNO()
{
OleDbConnection connection = new OleDbConnection(HIV.Database.DataConstants.CONNECT ION_STRING);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand("SELECT DISTINCT re_MLNO FROM ml_hiv_status", connection);

DataSet ds = new DataSet();
adapter.SelectCommand = command;

if (adapter.Fill(ds) > 0)
{
int adp= adapter.Fill(ds);
DataView view = ds.Tables[0].DefaultView;
mlno_DDL.DataSource = view;
mlno_DDL.DataValueField="re_MLNO";
mlno_DDL.DataBind();
resultsLabel.Visible= true;
//resultsLabel.Text ="TEST MESSAGE: You Have selected the DB "+ adp + Specific_Query_DDL.SelectedItem.Value;
mlno_DDL.Items.Insert(0, "ANY");
}
connection.Close();
}

private void submitButton_Click(object sender, System.EventArgs e)
{
if(Specific_Query_DDL.SelectedItem.Value=="default ")
{
resultsLabel.Visible= true;
resultsLabel.Text ="Please select a specific query from the dropdown list";
exportLinkbutton.Visible = false;
}
if(Specific_Query_DDL.SelectedItem.Value=="Resista ntL")
{
resultsLabel.Visible= true;
resultsLabel.Text ="You Have selected"+ Specific_Query_DDL.SelectedItem.Value;
}
if(Specific_Query_DDL.SelectedItem.Value=="Negativ eL")
{
resultsLabel.Visible= true;
resultsLabel.Text ="You Have selected"+ Specific_Query_DDL.SelectedItem.Value;
}
if(Specific_Query_DDL.SelectedItem.Value=="Positiv eL") [The problem is here in this if else block]
{
if(mlno_DDL.SelectedItem.Value=="ANY")
{
executePositiveListQuery_ANY(); [All the time this one is called but...]
}
else //if(mlno_DDL.SelectedItem.Value!="ANY")
{
executePositiveListQuery_MLNO(mlno_DDL.SelectedIte m.Value); [...I want this method to be called as well when I am selecting a specific patient number]
}
}
if(Specific_Query_DDL.SelectedItem.Value=="DeadL")
{
resultsLabel.Visible= true;
resultsLabel.Text ="You Have selected"+ Specific_Query_DDL.SelectedItem.Value;
}

}

public void executePositiveListQuery_ANY()
{
string sql= "SELECT DISTINCT re_MLNO FROM ml_hiv_status WHERE (re_HIV1_Status=1 AND re_HIV2_Status=1)";
try
{
OleDbConnection connection = new OleDbConnection(HIV.Database.DataConstants.CONNECT ION_STRING);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(sql, connection);

DataSet ds = new DataSet();
adapter.SelectCommand = command;

int count = adapter.Fill(ds);

if (count > 0 && count <= 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = true;
resultsLabel.Text = count.ToString() + " results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else if (count> 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = false;
resultsLabel.Text = "More than 200 results found. Please Click the Export to Excel Link to Download the Results.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else
{
resultsLabel.Text = "No results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = false;
resultsDatagrid.Visible = false;
}
connection.Close();
}
catch(Exception ex)
{
message.InnerHtml = "SQL: " + sql + "<p></p>" + ex.ToString();
}

//resultsLabel.Text ="You Have selected"+ Specific_Query_DDL.SelectedItem.Value;
}

public void executePositiveListQuery_MLNO(string selected_MLNO)
{
/*string sql= "SELECT * FROM ml_hiv_status WHERE (re_HIV1_Status=1 AND re_HIV2_Status=1)";
try
{
OleDbConnection connection = new OleDbConnection(HIV.Database.DataConstants.CONNECT ION_STRING);
OleDbDataAdapter adapter = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand(sql, connection);

DataSet ds = new DataSet();
adapter.SelectCommand = command;

int count = adapter.Fill(ds);

if (count > 0 && count <= 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = true;
resultsLabel.Text = count.ToString() + " results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else if (count> 200)
{
DataView view = ds.Tables[0].DefaultView;
//view.Sort=e.SortExpression;
resultsDatagrid.DataSource = view;
resultsDatagrid.DataBind();
resultsDatagrid.Visible = false;
resultsLabel.Text = "More than 200 results found. Please Click the Export to Excel Link to Download the Results.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = true;
}
else
{
resultsLabel.Text = "No results found.";
resultsLabel.Visible = true;
exportLinkbutton.Visible = false;
resultsDatagrid.Visible = false;
}
connection.Close();
}
catch(Exception ex)
{
message.InnerHtml = "SQL: " + sql + "<p></p>" + ex.ToString();
}*/

resultsLabel.Text ="You Have selected"+ selected_MLNO;
}

private void exportLinkbutton_Click(object sender, System.EventArgs e)
{
resultsDatagrid.Visible = true;
DataGridItem tblGrid=resultsDatagrid.Items[0];
ArrayList alLinks= new ArrayList();
TableCell TC;
LinkButton LB;

for(int i=0; i<tblGrid.Cells.Count - 1; i++)
{
TC=tblGrid.Cells[i];
if(TC.Controls.Count> 0)
{
LB=(LinkButton)TC.Controls[0];
resultsDatagrid.Items[0].Cells[i].Controls.Clear();
resultsDatagrid.Items[0].Cells[i].Text=LB.Text;
}
else
LB=new LinkButton();

alLinks.Add(LB);
}

Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
resultsDatagrid.EnableViewState = false;

System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
resultsDatagrid.RenderControl(hw);
//TextWriter TW= File.CreateText(Server.MapPath("ExportToExcel.xls" ));

Response.Write(tw.ToString());
Response.End();

for(int i=0; i<tblGrid.Cells.Count - 1; i++)
{
LB = (LinkButton)alLinks[i];
//TC=tblGrid.Rows[0].Cells[i];
resultsDatagrid.Items[0].Cells[i].Controls.Add(LB);
}
}
}
}