selecting data by session username and Job Role

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

Answer 1

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();
                    }
                }

Answer 2

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

Answer 3

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.

Answer 4

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();
}
}

Answer 5

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

Answer 6

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();
}