Click here to Skip to main content
15,066,928 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
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

C#
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.";
            }
        }

        //Start Filters//

        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;
        }
        //END Filters//
    }
}
Posted
Updated 6-Oct-15 1:00am
v4
Comments
Krunal Rohit 6-Oct-15 0:54am
   
First, your code has a SQL Injection threat.
And second, did you debug it, and see what exactly happened ?

-KR
JagzKrish 6-Oct-15 7:16am
   
SQL Injection threat I, understand but not really worried as this is going to be an intranet based, and any data leakage is really not a concern. However will try to curtail that, thank you for pointing it out. When I debug the code on GRID_ACTION only the Header row gets converted to column and no data corresponding to that shows up(no more columns, just one column). If I run the code with the criteria hard coded like "Where Fiscal_Month='Feb' AND Badge=123456" then it displays all the columns correctly with the data, from this I can understand that I am doing something wrong in passing the parameters, tried all possible solutions googling, but no luck.
JagzKrish 7-Oct-15 12:33pm
   
I am still not able to figure this out, Kindly help
Naveen.Sanagasetti 6-Oct-15 2:09am
   
Is gridview is AutoGenerate Columns or not.? check this once and debug the code and while binding grid check the datasource and see the list of columns over their.
JagzKrish 6-Oct-15 7:11am
   
AutoGenerate columns is set to true, and this is what is happening, if i execute the same code with the criteria hard coded like "Where Fiscal_Month='Feb' AND Badge=123456" then it displays all the columns correctly with the data, from this I can understand that I am doing something wrong in passing the parameters, tried all possible solutions googling, but no luck.

1 solution

debug and check the final query and then filter down the columns you required and you can see the number of columns returned.
   
Comments
JagzKrish 6-Oct-15 7:02am
   
If I run the code with the criteria hard coded like "Where Fiscal_Month='Feb' AND Badge=123456" then it displays all the columns correctly with the data, from this I can understand that I am doing something wrong in passing the parameters, tried all possible solutions googling, but no luck.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900