...

View Full Version : Urgent : Help me Out VB Script with XML



gtsenthil
11-14-2007, 10:44 PM
Hi i am getting an error while inserting the XML data into Sybase database
i am having the Field like Annual salary Amount in DB Schema.
1) I am parsing the XML elements while parsing the Annual Salary Node, it is not having the value.How can i insert this data into Database.Its is showing the Converting the Datatype Varchar into Money error for the annual salary.The Schema having the Money Datatype.
there no point to change the datatype the schema.we need to fix this from front end side.

2) The email function is also not working.
3) Its not creating the log file inthe Windows 2003 server.I tried with test filesystemobject.its creating the file.do i need to change anything in the code pls advice me.

pls give me some solution to resolve this issue.

'Option Explicit
Dim ConnObj
Public XMLFile
Public LogFilePath
Public RecordDetecter
RecordDetecter = 0
Public LOGTYPE_HTML
Public LOGTYPE_TEXT
Public LOG_FILE

LOGTYPE_HTML = 1
LOGTYPE_TEXT = 0

LOG_FILE_TYPE = LOGTYPE_TEXT


XMLFile = "E:\inetpub\wwwroot\cc\pc\Client_Equifax-NewHire_Export.XML"

' Path is mentioned for placing log file in the server
LogFilePath = "E:\inetpub\wwwroot\cc\pc"

' Calling the main method
Call Main()

' Main method should be invoked first
Sub Main()
'Call XMLFTP()
on error resume next
WriteLog Logtime(),"Starts Execution @ "& now()
LoadXMLfiles()
SendMail()
if err.number <> 0 then
'WriteLog Logtime(),"Mail Send Err :" & err.description
end if
WriteLog Logtime(),"Stop Execution @ " & now()

End Sub


'Build the data from XML file
Function LoadXMLfiles()
on error resume next
Call OpenDBConnection()
BuildQry = XMLloopParse("RequisitionCandidate",XMLFile)
Call CloseDB()
End Function

' Parsing the XML node from XML file
function XMLParse(sFld, sXML)
dim objXML
set objXML = CreateObject("Msxml2.DOMDocument")
objXML.loadXML sXML
XMLParse = ""
If Not objXML.Load(sXML) Then
WriteLog LogTime(),"<P>XML ERROR/XML FILE NOT LOADED<br>code: " & objXML.parseError.errorCode
Elseif objXML.getElementsByTagName(sFld).length <> 0 then
XMLParse = cstr(objXML.getElementsByTagName(sFld).item(0).text)
else
XMLParse = "NULL"
end if
set xmlDoc = nothing
end function

' Open the Database connection
Sub OpenDBConnection()
' Load data into database
on error resume next
Set ConnObj = CreateObject("ADODB.Connection")
ConnObj.Open "dsn=supreme2;Database=DB2;uid=DBS;pwd=DBS;"
If ConnObj.errors.count > 0 then
WriteLog LogTime(),"Database Error : " & ConnObj.errors(0).description
else
WriteLog LogTime(),"Database Connected Sucessfully"
end if
if Err.Number <> 0 then
WriteLog LogTime(),"Database Unable to connect/Connection timeout Error:" & Err.Description
end if
End Sub

' Close the Database connection
Function CloseDB()
ConnObj.close
set ConnObj = nothing
End function

' Add New records from XML FILE to Database
Function DataManager(sFld)
Set rs = CreateObject ("ADODB.Recordset")
on error resume next
rs = ConnObj.execute(sFld)
msgbox err.description
if Err.number <> 0 then
WriteLog Logtime(), "Record Insertion Err : " & Err.Description
Else
WriteLog Logtime(), "New Record Inserted Sucessfully.."
End if
set rs = nothing
End Function

' Managing the Logfile for the events
Sub WriteLog (LTime,LogDesc)
Dim FSObj,FileObj
Forappending =8
Set FSObj = CreateObject("Scripting.FileSystemObject")

if LOG_FILE_TYPE then
LOG_FILE = LogFilePath & replace (Date(),"/","") & "_Err_Log.txt"
else
LOG_FILE = LogFilePath & replace (Date(),"/","") & "_Err_Log.html"
End if

if FSObj.FileExists(LOG_FILE) then
Set FileObj = FSObj.OpenTextFile(LOG_FILE,Forappending)
if LOG_FILE_TYPE then
FileObj.WriteLine(LTime & "|" & LogDesc)
else
FileObj.WriteLine("<tr><td>" & LTime & "</td><td>" & LogDesc & "</td></tr>")
End if
else
Set FileObj = FSObj.CreateTextFile(LOG_FILE, true)
if LOGTYPE_TEXT then
FileObj.WriteLine(LTime & "|" & LogDesc)
else
FileObj.WriteLine("<table borderColor=saddlebrown cellSpacing=1 cellPadding=1 align=left border=1>")
FileObj.WriteLine("<FONT face=Arial size=2><tr><td>Log #</td><td>Error Description</td></tr></font>")
FileObj.WriteLine("<FONT face=Arial size=2><tr><td>" & LTime & "</td><td>" & LogDesc & "</td></tr></font>")
End if
end if

