Imports APNSoft.WebControls
Imports System.Data.OleDb
Imports System.Text

Public Class Orders
    Inherits System.Web.UI.UserControl


    'Declarations
    Public GridRow As APNSoft.WebControls.GridRow = Nothing

    Protected WithEvents lblCustomerID As System.Web.UI.WebControls.Label
    Protected WithEvents lblCompanyName As System.Web.UI.WebControls.Label
    Protected WithEvents lblContactName As System.Web.UI.WebControls.Label
    Protected WithEvents phOrders As System.Web.UI.WebControls.PlaceHolder


    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If (GridRow Is Nothing) Then Return

        lblCustomerID.Text = GridRow.Cells("CustomerID").Value.ToString()
        lblCompanyName.Text = GridRow.Cells("CompanyName").Value.ToString()
        lblContactName.Text = GridRow.Cells("ContactName").Value.ToString()

        'Define SQL query
        Dim SQL As String = "SELECT Orders.OrderID AS OrderID, " & _
         "Format(Orders.ShippedDate, 'mmm d yyyy') AS ShippedDate, " & _
         "Orders.Freight AS Freight FROM Customers INNER JOIN Orders ON " & _
         "Customers.CustomerID = Orders.CustomerID WHERE " & _
         "(Customers.CustomerID='" & GridRow.Cells("CustomerID").Value.ToString() & "');"

        'Create the DataTable object
        Dim myDataTable As DataTable = GetDataSource(SQL)
        Dim Orders As StringBuilder = New StringBuilder

        For i As Integer = 0 To myDataTable.Rows.Count - 1

            Orders.Append(myDataTable.Rows(i)("OrderID").ToString())
            Orders.Append(", ")
            Orders.Append(myDataTable.Rows(i)("ShippedDate").ToString())
            Orders.Append(", ")
            Orders.Append("$" + myDataTable.Rows(i)("Freight").ToString())
            Orders.Append("<br>")

        Next i

        'Add Orders
        phOrders.Controls.Add(New LiteralControl(Orders.ToString()))

    End Sub


    'Returns a DataTable from MS Access database
    Private Function GetDataSource(ByVal query As String) As DataTable

        Dim conn As OleDbConnection = New OleDbConnection
        Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter
        Dim myDataTable As DataTable = New DataTable

        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            System.Web.HttpContext.Current.Server.MapPath("~/DataBase/Nwind.mdb")

        myOleDbDataAdapter.SelectCommand = New OleDbCommand(query, conn)
        conn.Open()

        Try
            myOleDbDataAdapter.Fill(myDataTable)
        Finally
            conn.Close()
        End Try

        Return myDataTable

    End Function


#Region " Web Form Designer Generated Code "

    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    End Sub

    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        InitializeComponent()
    End Sub

#End Region

End Class