locked
selecting data by session username and Job Role RRS feed

  • Question

  • User-1901014284 posted

    I am trying to select data from a table where the session userName = the data UserName but also where the Session jobRole = the Created_By_ID in the table. I have submitted the code I am using below.

    using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
    var query = "select Expense_Date, Visit_Call_Other, Justification, Work_Time_DurationHours, Total_Mileage, Created_By FROM Expense_Entry_Form " +
    "where Created_By = @userName " +
    "AND Created_By_ID = @jobRole";

    cmd = new SqlCommand(query, connection);
    da = new SqlDataAdapter(cmd);

    cmd.Parameters.AddWithValue("@userName", Session["userName"]);
    cmd.Parameters.AddWithValue("@jobRole", Session["jobRole"].Equals(3));

    dt.Clear();
    da.Fill(dt);

    if (dt.Rows.Count > 0)
    {
    ExpenseGridView.DataSource = dt;
    ExpenseGridView.DataBind();
    }

    Any help would be greatly appreciated.

    Jonny

    Wednesday, May 10, 2017 12:02 PM

All replies

  • User2103319870 posted

    You didnt mention the issue or error you are getting. Please provide details on that.

    However on quick review of your code, I noticed that you have not opened the connection. You need to do that before fetching the data

    Please try with below code

     using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                    {
                        //Open the connection
                        connection.Open();
    
                        var query = "select Expense_Date, Visit_Call_Other, Justification, Work_Time_DurationHours, Total_Mileage, Created_By FROM Expense_Entry_Form " +
                        "where Created_By = @userName " +
                        "AND Created_By_ID = @jobRole";
    
                        cmd = new SqlCommand(query, connection);
                        
    
                        da = new SqlDataAdapter(cmd);
    
                        cmd.Parameters.AddWithValue("@userName", Session["userName"]);
                        cmd.Parameters.AddWithValue("@jobRole", Session["jobRole"].Equals(3));
    
                        dt.Clear();
                        da.Fill(dt);
    
                        if (dt.Rows.Count > 0)
                        {
                            ExpenseGridView.DataSource = dt;
                            ExpenseGridView.DataBind();
                        }
                    }

    Wednesday, May 10, 2017 12:07 PM
  • User-1901014284 posted

    Hi A2H,

    Thank you for your quick response, I have opened the connection as you have mentioned but still no luck. No errors appears when running the code, when looking at the data grid the data requested does not display within the data grid view.

    Many thanks for you assistance.

    Jonny

    Wednesday, May 10, 2017 12:47 PM
  • User753101303 posted

    Hi,

    For @jobRole you pass currently Session["jobRole"].Equals(3) rather than maybe Session["jobRole"] as intended.

    If the grid is empty (better than "the data requested does not display" which could still mean you do see something else than what you expected), it means that no data are returned and that the SQL statement doesn't select anything because of a wrong criteria.

    Wednesday, May 10, 2017 12:53 PM
  • User-1901014284 posted

    Thank you for your response,

    I have changed the code as mentioned (removing the .Equals(3)) thank you for this. My only issue is now against an employee all records within the database display against every client. I would like only records related to the client I have opened to be displayed. Please see below my code.

    using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
    //Open the connection
    connection.Open();

    var query = "select Expense_Date, Visit_Call_Other, Justification, Work_Time_DurationHours, Total_Mileage, Created_By FROM Expense_Entry_Form " +
    "where Client_ID=" + Request.QueryString["id"] +
    "AND Created_By = @userName " +
    "OR jobRoleID = @jobRole";

    cmd = new SqlCommand(query, connection);


    da = new SqlDataAdapter(cmd);

    cmd.Parameters.AddWithValue("@userName", Session["userName"]);
    cmd.Parameters.AddWithValue("@jobRole", Session["jobRole"]);

    dt.Clear();
    da.Fill(dt);

    if (dt.Rows.Count > 0)
    {
    ExpenseGridView.DataSource = dt;
    ExpenseGridView.DataBind();
    }
    }

    Wednesday, May 10, 2017 2:57 PM
  • User-707554951 posted

    Hi onnygareth30,

    No errors appears when running the code, when looking at the data grid the data requested does not display within the data grid view.

    I  supposed that the reason for your problem above is  that there is nod data in dt. 

    In other word, there is no matched record in database based on your condition in where clause.

    I suggest you could try to use the following code:

    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
        string  query = "select Expense_Date, Visit_Call_Other, Justification, Work_Time_DurationHours, Total_Mileage, Created_By FROM Expense_Entry_Form " +
    "where Created_By = @userName " +
    "AND Created_By_ID = @jobRole";
                string constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Parameters.AddWithValue("@userName", Session["userName"].ToString());
                            cmd.Parameters.AddWithValue("@jobRole", Session["jobRole"].Equals(3).ToString());
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (DataTable dt = new DataTable())
                            {
                                sda.Fill(dt);
                                GridView1.DataSource = dt;
                                GridView1.DataBind();
                              
                            }
                        }
                    }
                }

    If the some problem still arose. I suggest you could set break point to debug code. 

    1. you need to make sure that  the value  of Session["userName"].ToString() and  Session["jobRole"].Equals(3).ToString().

    2.Then go to your database to check the if exist  a record with  username equal Session["userName"].ToString() and jobRole equal to Session["jobRole"].Equals(3).ToString().

    Best regards

    Cathy

    Thursday, May 11, 2017 10:35 AM
  • User-1901014284 posted

    Hi Cathy,

    I have resolved the issue with the code below. Thank you for your response it is greatly appreciated.

    using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))

    {
    //Open the connection
    connection.Open();

    var query = @"SELECT Expense_Date,
    Visit_Call_Other,
    Justification,
    Work_Time_DurationHours,
    Total_Mileage,
    Created_By,
    Client_ID
    FROM Expense_Entry_Form
    WHERE
    (Client_ID = @ClientId AND Created_By = @userName)
    OR
    (Client_ID = @ClientId AND Created_By_ID = @jobRole)";

    cmd = new SqlCommand(query, connection);


    da = new SqlDataAdapter(cmd);

    cmd.Parameters.AddWithValue("@userName", Session["userName"]);
    cmd.Parameters.AddWithValue("@jobRole", Session["jobRole"]);
    cmd.Parameters.AddWithValue("@ClientId", Request.QueryString["id"]);

    dt.Clear();
    da.Fill(dt);

    if (dt.Rows.Count > 0)
    {
    ExpenseGridView.DataSource = dt;
    ExpenseGridView.DataBind();
    }

    Thursday, May 11, 2017 12:42 PM