asp.net multiple sqlcommands in one GridView

Question

I have problem to show multiple sql command in one GridView. Maybe I don't need two sqlcommands to show from two tables but I don't know how to do. The first command is to get all employees that have vacation between two dates. The second command I am using it to retrieve dates by ID. But I don't know how to Bind them both to one GridView to show as attached image. Thank you in advance.

What I get Now is
Albert 2016-03-16
Albert 2016-03-17

Albert 2016-03-18
Johanna 2016-03-17

Johanna 2016-03-18
Eric 2016-03-18

Instead of
Albert 2016-03-16, 2016-03-17, 2016-03-18
Johanna 2016-03-17, 2016-03-18
Eric 2016-03-18
I think I have to loop between two While statment and maybe with one sqlcommand?

My code is:

    using (SqlConnection con = new SqlConnection(connection))
{
 con.Open();
 SqlCommand cmd = new SqlCommand(" SELECT distinct E.EmployeeId, E.FirstName  
 FROM Employee E INNER JOIN Vacation V ON E.EmployeeId = V.EmployeeId " +
  " WHERE ((V.Dates >= @Start AND V.Dates <= @End) ) ", con);
  cmd.Parameters.AddWithValue("@Start", (Calendar1.SelectedDates[0]).Date.ToShortDateString());
 cmd.Parameters.AddWithValue("@End", (Calendar1.SelectedDates[Calendar1.SelectedDates.Count - 1]).Date.ToShortDateString());
using (SqlDataReader dr = cmd.ExecuteReader())
 {
   while (dr.Read())
    {

 Response.Write((dr[1]).ToString() + "  "); // Cheack if retrivs Employeename
 // Now By Id I want to get all dates belong to specifik employee

SqlCommand cmd2 = new SqlCommand(" SELECT V.Dates FROM Vacation V " +
" WHERE ((V.Dates >= @Start AND V.Dates <= @End) ) ", con);
cmd2.Parameters.AddWithValue("@Start", (Calendar1.SelectedDates[0]).Date.ToShortDateString());
cmd2.Parameters.AddWithValue("@End", (Calendar1.SelectedDates[Calendar1.SelectedDates.Count - 1]).Date.ToShortDateString());
cmd2.Parameters.AddWithValue("@EmployeeId", Convert.ToInt32(dr[0]));                                    
using (SqlDataReader dr2 = cmd2.ExecuteReader())
{
 while (dr2.Read())
    {
    //Response.Write(Convert.ToDateTime(dr2[0]));
     GridView7.DataSource = cmd2.ExecuteReader();
GridView7.DataBind();
    }

   }
       Response.Write("<br/>");
 } 


  }
    con.close();
}
GridView7.DataSource = cmd.ExecuteReader();
GridView7.DataBind();

Do you need to handle the information as a column? why not using all the dates they took vacations in a single nvarchar(max) column and just adding a comma between them? i'm saying this because the way you are trying to achieve this, is gonna be a mess, since all dates doesn't match between employees, you can use a dynamic SQL pivot table but that will leave you with a bunch of null (or blank) spaces. — thepanch
– thepanch, Commented Mar 15, 2016 at 18:36
@thepanch Thank you for your response But I need to show the result in a table or GridView. Is that possible? It's Ok with dynamic SQL pivot table even that will leave with a bunch of null (or blank) spaces. Is that possible then? Thank you again. — Jonas Willander
– Jonas Willander, Commented Mar 15, 2016 at 19:23
@thepanch : My code is working if I use Response.Write(.....) for example: With Response.Write((dr[1]).ToString() + " "); And Response.Write(Convert.ToDateTime(dr2[0])); I can retriv as I want but I don't want it to Write on the page as it is. I want to show them in a table , GridView or dynamic SQL pivot table — Jonas Willander
– Jonas Willander, Commented Mar 15, 2016 at 19:32

Martin Parenteau · Accepted Answer · 2016-03-15 22:20:44Z

0

The FOR XML PATH syntax allows your query to group several values in a single one:

SELECT 
    E.EmployeeId, 
    E.FirstName,
    REPLACE(STUFF((
        SELECT 
            COALESCE('¶' + V.Dates, '')
        FROM 
            Vacation V 
        WHERE 
            V.EmployeeId = E.EmployeeId AND V.Dates >= @Start AND V.Dates <= @End
        FOR XML PATH('')), 1, 1, ''), '¶', ', ') AS VacationDates
FROM 
    Employee E

You can replace the ', ' separator by something else if you want.

Note: Sorry for the multipe edits. I am just not sure how you connect the employees, vacations and dates. This piece of code basically shows the idea for the FOR XML PATH syntax.

edited Mar 15, 2016 at 22:20

answered Mar 15, 2016 at 18:43

Martin Parenteau

74k13 gold badges137 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Jonas Willander Over a year ago

Thank you for your respons , I got error "Must declare the scalar variable "@Start"" but I'am declaring as cmd2.Parameters.AddWithValue("@Start", (Calendar1.SelectedDates[0]).Date.ToShortDateString());

Martin Parenteau Over a year ago

You get that error when inserting this query in your code, or when testing it directly in the database?

Jonas Willander Over a year ago

It's Ok, but My code is working if I use Response.Write(.....) for example: With Response.Write((dr[1]).ToString() + " "); And Response.Write(Convert.ToDateTime(dr2[0])); I can retriv as I want but I don't want it to Write on the page as it is. I want to show them in a table , GridView or dynamic SQL pivot table

Jonas Willander Over a year ago

When I inserting in my code but he answered me that he tought I runned XML path or somethin like that so it will not work his code. Actualy my code as it is , it's working but as I said Before I want I want to show them in a table , GridView or dynamic SQL pivot table. But I Think I must use the same name of sqlcommand. I use two sqlcommand becouse I have to retrieve first the employeeId. If you study my code.. then how can I use one command instead of two sqlcommand and two ExecuteReader()? I Think the problem is that I use two command and ExecuteReader(). Thank you for your time

Martin Parenteau Over a year ago

My answer is a way to do it with only one query. I don't know what error message you get exactly. I usually save my query as a stored procedure and call the procedure from code. You can test my query in the database first (providing some values for Start and End) and see if the output is what you want. If it is, then we can see how to make it work in your code.

|

Collectives™ on Stack Overflow

asp.net multiple sqlcommands in one GridView

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related