How to display Employee with associated Manager in GridView using SQL inline query in ASP.Net

Megrio says:

I have a 2 tables:

Mrg:

UserID || UserID_Mrg || Status

and

User:

UserID || Name

I want to display all employees (UserID) under each Manager (UserID_Mrg)

example:

UserID_Mrg = 12

So display all UserID with UserID_Mrg = 12

so far I could just display the users Information an their Manager:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

private void BindGridView()     {         using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))         using (SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT tbl_user.Name, tbl_mrg.UserID_Mrg                                                              FROM   tbl_mrg                                                              INNER JOIN tbl_user ON tbl_mrg.UserID = tbl_user.UserID                                                              WHERE  ((tbl_mag.user_ID = @UserID))                                                              ORDER BY     tbl_user.Name", con))         {             adapter.SelectCommand.Parameters.Add(new SqlParameter("@UserID", SessionHelper.UserID));             adapter.Fill(Data2);             GridView2.DataSource = Data2;             GridView2.DataBind();         }     }

 

1 2 3 4 5 6 7

<asp:GridView ID="GridView2" runat="server">                 <Columns>                          <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />  <asp:BoundField DataField="Mrg" HeaderText="Manager" SortExpression="Manager" />                   </Columns>             </asp:GridView>

 

In your Code the Select query to display Employee and there manager details are totally wrong.

In your GridView you are showing Employee Name and Manager Name in boundfield so you need to get the Manager Name not a Manager Id.

Please replace your query with below test query and check hope it will work fine.

SELECT tbl_user.Name
       ,(SELECT Name FROM tbl_user TU WHERE TU.UserID = tbl_mrg.UserID_Mrg ) AS Manager
FROM tbl_mrg tbl_mrg
INNER JOIN tbl_user tbl_user
ON tbl_mrg.UserID = tbl_user.UserID
WHERE  tbl_mrg.UserID_Mrg = @UserID
ORDER BY tbl_user.Name