faqts : Computers : Databases : Microsoft SQL Server

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

3 of 6 people (50%) answered Yes
Recently 3 of 6 people (50%) answered Yes

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
----------------------------------------------------------------------