using System;

using System.Collections;

using System.ComponentModel;

using System.ComponentModel.Design;

using System.Data;

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.Data.Odbc;

namespace UACSEmployee

{

/// <summary>

/// Summary description for GetDescription.

/// </summary>

public class GetDescription : System.Web.UI.Page

{

protected System.Web.UI.WebControls.DataGrid DataGrid1;

protected System.Web.UI.WebControls.DataGrid DataGrid2;

protected System.Web.UI.WebControls.DropDownList DropDownList1;

private OdbcConnection mConn;

protected System.Web.UI.WebControls.Button ExecuteButton;

protected System.Web.UI.WebControls.Panel ResultsPanel;

protected System.Web.UI.WebControls.Label CatCodeLabel;

protected System.Web.UI.WebControls.Image UAImage;

private String mCatCode;

private void Page_Load(object sender, System.EventArgs e)

{

// Put user code to initialize the page here

if(!IsPostBack)

{

try

{

mConn = new OdbcConnection("DSN=webuacs;UID=bluefish;PWD=111111;");

mConn.ConnectionTimeout = 60;

mConn.Open();

string sql = "select distinct(CATEGORY_CODE) from JOB_CATEGORY";

OdbcCommand myComm = new OdbcCommand(sql, mConn);

OdbcDataAdapter da = new OdbcDataAdapter(myComm);

DataSet ds = new DataSet();

da.Fill(ds, "JOB_CATEGORY");

DropDownList1.DataSource = ds.Tables["JOB_CATEGORY"];

DropDownList1.DataBind();

DropDownList1.Visible = true;

}

catch(OdbcException ex)

{

Response.Write(ex.Message);

}

finally

{

mConn.Close();

}

}

}

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)

{

//

// CODEGEN: This call is required by the ASP.NET Web Form Designer.

//

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.ExecuteButton.Click += new System.EventHandler(this.ExecuteButton_Click);

this.MainButton.Click += new System.EventHandler(this.MainButton_Click);

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

private void ExecuteButton_Click(object sender, System.EventArgs e)

{

string tableName1 = "JOB_CATEGORY";

string tableName2 = "JOB_DESCRIPTION";

mCatCode = DropDownList1.SelectedItem.Value;

DataRow resultRow;

try

{

mConn = new OdbcConnection("DSN=webuacs;UID=bluefish;PWD=111111;");

mConn.ConnectionTimeout = 60;

mConn.Open();

ResultsPanel.Visible = true;

OdbcCommand commandHandle = new OdbcCommand( "{call get_description(?)}", mConn );

commandHandle.CommandType = CommandType.StoredProcedure;

OdbcParameterCollection paramHandle = commandHandle.Parameters;

OdbcParameter myNewParameter = paramHandle.Add("@category", OdbcType.VarChar);

myNewParameter.Value = mCatCode;

OdbcDataReader resultDR = commandHandle.ExecuteReader();

DataSet resultDS = new DataSet();

resultDS.Tables.Add(new DataTable(tableName1));

resultDS.Tables[tableName1].Columns.Add

(new DataColumn("job_code", System.Type.GetType("System.Int32")));

resultDS.Tables[tableName1].Columns.Add

(new DataColumn("category", System.Type.GetType("System.String")));

resultDS.Tables.Add(new DataTable(tableName2));

resultDS.Tables[tableName2].Columns.Add

(new DataColumn("job_code", System.Type.GetType("System.Int32")));

resultDS.Tables[tableName2].Columns.Add

(new DataColumn("category", System.Type.GetType("System.Int32")));

resultDS.Tables[tableName2].Columns.Add

(new DataColumn("salaried", System.Type.GetType("System.Boolean")));

resultDS.Tables[tableName2].Columns.Add

(new DataColumn("hourly", System.Type.GetType("System.Boolean")));

resultDS.Tables[tableName2].Columns.Add

(new DataColumn("minimum", System.Type.GetType("System.Double")));

resultDS.Tables[tableName2].Columns.Add

(new DataColumn("maximum", System.Type.GetType("System.Double")));

resultDS.Tables[tableName2].Columns.Add

(new DataColumn("description", System.Type.GetType("System.String")));

while (resultDR.Read())

{

resultRow = resultDS.Tables[tableName1].NewRow();

resultRow["job_code"] = resultDR.GetInt32(0);

resultRow["category"] = resultDR.GetString(1);

resultDS.Tables[tableName1].Rows.Add(resultRow);

}

resultDR.NextResult();

while (resultDR.Read())

{

resultRow = resultDS.Tables[tableName2].NewRow();

resultRow["job_code"] = resultDR.GetInt32(0);

resultRow["category"] = resultDR.GetString(1);

resultRow["salaried"] = resultDR.GetByte(2)==1?true:false;

resultRow["hourly"] = resultDR.GetByte(3)==1?true:false;

resultRow["minimum"] = resultDR.GetDouble(4);

resultRow["maximum"] = resultDR.GetDouble(5);

if( resultDR.IsDBNull(6) )

resultRow["description"] = "";

else

resultRow["description"] = resultDR.GetString(6);

resultDS.Tables[tableName2].Rows.Add(resultRow);

}

DataGrid1.DataSource = resultDS.Tables[tableName1];

DataGrid2.DataSource = resultDS.Tables[tableName2];

DataGrid1.DataBind();

DataGrid2.DataBind();

DataGrid1.Visible = true;

DataGrid2.Visible = true;

CatCodeLabel.Text = DropDownList1.SelectedItem.Text;

}

catch(OdbcException ex)

{

Response.Write(ex.Message);

}

finally

{

mConn.Close();

}

}

}

}