' *********************************************************
    ' *                                                       *
    ' *  Insert this code into the Page_Load event handler    *
    ' *                                                       *
    ' *  Remember to import the data access namespaces        *
    ' *  System.Data and System.Data.SqlClient                *
    ' *  at the top of the page.                              *
    ' *                                                       *
    ' *********************************************************

    ' declare a DataSet to hold the rows from the database
    Dim ds As New DataSet()

    ' specify the two SQL statements to select the rows, first one for the columns
    ' required from the MenuItem table, including a WHERE clause for MenuItemType
    Dim sGetMenuItems As String = "SELECT MenuItemID, MenuItemType, ItemName, PizzaToppings, " _
      & "Description, GraphicFileName FROM MenuItems " _
      & "WHERE MenuItemType LIKE @MenuItemType " _
      & "ORDER BY MenuItemType DESC, ItemName"
    ' second SQL statement gets the columns required from the SizeAndPrice table, but
    ' need to limit the rows to those specified by the MenuItemType value passed to
    ' this page. This column is not in the SizeAndPrice table, so have to join it to
    ' the MenuItems table to be able to select on the MenuItemType column in that table
    Dim sGetSizesAndPrices As String = "SELECT fkMenuItemID, ItemSize, ItemPrice " _
      & "FROM SizeAndPrice JOIN MenuItems ON MenuItems.MenuItemID = SizeAndPrice.fkMenuItemID " _
      & "WHERE MenuItemType LIKE @MenuItemType " _
      & "ORDER BY ItemPrice"

    ' get conection string from Web.config file
    Dim sConnect As String = ConfigurationManager.ConnectionStrings("PPQ_DataConnectionString1").ConnectionString

    ' create a connection to the database using the connection string
    ' the Using statement ensures that the connection is closed and
    ' disposed automatically after use
    Using con As New SqlConnection(sConnect)

      ' create a SqlDataAdapter using the SqlConnection object
      ' and the first SQL SELECT statement declared earlier
      Dim da As New SqlDataAdapter(sGetMenuItems, con)

      ' create a SqlParameter to hold the type of menu items to show
      Dim param As New SqlParameter("@MenuItemType", SqlDbType.VarChar, 10)

      ' get the item type specified when the page was opened from the query string
      ' it will force the SelectCommand to select only the required rows, or all
      ' of the rows if there is no value in the query string (% means match anything)
      param.Value = Request.QueryString("type") & "%"

      ' add the parameter to the SelectCommand used by the SqlDataAdapter
      da.SelectCommand.Parameters.Add(param)

      Try

        ' fetch the MenuItems rows into the DataSet as a new table
        da.Fill(ds, "MenuItems")

        ' change the SQL statement in the SelectCommand of the
        ' SqlDataAdapter to the one that selects the SizeAndPrice rows
        da.SelectCommand.CommandText = sGetSizesAndPrices

        ' fetch the SizeAndPrice rows into the DataSet as another new table
        da.Fill(ds, "SizeAndPrice")

      Catch ex As Exception

        ' if there is an error, display the message and stop execution here
        Label1.Text = "ERROR: " & ex.Message
        Exit Sub

      End Try

    End Using

    ' create a relationship between the two tables in the DataSet
    ' first get references to the primary key in the MenuItems table
    ' and the foreign key in the SizeAndPrice table
    Dim pkcol As DataColumn = ds.Tables("MenuItems").Columns("MenuItemID")
    Dim fkcol As DataColumn = ds.Tables("SizeAndPrice").Columns("fkMenuItemID")

    ' now create the relationship within the DataSet
    Dim dr As New DataRelation("MenuLink", pkcol, fkcol)

    ' add this relationship to the Relations collection of the DataSet
    ds.Relations.Add(dr)

    ' bind the MenuItems table to the DataList control to display the rows
    DataList1.DataSource = ds
    DataList1.DataMember = "MenuItems"
    DataList1.DataBind()