Order Importer - Retrive Orders Page

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

       Dim Da As OleDbDataAdapter

       'create dataset and set its namespace

       Dim DsOrders As New DataSet("DsOrders")

       DsOrders.Namespace = "http://www.tempuri.org/DsOrderInfo.xsd"

       'connect to database

       Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Request.PhysicalApplicationPath & "\DemoData\SageDemoData.mdb;Persist Security Info=False")

       Conn.Open()

       'get customer records

       Da = New OleDbDataAdapter("SELECT * FROM Customers WHERE CustomerID IN(SELECT DISTINCT CustomerID FROM Orders WHERE ExportedToSage=0)", Conn)

       Da.Fill(DsOrders, "Customers")

       'get order records

       Da = New OleDbDataAdapter("SELECT * FROM Orders WHERE ExportedToSage=0", Conn)

       Da.Fill(DsOrders, "Orders")

       'get order item records

       Da = New OleDbDataAdapter("SELECT * FROM OrderItems WHERE OrderID IN(SELECT DISTINCT OrderID FROM Orders WHERE ExportedToSage=0)", Conn)

       Da.Fill(DsOrders, "OrderItems")

       'disconnect from database

       Conn.Close()

       'clear any response data prior to outputing any XML

       Response.Clear()

       'begin outputing XML

       Response.Write("<?xml version=""1.0"" standalone=""yes"" ?>" & vbCrLf)

       Response.Write("<DsOrders xmlns=""http://www.tempuri.org/DsOrderInfo.xsd"">" & vbCrLf)

       For Each Dr As DataRow In DsOrders.Tables("Customers").Rows

           Response.Write(vbTab & "<Customers>" & vbCrLf)

           'Required Fields

           AddFieldToXML("CustomerID", Dr("CustomerID"))

           AddFieldToXML("ContactName", Dr("ContactName"))

           AddFieldToXML("Address1", Dr("Address1"))

           AddFieldToXML("Town", Dr("City"))

           'Optional Fields (Used)        

           AddFieldToXML("CompanyName", Dr("Company"))

           AddFieldToXML("Address2", Dr("Address2"))

           AddFieldToXML("County", Dr("County"))

           AddFieldToXML("PostCode", Dr("PostCode"))

           AddFieldToXML("Telephone", Dr("Tel"))

           AddFieldToXML("Fax", Dr("Fax"))

           AddFieldToXML("VatNumber", Dr("VATNumber"))

           AddFieldToXML("CountryCode", Dr("CountryCode"))

           'Optional Fields (Not Used)

           'AddFieldToXML("EmailAddress", Dr("Type Field Name"))

           Response.Write(vbTab & "</Customers>" & vbCrLf)

       Next

       'output orders XML

       For Each Dr As DataRow In DsOrders.Tables("Orders").Rows

           Response.Write(vbTab & "<Orders>" & vbCrLf)

           'Required Fields

           AddFieldToXML("CustomerID", Dr("CustomerID"))

           AddFieldToXML("OrderID", Dr("OrderID"))

           AddFieldToXML("OrderDate", Dr("OrderDate"))

           'Optional Fields (Used)          

           AddFieldToXML("DelContactName", Dr("DelContactName"))

           AddFieldToXML("DelCompany", Dr("DelCompany"))

           AddFieldToXML("DelAddress1", Dr("DelAddress1"))

           AddFieldToXML("DelAddress2", Dr("DelAddress2"))

           AddFieldToXML("DelTown", Dr("City"))

           AddFieldToXML("DelCounty", Dr("County"))

           AddFieldToXML("DelPostCode", Dr("PostCode"))

           'Optional Fields (Not Used)

           'AddFieldToXML("Carriage", Dr("Type Field Name"))

           'AddFieldToXML("CarriageTaxCode", Dr("Type Field Name"))

           'AddFieldToXML("CcAuthCode", Dr("Type Field Name"))

           'AddFieldToXML("CcCardType", Dr("Type Field Name"))

           'AddFieldToXML("CcIssueNo", Dr("Type Field Name"))

           'AddFieldToXML("CcNameOnCard", Dr("Type Field Name"))

           'AddFieldToXML("CcNumber", Dr("Type Field Name"))

           'AddFieldToXML("CcValidFrom", Dr("Type Field Name"))

           'AddFieldToXML("CcValidTo", Dr("Type Field Name"))

           'AddFieldToXML("DelCountry", Dr("Type Field Name"))

           Response.Write(vbTab & "</Orders>" & vbCrLf)

       Next

       'output order items XML

       For Each Dr As DataRow In DsOrders.Tables("OrderItems").Rows

           Response.Write(vbTab & "<OrderItems>" & vbCrLf)

           'Required Fields

           AddFieldToXML("OrderID", Dr("OrderID"))

           AddFieldToXML("Description", Dr("Description"))

           AddFieldToXML("Price", Dr("Price"))

           AddFieldToXML("ProductCode", Dr("PartNo"))

           AddFieldToXML("Quantity", Dr("Qty"))

           'Optional Fields (Not Used)

           'AddFieldToXML("TaxCode", Dr("Type Field Name"))

           Response.Write(vbTab & "</OrderItems>" & vbCrLf)

       Next

       'finish outputing XML

       Response.Write("</DsOrders>")

       'End the response stream here to ensure nothing is send after the XML data.

       Response.End()

   End Sub

   Sub AddFieldToXML(ByVal FieldName As String, ByVal Value As Object)

       If Not Value Is DBNull.Value Then

           Response.Write(vbTab & vbTab & "<" & FieldName & ">" & Value.ToString().Replace("&", "&amp;") & "</" & FieldName & ">" & vbCrLf)

       End If

   End Sub

</script>