How to retrieve data from access database for each item in a shopping cart and display in gridview in asp.net

Question

Hi,

I need to set up a CD shopping cart from scratch and it needs to use an access database.

So far, I'm storing the AlbumID in the session, then use it to create a DataTable which is then bound to the GridView. But I want to retrieve album info from an access database and display that in the GridView rather than the values I've created programmatically.

I don't need to use a GridView, I have tried just a Repeater control but getting nowhere. :|

Also, I can't store the cart information in a table in the database to use, it's got to be stored within a session.

Any help would be great.

Dim ShoppingCart As System.Data.DataTable
Dim CartItem As System.Data.DataRow

Private Sub Page_Load(ByVal s As Object, ByVal e As EventArgs)

Dim albuminfo As New AccessDataSource("~/App_Data/project.mdb", "SELECT * FROM 
album WHERE ([AlbumID] = " & Session("AlbumID") & ")")
gvShoppingCart.DataSource = albuminfo
gvShoppingCart.DataBind()

### Above code was my attempt at getting info from database into GridView, ###
### which works but only for a single record at any one time. ###
If Session("ShoppingCart") Is Nothing Then
    initialiseCart()
Else
    addToCart()
End If
End Sub

Private Sub initialiseCart()
    Dim ShoppingCart As New System.Data.DataTable("ShoppingCart")
    ShoppingCart.Columns.Add("ID", GetType(Integer))
    ShoppingCart.Columns.Add("Album", GetType(String))
    ShoppingCart.Columns.Add("Quantity", GetType(Integer))
    ShoppingCart.Columns.Add("Cost", GetType(Decimal))
    ShoppingCart.Columns("ID").ExtendedProperties("Width") = 200
    Session("ShoppingCart") = ShoppingCart
    addToCart()
End Sub

Private Sub addToCart()
    ShoppingCart = Session("ShoppingCart")
    Dim AlbumID = Session("AlbumID")
    Dim isInCart As Boolean = False

    If Not Session("AlbumID") Is Nothing Then
        For Each Me.CartItem In ShoppingCart.Rows
            If CartItem("ID") = AlbumID Then
                isInCart = True
                CartItem("Quantity") = CartItem("Quantity") + 1
                Session("AlbumID") = Nothing
                Response.Write("<b>Additional copy added.</b>")
                Exit For
            End If
        Next

        If Not isInCart Then
            Response.Write("<b>New item added.</b>")
            CartItem = ShoppingCart.NewRow
            CartItem("ID") = AlbumID
            CartItem("Quantity") = 1
            CartItem("Album") = AlbumID
            CartItem("Cost") = 4.99

            ShoppingCart.Rows.Add(CartItem)
        End If
    End If
    Session("ShoppingCart") = ShoppingCart

    gvShoppingCart.DataSource = ShoppingCart
    gvShoppingCart.DataBind()

    lblTotal.Text = "Total: £" & getItemTotal()
End Sub

Private Function getItemTotal() As Decimal
    Dim i As Integer
    Dim subTotal As Decimal

    For i = 0 To ShoppingCart.Rows.Count - 1
        CartItem = ShoppingCart.Rows(i)
        subTotal += (CartItem("Cost") * CartItem("Quantity"))
    Next

    Return subTotal
End Function

Private Sub Delete_Item(ByVal s As Object, ByVal e As GridViewCommandEventArgs)
    ShoppingCart = Session("ShoppingCart")
    ShoppingCart.Rows(e.CommandArgument).Delete()
    Session("ShoppingCart") = ShoppingCart

    gvShoppingCart.DataSource = ShoppingCart
    gvShoppingCart.DataBind()

    lblTotal.Text = "Total: £" & getItemTotal()
End Sub

Private Sub emptyCart()
    Session.Remove("AlbumID")
    Session.Remove("ShoppingCart")
    Response.Redirect("shopping_cart.aspx")
End Sub

Private Sub checkOut()
    Session.Remove("AlbumID")
    Response.Redirect("check_out.aspx")
End Sub