Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    New to the CF scene
    Join Date
    Nov 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Urgent : Help me Out VB Script with XML

    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
    Last edited by gtsenthil; 11-14-2007 at 09:53 PM. Reason: Code and Comment change


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •