Display field from different table in gridview

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#

1
stimpy4242
5/5/2009 12:06:52 AM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

3 Replies
1090 Views

Similar Articles

[PageSpeed] 56

Hi stimpy4242,

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_Name
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
Here 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.

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!

Regards,

~ mellamokb

0
mellamokb
5/5/2009 1:22:40 AM

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?

0
stimpy4242
5/5/2009 2:17:20 AM
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.

Regards,

~ mellamokb

0
mellamokb
5/5/2009 2:39:58 AM
Reply:

Web resources about - Display field from different table in gridview - asp.net.presentation-controls

Full Episode: WN 12/20/15: Clinton, Sanders Tangle in Democratic Debate
Gift Card Confidential: Turn Them in for Cash; Investigation of Alleged Lotto Fixing Scam Widens

Experts: Jet Crew Shouldn't Tell Passengers of Bomb Scare
ABC News Experts: Jet Crew Shouldn't Tell Passengers of Bomb Scare ABC News A child rests as passengers go through security screening at Moi ...

11 Taken to Hospital After Car Hits People on Vegas Strip
11 taken to hospital after car hits pedestrians on Las Vegas Strip

Splintered Spanish vote heralds arduous coalition talks
Reuters Splintered Spanish vote heralds arduous coalition talks Reuters MADRID A historically fragmented vote in Spanish elections on Sunday ...

Israel hit by rockets from Lebanon as tensions rise after Hezbollah's Samir Kantar killing
Katyusha missiles fired into Israel after air strike killed Hezbollah leader in Syria.

At Least 91 Still Missing After Landslide Hits Chinese Manufacturing Center
On Monday, a day after a landslide buried or toppled 33 buildings at an industrial park in China, at least 91 people were still missing, according ...

Hundreds protest release of New Delhi rapist
Salt Lake Tribune Hundreds protest release of New Delhi rapist Salt Lake Tribune Indian youth shout slogans as they are detained by police ...

Las Vegas Strip blocked off after car mows down pedestrians
NBCNews.com Las Vegas Strip blocked off after car mows down pedestrians CNN (CNN) The Las Vegas Strip was blocked off after a car mowed down ...

Ted Cruz Maintains Big Lead in Iowa
Here's more for the "establishment is doomed" files : Ted Cruz maintains a strong lead over Donald Trump in Iowa, according to the results of ...

Learner drivers to get lessons on the motorway for the first time
Learner drivers could have lessons on motorways for the first time, in a bid to ensure they have tackled challenging conditions with a qualified ...

Resources last updated: 12/21/2015 5:12:14 AM