I have a gridview based on one table, obviously, and I would like to display a field from a different table using the primay key of the gridview table.
So my one table stores the ID of the vendor, employee and patient. I want to display the name fields for each of their respective tables.
So the main table is call transport_Request, the columns are named MRN, vendor_ID, employee_ID
How do I display the fields from those respective tables, namely patient_Last_Name, vendor_Name, employee_Last_Name
I am using c#
I would recommend binding your GridView to a query that joins all the tables together to get the names, like this (assuming SQL Server):SELECT T.ID, V.vendor_Name, E.employee_Last_Name, P.patient_Last_NameHere I am using LEFT JOINS in case the vendor, employee, or patient information is not available for a particular record. This query would return all of the records from transport_Request, and the employee, patient, and vendor names for each record where they are available. Then you can bind the columns of your GridView to the column names ID, vendor_Name, employee_Last_Name, and patient_Last_Name to display those fields in the grid.
FROM transport_Request AS T
LEFT JOIN vendors AS V ON V.vendor_ID = T.vendor_ID
LEFT JOIN employees AS E ON E.employee_ID = T.employee_ID
LEFT JOIN patients AS P ON P.patient_ID = T.patient_ID
This would be more efficient than looking up the associated information in each row using, say, GridView's OnRowDataBound event. This is because the database server is almost always much quicker at joining the tables and pulling related information all at once.
Hope this helps!
Does this accomplish the task of replacing what the gridview column shows? Meaning, I don't want to show both the ID and the name, just the name. But when I select that row in the gridview and it shows up in the details view, i still need the ID to be present to reference into the detailsview...does this do that?
You can specify which columns to show in the GridView by setting the AutoGenerateColumns property to false on the GridView, then add in the columns that you want using either the Visual Studio designer, or code like the following:<asp:GridView ... > <Columns> <asp:BoundField DataField="vendor_Name" HeaderText="Vendor" /> <asp:BoundField DataField="patient_Last_Name" HeaderText="Patient" /> <asp:BoundField DataField="employee_Last_Name" HeaderText="Employee" /> </Columns> </asp:GridView>
The code above will only show the vendor, patient, and employee names. However, you don't need the id's to show the details in the DetailsView, because the DetailsView will have its own query to the database. The DetailsView only needs to know the ID of the record in the transport_Request table, and then you can have another query to pull the id's for that table, depending on how you want the data displayed (DropDownLists's, etc.):<asp:DetailsView DataSourceID="DetailsDataSource" ... > </asp:DetailsView> <asp:SqlDataSource ID="DetailsDataSource" runat="Server" ConnectionString="<%$ConnectionStrings:CONNECTION_STRING_HERE%>" SelectCommand="SELECT * FROM transport_Request WHERE ID=@ID"> <SelectParameters> <asp:ControlParameter Name="ID" ControlID="MyGridView" PropertyName="SelectedValue" /> </SelectParameters>It may take some tweaking to get this to work, but that is the general idea.