Entry
Database:Microsoft:SQL server: Visual Studio: ASP.NET:Table: Master: How create master detail table?
Dec 29th, 2003 12:12
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 29 December 2003 - 06:47 pm -------------------
Database:Microsoft:SQL server: Visual Studio: ASP.NET:Table: Master:
How create master detail table?
---
Steps: Overview:
1. -Create a new ASP.NET project,
2. -Start the data form wizard
1. -select from menu option 'Project'
2. -select from list 'Add Web Form...'
3. -Select icon 'Data Form Wizard',
by double clicking on it in the
right pane
4. -This will open the 'Data Form Wizard'
dialog box
5. -click button 'Next'
6. -Choose a name for a new dataset
e.g.
DataSet11
7. -click button 'Next'
8. -Select an existing database connection or create a database new
connection
9. -Add the tables you want to view
from the left pane to the right
pane
e.g.
1. To select <your tablename1>
1. Click on <your tablename1>
in the left pane
2. click '>' to add this table
to the right pane
2. To select <your tablename2>
1. Click on <your tablename2>
in the left pane
2. click '>' to add this table
to the right pane
and so on.
10. -click button 'Next' when finished
selecting
11. -If you have selected 2 or more tables,
you can indicate a relationship between
this tables
(e.g. a master-detail, or parent-child,
or primary-foreign key relationship)
e.g.
1. Choose a 'Name:'
e.g.
<your table1_table2relationshipname>
2. Choose the parent (or master table,
or primary key table)
e.g.
<your tablename1>
3. If there is a primary key-foreign key
relationship existing between the
tables in Microsoft SQL server,
then automatically the name of
the 'Child table:' is filled in.
e.g.
<your tablename2>
4. Possibly select the 'Keys'
e.g.
1. <your primarykey name>
for the parent table
2. <your foreignkey name>
for the child table
5. When finished, click the '>' to
add your relationship to the
right pane
12. -click button 'Next'
13. -choose the tables to display
1. choose <your (master)tablename1>
in 'Master or single table:'
Note: it is important that you select your 'master' table, or
else the 'detail table' will be greyed out.
2. possibly choose <your (detail)tablename1>
in 'Detail table:'
14. -click button 'Finish'
15. -this will then generate automatically
source code and controls to show this
table relationship
--- cut here ---------------------------------------------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub ShowDetailGrid()
If (Me.masterDataGrid.SelectedIndex <> -1) Then
Dim parentRows As System.Data.DataView
Dim childRows As System.Data.DataView
Dim currentParentRow As System.Data.DataRowView
Me.objdataset11 = CType(Application("objdataset11"),
WebApplication83.dataset11)
parentRows = New DataView()
parentRows.Table = Me.objdataset11.Tables("Person")
currentParentRow = parentRows
(Me.masterDataGrid.SelectedIndex)
childRows = currentParentRow.CreateChildView
("Person_Email")
Me.detailDataGrid.DataSource = childRows
Me.detailDataGrid.DataBind()
Me.detailDataGrid.Visible = True
Else
Me.detailDataGrid.Visible = False
End If
End Sub
Private Sub masterDataGrid_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
masterDataGrid.SelectedIndexChanged
Me.ShowDetailGrid()
End Sub
Private Sub buttonLoad_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles buttonLoad.Click
Try
Me.LoadDataSet()
Me.masterDataGrid.SelectedIndex = -1
Me.masterDataGrid.DataBind()
Me.detailDataGrid.Visible = False
Application("objdataset11") = Me.objdataset11
Catch eLoad As System.Exception
Me.Response.Write(eLoad.Message)
End Try
End Sub
Public Sub LoadDataSet()
'Create a new dataset to hold the records returned from the
call to FillDataSet.
'A temporary dataset is used because filling the existing
dataset would
'require the databindings to be rebound.
Dim objDataSetTemp As WebApplication83.dataset11
objDataSetTemp = New WebApplication83.dataset11()
Try
'Attempt to fill the temporary dataset.
Me.FillDataSet(objDataSetTemp)
Catch eFillDataSet As System.Exception
'Add your error handling code here.
Throw eFillDataSet
End Try
Try
'Empty the old records from the dataset.
objdataset11.Clear()
'Merge the records into the main dataset.
objdataset11.Merge(objDataSetTemp)
Catch eLoadMerge As System.Exception
'Add your error handling code here.
Throw eLoadMerge
End Try
End Sub
Public Sub FillDataSet(ByVal dataSet As WebApplication83.dataset11)
'Turn off constraint checking before the dataset is filled.
'This allows the adapters to fill the dataset without concern
'for dependencies between the tables.
dataSet.EnforceConstraints = False
Try
'Open the connection.
Me.OleDbConnection1.Open()
'Attempt to fill the dataset through the OleDbDataAdapter1.
Me.OleDbDataAdapter1.Fill(dataSet)
Me.OleDbDataAdapter2.Fill(dataSet)
Catch fillException As System.Exception
'Add your error handling code here.
Throw fillException
Finally
'Turn constraint checking back on.
dataSet.EnforceConstraints = True
'Close the connection whether or not the exception was
thrown.
Me.OleDbConnection1.Close()
End Try
End Sub
--- cut here ---------------------------------------------------------
16. -First run this program as usual
(e.g. <F5>)
Note:
If you run this program as usual, you will not see
anything, as it shows the 'Webform1.aspx' page, instead
of the 'DataWebForm1.aspx'.
17. -To run this program itself, right click on the page
'DataWebForm1.aspx', and choose 'View in browser'.
---
18. -If you click on the 'load' button it will
show the master table.
If you then click on the link 'Show details',
it will show the corresponding entries for
that row from your 'detail table'
---
---
Book: see also:
---
[book: Walther, Stephen - ASP.NET Unleashed, Second Edition]
---
[book: Stephens, Rod - Visual Basic .NET Database Programming]
---
---
Internet: see also:
---
Database: Visual Studio.NET: ASP.NET: Table: How create master detail
table + deploy it to Internet?
http://www.faqts.com/knowledge_base/view.phtml/aid/27772/fid/147
----------------------------------------------------------------------
