Hi All,
I need some help here, I am trying to display some data in gridview with the data rotated, meaning first to datatable and then rotate the data and bind that to gridview, based on some criteria, however the code doesnt seem to work fine, the two inputs are 1.Scalar Variable @Filter which is a text value of a dropdownlist.
2. Session["Member_Id"]. However if I manually add these 2 values to the code i get desired output. for example:
SqlDataSource1.SelectCommand = "Select [Fiscal Week],CAST(CAST([CE%] AS NUMERIC(36,2)) AS varchar(50))+'%' AS [CE%],CAST(CAST([XPR%] AS NUMERIC(36,2)) AS varchar(50))+'%'[XPR%],CAST(CAST([RAR%] AS NUMERIC(36,2)) AS varchar(50))+'%'[RAR%],CAST(CAST([Quick Call Rate] AS numeric(36,2)) AS VARCHAR(50)) + ' %' AS [Quick Call Rate],CAST(CAST([IB Log Rate] AS NUMERIC(36,2)) AS varchar(50))+'%'[IB Log Rate %],CAST(CAST([IB Compliance] AS numeric(36,2)) AS VARCHAR(50)) + '%' AS [IB Compliance],CAST(CAST([OB log rate] AS numeric(36,2)) AS VARCHAR(50)) + ' %' AS [OB Log Rate %],CAST(CAST([OB Compliance] AS numeric(36,2)) AS VARCHAR(50)) + '%' AS [OB Compliance],CAST(CAST([Email Bounce %] AS numeric(36,2)) AS VARCHAR(50)) + ' %' AS [Email Bounce %],CAST(CAST([Good Email Capture %(SR Based)] AS numeric(36,2)) AS VARCHAR(50)) + '%' AS [Good Email Capture %(SR Based)],CAST(CAST([AHT] AS numeric(36,2)) AS VARCHAR(50)) as [AHT],CAST(CAST([CRW] AS numeric(36,2)) AS VARCHAR(50)) as[CRW],CAST(CAST([Target] AS numeric(36,2)) AS VARCHAR(50)) as[Target] from Dashboard_Final Where Fiscal_Month='Feb' AND Badge=123456";
Any help or suggestions appreciated
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.Data;
using System.Configuration;
using System.Security.Principal;
namespace WebApp
{
public partial class Agentdashboard : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SqlDataSource1.SelectCommand = "Select [Fiscal Week],CAST(CAST([CE%] AS NUMERIC(36,2)) AS varchar(50))+'%' AS [CE%],CAST(CAST([XPR%] AS NUMERIC(36,2)) AS varchar(50))+'%'[XPR%],CAST(CAST([RAR%] AS NUMERIC(36,2)) AS varchar(50))+'%'[RAR%],CAST(CAST([Quick Call Rate] AS numeric(36,2)) AS VARCHAR(50)) + ' %' AS [Quick Call Rate],CAST(CAST([IB Log Rate] AS NUMERIC(36,2)) AS varchar(50))+'%'[IB Log Rate %],CAST(CAST([IB Compliance] AS numeric(36,2)) AS VARCHAR(50)) + '%' AS [IB Compliance],CAST(CAST([OB log rate] AS numeric(36,2)) AS VARCHAR(50)) + ' %' AS [OB Log Rate %],CAST(CAST([OB Compliance] AS numeric(36,2)) AS VARCHAR(50)) + '%' AS [OB Compliance],CAST(CAST([Email Bounce %] AS numeric(36,2)) AS VARCHAR(50)) + ' %' AS [Email Bounce %],CAST(CAST([Good Email Capture %(SR Based)] AS numeric(36,2)) AS VARCHAR(50)) + '%' AS [Good Email Capture %(SR Based)],CAST(CAST([AHT] AS numeric(36,2)) AS VARCHAR(50)) as [AHT],CAST(CAST([CRW] AS numeric(36,2)) AS VARCHAR(50)) as[CRW],CAST(CAST([Target] AS numeric(36,2)) AS VARCHAR(50)) as[Target] from Dashboard_Final Where Fiscal_Month=@Filter AND Badge='"+Session["Member_Id"]+"'";
SqlDataSource1.SelectParameters.Add("Filter", FMth.SelectedItem.Text);
SqlDataSource1.SelectCommand.Replace("'", "''");
DataTable dt = (SqlDataSource1.Select(DataSourceSelectArguments.Empty) as DataView).ToTable();
ViewState["dt"] = dt;
BindGrid(dt, false);
}
GridView1.Visible = false;
}
private void BindGrid(DataTable dt, bool rotate)
{
this.GridView1.DataSourceID = "";
GridView1.ShowHeader = !rotate;
this.GridView1.DataSource = dt;
GridView1.DataBind();
if (rotate)
{
foreach (GridViewRow row in GridView1.Rows)
{
row.Cells[0].CssClass = "header1";
}
}
}
protected void grid_action(object sender, EventArgs e)
{
GridView1.Visible = true;
DataTable dt = (DataTable)ViewState["dt"];
if ((sender as Button).CommandArgument == "1")
{
btnConvert1.Visible = true;
btnConvert2.Visible = false;
DataTable dt2 = new DataTable();
for (int i = 0; i <= dt.Rows.Count; i++)
{
dt2.Columns.Add("", typeof(string));
}
for (int i = 0; i < dt.Columns.Count; i++)
{
dt2.Rows.Add();
dt2.Rows[i][0] = dt.Columns[i].ColumnName;
}
for (int i = 0; i < dt.Columns.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
dt2.Rows[i][j + 1] = dt.Rows[j][i];
}
}
BindGrid(dt2, true);
}
else
{
btnConvert1.Visible = true;
btnConvert2.Visible = false;
BindGrid(dt, false);
}
}
protected void Page_LoadComplete(object sender, EventArgs e)
{
if (!IsPostBack)
{
FYr.AppendDataBoundItems = true;
String strConnString = ConfigurationManager.ConnectionStrings["BLRUSCTSConnectionString"].ConnectionString;
String strQuery = "select Distinct Fiscal_Year from Dashboard_Final";
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
try
{
con.Open();
FYr.DataSource = cmd.ExecuteReader();
FYr.DataTextField = "Fiscal_Year";
FYr.DataValueField = "FISCAL_YEAR";
FYr.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
if (IsPostBack)
{
pnlDBdata.Visible = true;
}
lblDate.Text = DateTime.Now.ToString("dddd, dd MMMM, yyyy");
lblYear.Text = DateTime.Now.ToString("yyyy");
String Comp_Name = System.Net.Dns.GetHostName();
if (Session["Member_ID"] == null)
{
pnlLogin_Form.Visible = true;
lblLogin_Info.Text = "";
pnlLogin_Details.Visible = false;
pnlSession_Expired.Visible = true;
filters.Visible = false;
}
else
{
pnlLogin_Form.Visible = false;
lblLogin_Info.Text = " Hi " + Session["Member_Name"] + ", You Previously Logged in: " + "from Machine" + " " + Comp_Name + " " + "on " + Session["Member_PreviousLogin"];
pnlLogin_Details.Visible = true;
filters.Visible = true;
pnlSession_Expired.Visible = false;
}
}
protected void cmdLogin_Click(object sender, ImageClickEventArgs e)
{
bool Login_Verify = true;
if (username.Text.Trim() == "")
{
Login_Verify = false;
}
else
{
int Num;
bool isNum = int.TryParse(username.Text.ToString(), out Num);
if (!isNum)
{
Login_Verify = false;
}
}
if (password.Text.Trim() == "")
{
Login_Verify = false;
}
if (Login_Verify == true)
{
string CS = ConfigurationManager.ConnectionStrings["BLRUSCTSConnectionString"].ConnectionString;
using (SqlConnection Conn = new SqlConnection(CS))
{
SqlCommand CMD = new SqlCommand();
CMD.CommandText = "Select Count(Badge) From Emp_Login Where Badge=@Badge";
CMD.Parameters.AddWithValue("@Badge", username.Text);
CMD.Connection = Conn;
Conn.Open();
int Rows_Count = (int)CMD.ExecuteScalar();
if (Rows_Count > 0)
{
CMD.CommandText = "Select Badge, PWD, LoB, Previous_Login, Name From Emp_Login Where Badge=@Badge1";
CMD.Parameters.AddWithValue("@Badge1", username.Text);
using (SqlDataReader RDR = CMD.ExecuteReader())
{
if (RDR.Read())
{
if (password.Text == Convert.ToString(RDR["PWD"]))
{
Session["Member_Id"] = Convert.ToInt32(RDR["Badge"]);
Session["Member_Name"] = Convert.ToString(RDR["Name"]);
Session["Member_LoB"] = Convert.ToString(RDR["LoB"]);
Session["Member_PreviousLogin"] = Convert.ToString(RDR["Previous_Login"]);
}
else
{
Login_Verify = false;
lblLogin_Msg.Text = "Login Attempt Failed! Please Try Again.";
}
}
else
{
Login_Verify = false;
lblLogin_Msg.Text = "Login Attempt Failed! Please Try Again.";
}
}
if (Login_Verify)
{
CMD.CommandText = "Update Emp_Login Set Previous_Login=Current_Login, Current_Login=GETDATE() Where Badge=@EmpID";
CMD.Parameters.AddWithValue("@EmpID", username.Text);
CMD.ExecuteNonQuery();
}
}
else
{
lblLogin_Msg.Text = "Login Attempt Failed! Please Try Again.";
}
}
}
else
{
lblLogin_Msg.Text = "Login Attempt Failed! Please Try Again.";
}
}
protected void FYr_SelectedIndexChanged(object sender, EventArgs e)
{
FQtr.Items.Clear();
FQtr.Items.Add(new ListItem("--Select Fiscal Quarter--", ""));
FMth.Items.Clear();
FMth.Items.Add(new ListItem("--Select Fiscal Month--", ""));
FQtr.AppendDataBoundItems = true;
String strConnString = ConfigurationManager.ConnectionStrings["BLRUSCTSConnectionString"].ConnectionString;
String strQuery = "select DISTINCT FQID, FISCAL_QUARTER from DASHBOARD_FINAL " + "where FISCAL_YEAR=@ContinentID";
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@ContinentID", FYr.SelectedItem.Value);
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
try
{
con.Open();
FQtr.DataSource = cmd.ExecuteReader();
FQtr.DataTextField = "FISCAL_QUARTER";
FQtr.DataValueField = "FQID";
FQtr.DataBind();
if (FQtr.Items.Count > 1)
{
FQtr.Enabled = true;
}
else
{
FQtr.Enabled = false;
FMth.Enabled = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
protected void FQtr_SelectedIndexChanged(object sender, EventArgs e)
{
FMth.Items.Clear();
FMth.Items.Add(new ListItem("--Select Fiscal Month--", ""));
FMth.AppendDataBoundItems = true;
String strConnString = ConfigurationManager.ConnectionStrings["BLRUSCTSConnectionString"].ConnectionString;
String strQuery = "select DISTINCT FQID, FISCAL_MONTH from DASHBOARD_FINAL where FQID=@CountryID";
strQuery.Replace("'", "''");
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.Parameters.AddWithValue("@CountryID", FQtr.SelectedItem.Value);
cmd.CommandType = CommandType.Text;
cmd.CommandText = strQuery;
cmd.Connection = con;
try
{
con.Open();
FMth.DataSource = cmd.ExecuteReader();
FMth.DataTextField = "FISCAL_MONTH";
FMth.DataValueField = "FISCAL_MONTH";
FMth.DataBind();
if (FMth.Items.Count > 1)
{
FMth.Enabled = true;
}
else
{
FMth.Enabled = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
}
protected void FMth_SelectedIndexChanged(object sender, EventArgs e)
{
lblResults.Text = "You Selected " +
FYr.SelectedItem.Text + " -----> " +
FQtr.SelectedItem.Text + " -----> " +
FMth.SelectedItem.Text;
}
}
}
Submit an article or tip