Order Importer - Retrive Orders Page

<%

 

Sub AddFieldToXML(FieldName, Value)

   If Not IsNull(Value) Then

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

   Else

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

   End If  

End Sub

 

'clear any response data prior to outputing any XML

Response.Clear()

 

'connect to database

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(".") & "\SageDemoData.mdb;Persist Security Info=False")

 

'create the recordsets

Set Customers = Conn.Execute("SELECT * FROM Customers WHERE CustomerID IN(SELECT DISTINCT CustomerID FROM Orders WHERE ExportedToSage=0)")

Set Orders = Conn.Execute("SELECT * FROM Orders WHERE ExportedToSage=0")

Set OrderItems = Conn.Execute("SELECT * FROM OrderItems WHERE OrderID IN(SELECT DISTINCT OrderID FROM Orders WHERE ExportedToSage=0)")

 

'begin outputing XML

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

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

 

'output customer/s XML

Do While Not Customers.Eof

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

 

 'Required Fields

 AddFieldToXML "CustomerID", Customers("CustomerID")

 AddFieldToXML "ContactName", Customers("ContactName")

 AddFieldToXML "Address1", Customers("Address1")

 AddFieldToXML "Town", Customers("City")

 

 'Optional Fields (Used)        

 AddFieldToXML "CompanyName", Customers("Company")

 AddFieldToXML "Address2", Customers("Address2")

 AddFieldToXML "County", Customers("County")

 AddFieldToXML "PostCode", Customers("PostCode")

 AddFieldToXML "Telephone", Customers("Tel")

 AddFieldToXML "Fax", Customers("Fax")

 AddFieldToXML "VatNumber", Customers("VATNumber")

 AddFieldToXML "CountryCode", Customers("CountryCode")

 

 'Optional Fields (Not Used)

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

 

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

 Customers.MoveNext()

Loop

 

'output orders XML

Do While Not Orders.Eof

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

 

 'Required Fields

 AddFieldToXML "CustomerID", Orders("CustomerID")

 AddFieldToXML "OrderID", Orders("OrderID")

 AddFieldToXML "OrderDate", Orders("OrderDate")

 

 'Optional Fields (Used)        

 AddFieldToXML "DelContactName", Orders("DelContactName")

 AddFieldToXML "DelCompany", Orders("DelCompany")

 AddFieldToXML "DelAddress1", Orders("DelAddress1")

 AddFieldToXML "DelAddress2", Orders("DelAddress2")

 AddFieldToXML "DelTown", Orders("City")

 AddFieldToXML "DelCounty", Orders("County")

 AddFieldToXML "DelPostCode", Orders("PostCode")

 

 'Optional Fields (Not Used)

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

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

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

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

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

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

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

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

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

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

 

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

 Orders.MoveNext()

Loop

 

'output order items XML

Do While Not OrderItems.Eof

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

 

 'Required Fields

 AddFieldToXML "OrderID", OrderItems("OrderID")

 AddFieldToXML "Description", OrderItems("Description")

 AddFieldToXML "Price", OrderItems("Price")

 AddFieldToXML "ProductCode", OrderItems("PartNo")

 AddFieldToXML "Quantity", OrderItems("Qty")

 

 'Optional Fields (Not Used)

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

 

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

 OrderItems.MoveNext()

Loop

 

'disconnect from database

Conn.Close()

 

'finish outputing XML

Response.Write("</DsOrders>")

 

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

Response.End()

 

%>