'if strcomp(LTime,"EndTable") then
' FileObj.WriteLine("</table>")
'end if

FileObj.Close

set FileObj = nothing
set FSObj = nothing
End Sub

Function LogTime()
RecordDetecter = RecordDetecter + 1
LogTime = RecordDetecter & "|" & now()
End Function


function XMLloopParse(PNodename,sXML)
Set objXML = CreateObject("msxml2.DOMDocument.3.0")
objXML.async = false
objXML.setProperty "ServerHTTPRequest", True
objXML.validateOnParse = true
objXML.preserveWhiteSpace = false

on error resume next
If Not objXML.Load(sXML) Then
WriteLog LogTime(),"XML Loop Parse Err : " & objXML.parseError.errorCode
Else
Dim tempCount
Dim tempCountInLoop
tempCount = 1
tempCountInLoop = 1
phone_1_nbr(4)
Set objNodeList = objXML.getElementsByTagName(""& PNodename & "")
for i = 1 to objXML.getElementsByTagName("RequisitionCandidate").length
first_name = objXML.getElementsByTagName("FirstName").item(i-1).text
first_middle_name = objXML.getElementsByTagName("MiddleName").item(i-1).text
last_name= objXML.getElementsByTagName("LastName").item(i-1).text
name_suffix= objXML.getElementsByTagName("Suffix").item(i-1).text
addr_1_line_1= objXML.getElementsByTagName("Address1").item(i-1).text
addr_1_line_2= objXML.getElementsByTagName("Address2").item(i-1).text
addr_1_line_3= objXML.getElementsByTagName("Address3").item(i-1).text
addr_1_city_name = objXML.getElementsByTagName("City").item(i-1).text
addr_1_country_sub_entity_code = objXML.getElementsByTagName("StateProvince").item(i-1).text
addr_1_postal_code = objXML.getElementsByTagName("PostalCode").item(i-1).text
country = objXML.getElementsByTagName("Country").item(i-1).text
home_phone_1 = objXML.getElementsByTagName("HomePhone").item(i-1).text
phone_1_nbr_parse = Split(home_phone_1," ", -1, 1)
phone_1_nbr = phone_1_nbr_parse(2)
phone_1_country_code = phone_1_nbr_parse(0)
phone_1_area_city_code = phone_1_nbr_parse(1)
phone_1_extension_nbr = phone_1_nbr_parse(3)

Work_phone_2 = objXML.getElementsByTagName("WorkPhone").item(i-1).text
phone_2_nbr_parse = Split(Work_phone_2," ", -1, 1)
phone_2_nbr = phone_2_nbr_parse(2) 'Phone Number
phone_2_country_code = phone_2_nbr_parse(0) ' Country Code
phone_2_area_city_code = phone_2_nbr_parse(1) ' City Code

national_id_1= objXML.getElementsByTagName("EEOSSN").item(i-1).text
sex_code= objXML.getElementsByTagName("EEOGender").item(i-1).text
birth_date= objXML.getElementsByTagName("EEODOB").item(i-1).text
hire_date= objXML.getElementsByTagName("TargetStartDate").item(i-1).text
payroll_company_code = objXML.getElementsByTagName("Duration").item(i-1).text
job_code= objXML.getElementsByTagName("JobCode").item(i-1).text
org_unit= objXML.getElementsByTagName("CostCenter").item(i-1).text
annual_salary= objXML.getElementsByTagName("CompOfferBase").item(i-1).text



tax_state= objXML.getElementsByTagName("StateProvince").item(i-1).text
loc_code= objXML.getElementsByTagName("Location").item(i-1).text


