Dim cXML
cXML = "<PavRequest xmlns=""pav3.cdyne.com"">" & _
"<LicenseKey>f01d89fd-5155-5455-5585-e84ab8de8591</LicenseKey>" & _
"<FirmOrRecipient></FirmOrRecipient>" & _
"<PrimaryAddressLine>505 INDEPENDENCE PARKWAY</PrimaryAddressLine>" & _
"<SecondaryAddressLine>SUITE 300</SecondaryAddressLine>" & _
"<CityName></CityName>" & _
"<State></State>" & _
"<ZipCode>23320</ZipCode>" & _
"<Urbanization></Urbanization>" & _
"<ReturnCaseSensitive>true</ReturnCaseSensitive>" & _
"<ReturnCensusInfo>true</ReturnCensusInfo>" & _
"<ReturnCityAbbreviation>true</ReturnCityAbbreviation>" & _
"<ReturnGeoLocation>true</ReturnGeoLocation>" & _
"<ReturnLegislativeInfo>true</ReturnLegislativeInfo>" & _
"<ReturnMailingIndustryInfo>true</ReturnMailingIndustryInfo>" & _
"<ReturnResidentialIndicator>true</ReturnResidentialIndicator>" & _
"<ReturnStreetAbbreviated>true</ReturnStreetAbbreviated>" & _
"</PavRequest>"
Dim oXMLHTTP
Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
Call oXMLHttp.Open("POST", "http://pav3.cdyne.com/PavService.svc/VerifyAddressAdvanced", False)
Call oXMLHttp.setRequestHeader("Content-Type", "text/xml")
Call oXMLHttp.send(cXML)
'Display response in a message box.
MsgBox oXMLHTTP.responseText
'Write and save response to a text file.
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, MyFile, FileName, TextLine
Set fso = CreateObject("Scripting.FileSystemObject")
FileName = "c:\Users\Desktop\pavRETURN.txt"
Set MyFile = fso.OpenTextFile(FileName, ForWriting, True)
MyFile.WriteLine oXMLHttp.responseText
MyFile.Close
Set MyFile = fso.OpenTextFile(FileName, ForReading)
Do While MyFile.AtEndOfStream <> True
TextLine = MyFile.ReadLine
Loop
MyFile.Close
Call PAV
directly from MS-Access or Excel.
'Address to post request
surl = "http://pav3.cdyne.com/PavService.svc/VerifyAddressAdvanced"
'Create an HTTP object for sending package
Set ohtml = CreateObject("Microsoft.XMLHTTP")
'Create XML DOM for reading response tags - Uses PUBLIC Object xmldoc
Set xmldoc = CreateObject("Microsoft.XMLDOM")
'Move address info into package and send to Cdyne
vpackage = ""
vpackage = "<PavRequest xmlns=""pav3.cdyne.com"">" & vbCrLf
vpackage = vpackage & "<CityName>" & rs!City & "</CityName>" & vbCrLf
vpackage = vpackage & "<LicenseKey>your license key here</LicenseKey>" & vbCrLf
vpackage = vpackage & "<PrimaryAddressLine>" & rs!Address & "</PrimaryAddressLine>" & vbCrLf
vpackage = vpackage & "<ReturnCaseSensitive>false</ReturnCaseSensitive>" & vbCrLf
vpackage = vpackage & "<ReturnCensusInfo>true</ReturnCensusInfo>" & vbCrLf
vpackage = vpackage & "<ReturnCityAbbreviation>true</ReturnCityAbbreviation>" & vbCrLf
vpackage = vpackage & "<ReturnGeoLocation>true</ReturnGeoLocation>" & vbCrLf
vpackage = vpackage & "<ReturnLegislativeInfo>true</ReturnLegislativeInfo>" & vbCrLf
vpackage = vpackage & "<ReturnMailingIndustryInfo>true</ReturnMailingIndustryInfo>" & vbCrLf
vpackage = vpackage & "<ReturnResidentialIndicator>true</ReturnResidentialIndicator>" & vbCrLf
vpackage = vpackage & "<ReturnStreetAbbreviated>true</ReturnStreetAbbreviated>" & vbCrLf
vpackage = vpackage & "<SecondaryAddressLine nil=""true""/>" & vbCrLf
vpackage = vpackage & "<State>" & rs!State & "</State>" & vbCrLf
If IsNull(rs!Zip) Then
vpackage = vpackage & "<ZipCode nil=""true""/>" & vbCrLf
Else
vpackage = vpackage & "<ZipCode>" & rs!Zip & "</ZipCode>" & vbCrLf
End If
vpackage = vpackage & "</PavRequest>" & vbCrLf
'Set up the POST
ohtml.Open "POST", surl, False
ohtml.SetRequestHeader "Content-type", "text/xml"
'Send the package
ohtml.Send vpackage
'Get response in raw text
gethtml = ohtml.responseText
'Load the XML DOM
xmldoc.async = False
xmldoc.LoadXML (ohtml.responsexml.XML)
'Update database
Select Case xmldoc.DocumentElement.SelectSingleNode("ReturnCode").Text
Case 100, 102, 103
'Update address
With rs
!Address = xmldoc.DocumentElement.SelectSingleNode("PrimaryDeliveryLine").Text
!City = xmldoc.DocumentElement.SelectSingleNode("CityName").Text
!State = xmldoc.DocumentElement.SelectSingleNode("StateAbbreviation").Text
!Zip = xmldoc.DocumentElement.SelectSingleNode("ZipCode").Text
!UserLastModified = "CDYNEScrub"
.Update
End With
'Addresses can be longer than 36 characters (standard label width)
If Len(xmldoc.DocumentElement.SelectSingleNode("PrimaryDeliveryLine").Text) < 36 Then
cn2.Execute vSQL
Call AddCdyneLocationInfo(vRecordType, vscrubid)
Else
Call RecordBadAddress(vRecordType, vscrubid, 9999)
End If
Case Else
Call RecordBadAddress(vRecordType, vscrubid, xmldoc.DocumentElement.SelectSingleNode("ReturnCode").Text)
End Select
Code Snippet from AddCdyneLocationInfo() for updating additional CDYNE
fields in separate table:
'Get recordset of columns - names must be spelled same way as xmldoc (case sensitive)
vSQL = "select column_name from information_schema.columns where table_name='cdyne' "
vSQL = vSQL & "and column_name not in ('cdyneid','recordtype')"
rsCols.Open vSQL, cnlocal, adOpenForwardOnly, adLockReadOnly
'Loop through each column to update the value
'Columns must be named identically to Cdyne field names
With rs
.Open vSQL, cnlocal, adOpenKeyset, adLockPessimistic
rs.AddNew
!RecordType = vRecordType
Do While Not rsCols.EOF
Set vtag = xmldoc.getElementsByTagName(rsCols!column_name)
rs(Trim(rsCols!column_name)) = vtag(0).Text
rsCols.MoveNext
Loop
!DateLastUpdated = Now()
.Update
End With