Order Importer - Retrive Orders Page |
<%
Sub AddFieldToXML(FieldName, Value) If Not IsNull(Value) Then Response.Write(vbTab & vbTab & "<" & FieldName & ">" & Replace(Value, "&", "&") & "</" & 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()
%>
|