order.aspx.vb.txt

ASP.NET 2.0 visual web desginer CODE

        ' 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()