by thushara (1 Submission)
Category: Miscellaneous
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 3rd May 2007
Date Added: Mon 8th February 2021
Rating: (1 Votes)
read xml file and give it result
' load xml file into Object
On Error GoTo Errh
If Trim(strXMLFilePath) <> "" Then
'Set ObjXmlDOM = CreateObject("Microsoft.XMLDOM")
'load xmlDocument
Set ObjXmlDOM = New MSXML2.DOMDocument
ObjXmlDOM.Load (strXMLFilePath)
'MsgBox ObjXmlDOM.xml
Set rootNode = ObjXmlDOM.documentElement
If Not IsObject(rootNode) Then
MsgBox "No file Loaded", vbCritical, App.Title
OpenXMLFile = False
Exit Function
End If
OpenXMLFile = True
End If
Exit Function
Errh:
MsgBox "XML file Opening failed !" + Chr(13) + Err.Description, vbCritical, App.Title
OpenXMLFile = False
End Function
'========================================================
Public Function booImportPackingDeatilsNew(strXMLFilePath As String) As Boolean
On Error GoTo Errh
Dim objRoot
Dim strSql, strSqlDelate As String
Dim strPLNo As String
Dim strStyleNo As String
Dim strNewPlNo As String
' open the xml file
If OpenXMLFile(strXMLFilePath) = False Then
MsgBox "Data uploading failed!", vbCritical, App.Title
Exit Function
End If
Set objRoot = ObjXmlDOM.documentElement
'Set objRoot = ObjXmlDOM.selectSingleNode("List/Header/intListNo")
'ObjXmlDOM
' save for later use
strPLNo = objRoot.selectSingleNode("List/Header/intListNo").Text
strStyleNo = objRoot.selectSingleNode("List/Header/strStyleNo").Text
gConxn.BeginTrans
' strSqlDelate = "DELETE FROM Factory_Packing_Head WHERE strPackingListNo = '" & _
' objRoot.selectSingleNode("List/Header/intListNo").Text & "'"
'gConxn.Execute strSqlDelate
strNewPlNo = strGetNextPackingListNo
strSql = ""
strSql = "INSERT INTO Factory_Packing_Head(strPackingListNo, intMode, strCompany, " & _
"dblQuantity, intNoOfCartons, dblNetMass, dblGrossMass, dblNetNetMass, " & _
"strMeasurement, strCTMMeasurement, strCPONo, strBuyerPONo, dtmDate, strUser, " & _
"intStatus, intContainerType, strContainerName, strCompanyID, strPL_Type, strStyleNo, " & _
"strCATNo, strItemDescription, strFabricContent, strInvoiceNo, strLCNo, strVessel, " & _
"strLable, strCTNo, strBStyleNo, strCStyleNo, strReferenceNo, boolOrderCompletelyShip, " & _
"strLotNo, strSEALNo, strMainMarks, strSideMarks, strShipper, strConsignee, " & _
"strBuyerProgName, strTags, strHangerNo, strKellWoodItem, intRopsPerBar, intKnotsPerRops, " & _
"intPcsPerKnot, strTariffNo, dtShipDate, strManeLable, strPaperCap, strColorSize, strPortOfLoading, " & _
"strFinlDestination, strUnits, strCBM, dtPickupDate, intpreticketed, intperpack, intFlatpack, intGOH, strCustomerId, " & _
"RNNo, strProductionTeam, strBusinessUnit, strBusinessUnitPONo, " & _
"strShipToAddress , intStandedCTNQty, dblCTNL, dblCTNW, dblCTNH, strCTNMType, strPackingAccDescription, " & _
"strRNNo, strOrderNo, strLineNo, strCATLOG,strRequiredStyleDesc,strLineCode,strItemNo,strOriginalPLNO,intGarmentType) "
strSql = strSql & _
"VALUES('" & _
strNewPlNo & "', '" & _
objRoot.selectSingleNode("List/Header/intMode").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCompany").Text & "', '" & _
objRoot.selectSingleNode("List/Header/dblQuantity").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intNoOfCartons").Text & "', '" & _
objRoot.selectSingleNode("List/Header/NetMass").Text & "', '" & _
objRoot.selectSingleNode("List/Header/GrossMass").Text & "', '" & _
objRoot.selectSingleNode("List/Header/NetNetMass").Text & "', '" & _
objRoot.selectSingleNode("List/Header/Measurement").Text & "', '" & _
objRoot.selectSingleNode("List/Header/CTNMeasurement").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCPONo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strBuyerPONo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/dtmDate").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strUser").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intStatus").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strContainerType").Text & "', '"
strSql = strSql & _
objRoot.selectSingleNode("List/Header/strContainerName").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCompany").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strPL_Type").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strStyleNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCATNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strItemDescription").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strFabricContent").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strInvoiceNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strLCNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strVessel").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strLable").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCTNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strBStyleNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCStyleNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strReferenceNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/boolOrderCompletelyShip").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strLotNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strSEALNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strMainMarks").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strSideMarks").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strShipper").Text & "', '" & _
ReplaceComma(objRoot.selectSingleNode("List/Header/strConsignee").Text) & "', '"
strSql = strSql & _
objRoot.selectSingleNode("List/Header/strBuyerProgName").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strTags").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strHangerNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strKellWoodItem").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intRopsPerBar").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intKnotsPerRops").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intPcsPerKnot").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strTariffNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/dtShipDate").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strManeLable").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strPaperCap").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strColorSize").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strPortOfLoading").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strFinlDestination").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strUnits").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCBM").Text & "', '" & _
objRoot.selectSingleNode("List/Header/dtPickupDate").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intpreticketed").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intperpack").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intFlatpack").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intGOH").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCustomerId").Text & "', '" & _
"', '" & _
objRoot.selectSingleNode("List/Header/strProductionTeam").Text & "', '"
'''' strSQL = strSQL & _
'''' objRoot.selectSingleNode("List/Header/strBusinessUnit").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strBusinessUnitPONo").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strShipToAddress").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/intStandedCTNQty").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/dblCTNL").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/dblCTNW").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/dblCTNH").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strCTNMType").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strPackingAccDescription").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strRNNo").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strOrderNo").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strLineNo").Text & "', '" & _
'''' objRoot.selectSingleNode("List/Header/strCATLOG").Text & "' ')"
strSql = strSql & _
objRoot.selectSingleNode("List/Header/strBusinessUnit").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strBusinessUnitPONo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strShipToAddress").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intStandedCTNQty").Text & "', '" & _
objRoot.selectSingleNode("List/Header/dblCTNL").Text & "', '" & _
objRoot.selectSingleNode("List/Header/dblCTNW").Text & "', '" & _
objRoot.selectSingleNode("List/Header/dblCTNH").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCTNMType").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strPackingAccDescription").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strRNNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strOrderNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strLineNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strCATLOG").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strRequiredStyleDesc").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strLineCode").Text & "', '" & _
objRoot.selectSingleNode("List/Header/strItemNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intListNo").Text & "', '" & _
objRoot.selectSingleNode("List/Header/intGarmentType").Text & "')"
gConxn.Execute strSql
' **************** Save Color Informations **************************
Set z = objRoot.selectSingleNode("//ColorDetails")
' delete exsiting data
strSqlDelate = "DELETE FROM Factory_PackingColors WHERE strPackingListNo = '" & strPLNo & "'"
gConxn.Execute strSqlDelate
For Each Child In z.childNodes
If UCase(Child.nodeName) = "CROWS" Then
strSql = "INSERT INTO Factory_PackingColors(strPackingListNo, strStyleId, intColorId, " & _
"strColor, strColorCode) " & _
"VALUES('" & _
strPLNo & "', '" & _
strStyleNo & "', '" & _
Child.selectSingleNode("intColorId").Text & "', '" & _
Child.selectSingleNode("strColor").Text & "', '" & _
Child.selectSingleNode("strColorCode").Text & "')"
gConxn.Execute strSql
End If
Next
' *******************************************************************
'' ################## Saving Size Ratio Infromations #####################
Set z = objRoot.selectSingleNode("//SizeRatio")
' delete exsiting data
strSqlDelate = "DELETE FROM Factory_Packing_Ratio WHERE strPackingListNo = '" & strPLNo & "' " & _
"And strStyleId = '" & strStyleNo & "'"
gConxn.Execute strSqlDelate
For Each Child In z.childNodes
If UCase(Child.nodeName) = "CROWS" Then
strSql = "INSERT INTO Factory_Packing_Ratio(strPackingListNo, strStyleId, strSize, dblRatio) " & _
"VALUES('" & _
strPLNo & "', '" & _
strStyleNo & "', '" & _
Child.selectSingleNode("strSize").Text & "', '" & _
Child.selectSingleNode("strRatio").Text & "')"
gConxn.Execute strSql
End If
Next
'' ##########################################################################
If UCase(objRoot.selectSingleNode("List/Header/strPL_Type").Text) = "CTRN" Then
Set z = objRoot.selectSingleNode("//CartonDetails")
' delete exsiting data
'' strSqlDelate = "DELETE FROM Factory_Packing_Cartons WHERE strPackingListNo = '" & strPLNo & "'"
'' gConxn.Execute strSqlDelate
For Each Child In z.childNodes
If UCase(Child.nodeName) = "ROWS" Then
Dim strmes As String
strmes = Child.selectSingleNode("strMeasurement").Text
strSql = "INSERT INTO Factory_Packing_Cartons(strPackingListNo, strStyleId, strContainerName, " & _
"intCTNFrom, intCTNTo, strColor, strSize, dblQty, dblGrossMass, dblNetMass, " & _
"dblNetNetMass, strMeasurement, intStatus, dblPrePackA, dblPrePackB, dblPrePackC,strLineCode,intTotQty,strBuyerPONo) " & _
"VALUES('" & _
strNewPlNo & "', '" & _
strStyleNo & "', '" & _
Child.selectSingleNode("strContainerName").Text & "', '" & _
Child.selectSingleNode("intCTNFrom").Text & "', '" & _
Child.selectSingleNode("intCTNTo").Text & "', '" & _
Child.selectSingleNode("strColor").Text & "', '" & _
Child.selectSingleNode("strSize").Text & "', '" & _
Child.selectSingleNode("dblQty").Text & "', '" & _
Child.selectSingleNode("dblCGrossMass").Text & "', '" & _
Child.selectSingleNode("dblCNetMass").Text & "', '" & _
Child.selectSingleNode("dblCNetNetMass").Text & "', '" & _
strmes & "', '', '" & _
Child.selectSingleNode("dblPrePackA").Text & "', '" & _
Child.selectSingleNode("dblPrePackB").Text & "', '" & _
Child.selectSingleNode("dblPrePackC").Text & "', '" & _
Child.selectSingleNode("strLineCode").Text & "', '" & _
Child.selectSingleNode("intTotQty").Text & "', '" & _
Child.selectSingleNode("strBuyerPONo").Text & "')"
gConxn.Execute strSql
End If
Next
Else ' packing list type is Hanging
Set z = objRoot.selectSingleNode("//HangerDetails")
' delete exsiting data
strSqlDelate = "DELETE FROM Factory_Packing_Hangers WHERE strPackingListNo = '" & strPLNo & "'"
gConxn.Execute strSqlDelate
For Each Child In z.childNodes
If UCase(Child.nodeName) = "ROWS" Then
strSql = "INSERT INTO Factory_Packing_Hangers(strPackingListNo, strStyleId, strContainerName, " & _
"intBarNo, intStringFrom, intStringTo, intKnotFrom, intKnotTo, strColor, strSize, " & _
"dblQty, dblGrossMass, dblNetMass, dblNetNetMass,strMeasurement, intStatus, strCutTktNo, strRopeNO, strPCSKnot,strBuyerPONo) " & _
"VALUES('" & _
strNewPlNo & "', '" & _
strStyleNo & "', '" & _
"" & "', '" & _
Child.selectSingleNode("intBarNo").Text & "', '" & _
Child.selectSingleNode("intSFrom").Text & "', '" & _
Child.selectSingleNode("intSTo").Text & "', '" & _
Child.selectSingleNode("intKnotFrom").Text & "', '" & _
Child.selectSingleNode("intKnotTo").Text & "', '" & _
Child.selectSingleNode("strColor").Text & "', '" & _
Child.selectSingleNode("strSize").Text & "', '" & _
Child.selectSingleNode("dblQty").Text & "', '" & _
Child.selectSingleNode("dblHGrossMass").Text & "', '" & _
Child.selectSingleNode("dblHNetMass").Text & "', '" & _
Child.selectSingleNode("dblHNetNetMass").Text & "', '" & _
"" & "', '', '" & _
Child.selectSingleNode("strCutTktNo").Text & "', '" & _
Child.selectSingleNode("strRopeNO").Text & "', '" & _
Child.selectSingleNode("strPCSKnot").Text & "', '" & _
Child.selectSingleNode("strBuyerPoNo").Text & "')"
gConxn.Execute strSql
End If
Next
End If
gConxn.CommitTrans
booImportPackingDeatilsNew = True
Exit Function
Errh:
' If Err.Number = 91 Then
' Resume Next
' End If
MsgBox "Packing-list retrieving failed!" + Chr(13) + Err.Description, vbCritical, App.Title
booImportPackingDeatilsNew = False
gConxn.RollbackTrans
End Function