BuildQry = "'" & first_name & "','" & first_middle_name & "'"
BuildQry = BuildQry & ",'" & last_name & "','" & name_suffix & "'"
BuildQry = BuildQry & ",'" & addr_1_line_1 & "','" & addr_1_line_2 & "'"
BuildQry = BuildQry & ",'" & addr_1_line_3 & "','" & addr_1_city_name & "'"
BuildQry = BuildQry & ",'" & addr_1_country_sub_entity_code & "','" & addr_1_postal_code & "'"
BuildQry = BuildQry & ",'" & country & "','" & phone_1_country_code & "'"
BuildQry = BuildQry & ",'" & phone_1_area_city_code & "','" & phone_1_nbr & "'"
BuildQry = BuildQry & ",'" & phone_1_extension_nbr & "','" & phone_2_area_city_code & "'"
BuildQry = BuildQry & ",'" & phone_2_nbr & "'"
BuildQry = BuildQry & ",'" & national_id_1 &"','" & sex_code & "','" & birth_date & "'"
BuildQry = BuildQry & ",'" & hire_date & "'"
BuildQry = BuildQry & ",'" & payroll_company_code & "'"
BuildQry = BuildQry & ",'" & job_code & "','" & annual_salary & "'"
BuildQry = BuildQry & ",'" & org_unit & "'"
BuildQry = BuildQry & ",'" & tax_state & "','" & loc_code & "'"
msgbox BuildQry
'
Qry = "insert into itb_147_ss_mass_new_hire ("
Qry = Qry & " first_name,first_middle_name,last_name,name_suffix,addr_1_line_1,"
Qry = Qry & " addr_1_line_2,addr_1_line_3,addr_1_city_name,addr_1_country_sub_entity_code,"
Qry = Qry & " addr_1_postal_code,country,phone_1_country_code,phone_1_area_city_code,"
Qry = Qry & " phone_1_nbr,phone_1_extension_nbr,phone_2_area_city_code,phone_2_nbr,national_id_1,"
Qry = Qry & " sex_code,birth_date,hire_date,payroll_company_code,"
Qry = Qry & " job_code,annual_salary,org_unit,tax_state,"
Qry = Qry & " loc_code) values (" & BuildQry & ")"
WriteLog "",Qry
msgbox Qry

Call DataManager(Qry)

Next
set objNodeList = Nothing
End if
if Err.Number <> 0 then
WriteLog Logtime(),"XML Inner Node Parse Err:" & Err.Description
End if
End Function

Function XMLNodeDiff(PNodename,Nodename,sXML)
Set objXML = CreateObject("msxml2.DOMDocument.3.0")
objXML.async = false
objXML.setProperty "ServerHTTPRequest", True
objXML.validateOnParse = true
objXML.preserveWhiteSpace = false

on error resume next
If Not objXML.Load(sXML) Then
WriteLog LogTime(),"XML Loop Parse Err : " & objXML.parseError.errorCode
Else
Dim tempCount
Dim tempCountInLoop
tempCount = 1
tempCountInLoop = 1
Set objNodeList = objXML.getElementsByTagName(""& PNodename & "")
For Each objNode In objNodeList
'msgbox objNode.nodeName
If strcomp(objNode.nodeName,Nodename) = 0 then
If objNode.firstChild.nodevalue <> "" Then
msgbox objNode.firstChild.nodevalue
End if
End if
For Each objNode2 In objNode.childNodes
' msgbox objNode2.nodeName
If strcomp(objNode2.nodeName,Nodename) = 0 then
If objNode2.firstChild.nodevalue <> "" Then
msgbox objNode2.firstChild.nodevalue
End if
End if
' msgbox objNode2.firstChild.nodevalue
For Each objNode3 In objNode2.childNodes
on error resume next
'msgbox objNode3.nodeName
If strcomp(objNode3.nodeName,Nodename) = 0 then
If objNode3.firstChild.nodevalue <> "" Then
msgbox objNode3.firstChild.nodevalue
End if
End if
Next
tempCountInLoop = tempCountInLoop + 1
Next
tempCount = tempCount + 1
Next
set objNodeList = Nothing
End if
if Err.Number <> 0 then
WriteLog Logtime(),"XML Inner Node Parse Err:" & Err.Description
End if
End Function

Sub SendMail()
Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

set objMsg = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "ss.efx.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "senthilkumar.elangovan@equifax.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Super78"
.Update
End With

strBody = "This is a sample message from People Click New Hire" & vbCRLF
strBody = strBody & "It was sent using PeopleClick New Hire Clients." & vbCRLF

With objMsg
Set .Configuration = objConf
.To = "senthilkumar.elangovan@equifax.com"
.From = "senthilkumar.elangovan@equifax.com"
.Subject = "TEST - People Click NewHire Upload"
.TextBody = strBody
'use .HTMLBody to send HTML email.

' .Addattachment "E:\inetpub\wwwroot\cc\pc\1152007_Err_Log.html"
'.Fields("urn:schemas:mailheader:disposition-notification-to") = "senthilkumar.elangovan@equifax.com"
'.Fields("urn:schemas:mailheader:return-receipt-to") = "senthilkumar.elangovan@equifax.com"
'.DSNOptions = cdoDSNSuccessFailOrDelay
'.Fields.update
.Send
End With
End Sub



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum