Click here to Skip to main content
Click here to Skip to main content

Display Multiple Table Field Data in a Single DataGrid

, 1 Nov 2007
Rate this:
Please Sign up or sign in to vote.
Display data from multiple tables in a single DataGrid.

Introduction

This article helps you to display multiple table fields in a single grid.

Database Design

Here is the SQl to create the sample database:

CREATE TABLE [dbo].[US_Status] (
    [USST_StatusId] [int] IDENTITY (1, 1) NOT NULL ,
    [USST_Name] [varchar] (50) NOT NULL ,
    [USST_Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[US_Type] (
    [USTY_TypeID] [int] IDENTITY (1, 1) NOT NULL ,
    [USTY_Name] [varchar] (50) NOT NULL ,
    [USTY_Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[US_User] (
    [USUS_UserID] [int] IDENTITY (1, 1) NOT NULL ,
    [USUS_Email] [varchar] (100) NOT NULL ,
    [USUS_Password] [varchar] (100) NOT NULL ,
    [USUS_FirstName] [varchar] (100) NOT NULL ,
    [USUS_LastName] [varchar] (100) NOT NULL ,
    [USUS_CheckVal] [varchar] (100) NOT NULL ,
    [USST_StatusID] [int] NOT NULL ,
    [USTY_TypeID] [int] NOT NULL ,
    [USUS_Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO

Using the Code

I use the BindData method below to retrieve data from the database. This method consists of:

  1. A DataSet - a collection of DataTable objects
  2. DataRelation - Represents the parent\child relationship between two DataTable objects

In this method, we have created two data relations:

  • User_StatusRelation: Parent table is USST_Status which has the primary key
  • User_TypeRelation : Parent table is USST_Type which has the primary key
Sub BindData()
    Dim ConString As String = "Data Source=AARTHANA-BF2A86\SQLEXPRESS;" & _ 
                              "Initial Catalog=Ec2;Integrated Security=True"
    Dim conn As New SqlConnection(ConString)
    Dim cmd As SqlCommand
    conn.Open()
    cmd = New SqlCommand("select * from US_User", conn)
    adapater = New SqlDataAdapter(cmd)
    adapater.Fill(DB, "US_User")
    cmd.CommandText = "select * from US_Status"
    adapater.Fill(DB, "US_Status")
    cmd.CommandText = "select * from US_Type"
    adapater.Fill(DB, "US_Type")
    statusview = DB.Tables("US_Status")
    tempdataview = DB.Tables("US_Type")
    User_Statusrelation = New Data.DataRelation("", _
       DB.Tables("US_Status").Columns("USST_StatusID"), _
       DB.Tables("US_User").Columns("USST_StatusID"), True)
    DB.Relations.Add(User_Statusrelation)
    User_Typerelation = New Data.DataRelation("", _
       DB.Tables("US_Type").Columns("USTY_TypeID"), _
       DB.Tables("US_User").Columns("USTY_TypeID"), True)
    DB.Relations.Add(User_Typerelation)
    DbGrid.DataSource = DB
    DbGrid.DataBind()
End Sub

Interesting Facts

The interesting part of our HTML code is:

<asp:TemplateColumn HeaderText="Status"><ItemTemplate >
 <%#BindJob(Container.DataItem)%>
 </ItemTemplate>
<EditItemTemplate >
<asp:DropDownList ID="statuslist" runat ="server" 
   DataTextField="USST_Name" DataValueField ="USST_StatusId" 
   DataSource =<%#statusview%> >
</asp:DropDownList>
</EditItemTemplate>
<FooterTemplate >
<asp:DropDownList ID="statuslistAdd"
    runat ="server" DataTextField="USST_Name" 
    DataValueField ="USST_StatusId"
    DataSource =<%#statusview%> />

Wwe are binding the DataGrid value using #BindJob(Container.DataItem), which gives the value of USST_Name from the table US_Status.

Public Function BindJob(ByVal o As Object) As String
    Dim rowin As Data.DataRow
    Dim drv As Data.DataRowView = o
    rowin = drv.Row.GetParentRow(User_Statusrelation)
    Return rowin("USST_Name")
End Function

This code also includes features like:

  • Inserting a row in a DataGrid
  • Using a DropDownList in a DataGrid

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

call_vino
Web Developer
Unknown
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 2 Nov 2007
Article Copyright 2007 by call_vino
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid