locked
Disply only relevant company data in Gridview RRS feed

  • Question

  • User-1901014284 posted

    Hi,

    My issue with the code below is that i records within the database are being displayed against every client. I would like only records related to the individual clients I have opened to be displayed within the gridview. 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();
    }
    }

    Any help would be greatly appreciated.

    Many thanks

    Jonny

    Wednesday, May 10, 2017 4:42 PM

Answers

  • User475983607 posted

    My issue with the code below is that i records within the database are being displayed against every client. I would like only records related to the individual clients I have opened to be displayed within the gridview. Please see below my code.

    That's how the SQL script is written which drives the results

    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";

    The filter (where clause) says, give me all the records where Client_ID is {...} and Created_By is {...} ALSO give me all the records where jobRoleID is {...}  

    Maybe, you want this?

    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 = @ClientId AND Created_By = @userName)
    					OR  
    					(Client_ID = @ClientId AND jobRoleID = @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();
    	}
    }

    Give me all the records where client id is {...} and created by is {...} ALSO give me all the records where client id is {...} and jobRoleId is {...}

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 10, 2017 9:41 PM

All replies

  • User475983607 posted

    My issue with the code below is that i records within the database are being displayed against every client. I would like only records related to the individual clients I have opened to be displayed within the gridview. Please see below my code.

    That's how the SQL script is written which drives the results

    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";

    The filter (where clause) says, give me all the records where Client_ID is {...} and Created_By is {...} ALSO give me all the records where jobRoleID is {...}  

    Maybe, you want this?

    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 = @ClientId AND Created_By = @userName)
    					OR  
    					(Client_ID = @ClientId AND jobRoleID = @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();
    	}
    }

    Give me all the records where client id is {...} and created by is {...} ALSO give me all the records where client id is {...} and jobRoleId is {...}

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 10, 2017 9:41 PM
  • User-1901014284 posted

    Thank you for your assistance, I added Client_ID to SQL SELECT query and it run  perfectly.

    Thank you very much :)

    Jonny

    Thursday, May 11, 2017 10:51 AM