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:
- A
DataSet
- a collection of DataTable
objects 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 keyUser_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%> />
</FooterTemplate>
</asp:TemplateColumn>
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