check if there is a data or not

[cat5ive]

Hi,

How do I write a statement to check if a certain element has data or not?
Here is my code, which is not work. I want to check if <invoicenumber> element does not have any data, then don't display the column. It's always show the column, even there is no invoice# in the data.

Thanks in advance

if (xmldoc.SelectNodes ("//invoicenumber").length > 0) then
     columnlabels(i) = "Invoice #"
     oShowInvoice = true
     i = i + 1
  else
     oShowInvoice = false	 
  end if

 

[cat5ive]

Here is an example xml

<podata>
 <itemrecord>
 <ponumber>1234</ponumber>
 <invoicenumber></invoicenumber>
 </itemrecord>

 <itemrecord>
 <ponumber>5678</ponumber>
 <invoicenumber></invoicenumber>
 </itemrecord>
</podata>

How to check if all the invoicenumber is empty or not, without looping thru it?

Thanks in advance

 

[DotNetGnat]

something like this:

Dim objTest 
Set objTest = podata(counter).selectSinglenode("invoicenumber").firstChild 
if Not (objTest is Nothing) Then  
Response.Write objTest.nodeValue 
End if

also, look into isobject()...something like this may also help...

if isObject(podata(counter).selectSinglenode("invoicenumber")) Then 
Response.Write "test1" 
Else 
Response.Write "test2" 
End if

-DNG

 

[JontyMC]

Might be easier to write an XSL stylesheet

Jon

"I don't regret this, but I both rue and lament it.

 

[cat5ive]

It works. Thank you so much.

 

[DotNetGnat]

great...glad to be of help...

-DNG

 

[cat5ive]

Hi,

Somehow it's not working any more. In the beginning I tried with only 2 elements (<itemnumber> and <department>). I swear that it worked. When I changed all the other elements, then it's not working any more. Seem like it only detect the first row of data. For example, if I have itemnumber on the 2nd row and the 1st row is an empty element, then my oShowItem got turn off. But if I have data item# in the first element, then oShowItem is on.
Here is my code.

Please help!

<%
... some variable set up ....

  queryString = as400url + username + "+landedcost+" + fileid

  Set xmldoc = ReadXMLFromAS400 (queryString)

  customername = ReadXML (xmldoc, "//customername")
  entrynumber = ReadXML (xmldoc, "//entrynumber")
  crossreference = ReadXML (xmldoc, "//crossreference")

  set nodelist = xmldoc.selectNodes("//itemrecord")
  
  // Set up columns for excel, only show column if there is data 
  
  dim columnlabels(), columntags(), columnwidths()
  dim objTest 
  redim preserve columnlabels (50)
  redim preserve columntags (50)
  redim preserve columnwidths (50)
      
  i = 0
 
  Set objTest = nodelist(counter).selectSinglenode("invoicenumber").firstChild 
  if Not (objTest is Nothing) Then 
     columnlabels(i) = "Invoice #"
     oShowInvoice = true
     columnwidths(i) = "70"
     i = i + 1
  else
     oShowInvoice = false	 
  end if
  
  Set objTest = nodelist(counter).selectSinglenode("ponumber").firstChild
  if Not (objTest is Nothing) Then  
     columnlabels(i) = "P.O.#"
     oShowPONumber = true
     columnwidths(i) = "70"
     i = i + 1
  else 
	 oShowPONumber = false
  end if
			
  Set objTest = nodelist(counter).selectSinglenode("itemnumber").firstChild
  if Not (objTest is Nothing) Then  
    columnlabels(i) = "Item/ SKU/ Style"
    oShowItem = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowItem = false
  end if
  
  Set objTest = nodelist(counter).selectSinglenode("store").firstChild 
  if Not (objTest is Nothing) Then 
    columnlabels(i) = "Store/ Div"
    oShowStore = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowStore = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("department").firstChild 
  if Not (objTest is Nothing) Then  
    columnlabels(i) = "Department"
    oShowDept = true
    columnwidths(i) = "70"
    i = i + 1
  else
	oShowDept = false	
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("htsnumber").firstChild 
  if Not (objTest is Nothing) Then  
    columnlabels(i) = "HTS No."
    oShowHTS = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowHTS = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("origincountry").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Origin Country"
    oShowOriginCntry = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowOriginCntry = false
  end if
  
  Set objTest = nodelist(counter).selectSinglenode("manufacturerid").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "MID #"
    oShowMID = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowMID = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("spi").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "SPI"
    oShowSPI = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowSPI = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("masterbilllading").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Master BL/AWB #"
    oShowMBL = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowMBL = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("housebilllading").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "House BL/AWB #"
    oShowHBL = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowHBL = false
  end if
		
  Set objTest = nodelist(counter).selectSinglenode("containernumber").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Container #"
    oShowContainer = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowContainer = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("containertype").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Container Type"
    oShowContainerType = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowContainerType = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("cartons").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "CTN"
    oShowCTN = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowCTN = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("pieces").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Pieces"
    oShowPieces = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowPieces = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("unitprice").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Unit Price"
    oShowUnitPrice = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowUnitPrice = false
  end if
  
  Set objTest = nodelist(counter).selectSinglenode("cubicmeter").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Cubic Mtr"
    oShowCBM = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowCBM = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("weight").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Weight"
    oShowWeight = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowWeight = false
  end if
  
  Set objTest = nodelist(counter).selectSinglenode("value").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Value"
    oShowValue = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowValue = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("duty").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Duty"
    oShowDuty = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowDuty = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("advaloremrate").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Duty Rate (Advalorem)"
    oShowAdvaloremRate = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowAdvaloremRate = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("specificrate").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Duty Rate (Specific)"
    oShowSpecificRate = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowSpecificRate = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("supplementalrate").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Duty Rate (Supplemental)"
	oShowSupplementalRate = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowSupplementalRate = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("hmf").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "HMF"
    oShowHMF = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowHMF = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("mpf").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "MPF"
    oShowMPF = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowMPF = false
  end if
  
  Set objTest = nodelist(counter).selectSinglenode("freight").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Freight"
    oShowFreight = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowFreight = false
  end if
		
  Set objTest = nodelist(counter).selectSinglenode("exam").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Exam"
    oShowExam = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowExam = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("assistcost").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Assist Cost"
    oShowAssist = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowAssist = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("tax").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Tax"
    oShowTax = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowTax = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("cottonfee").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Cotton Fee"
    oShowCotton = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowCotton = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("otherfee").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Other Fee"
    oShowOtherFee = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowOtherFee = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("brokerfee").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Broker Fee"
    oShowBroker = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowBroker = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("othercharge").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "Other Charge"
    oShowOtherCharge = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowOtherCharge = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("addcasenumber").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "ADD #"
    oShowADDNumber = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowADDNumber = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("addduty").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "ADD Duty"
    oShowADDDuty = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowADDDuty = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("cvdcasenumber").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "CVD #"
    oShowCVDNumber = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowCVDNumber = false
  end if
	
  Set objTest = nodelist(counter).selectSinglenode("cvdduty").firstChild 
  if Not (objTest is Nothing) Then
    columnlabels(i) = "CVD Duty"
    oShowCVDDuty = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowCVDDuty = false
  end if
	
%>

 

[JontyMC]

This would be a LOT easier with a stylesheet. If you don't wanna use a stylesheet, I'd at least reduce the code with a function.

Jon

"I don't regret this, but I both rue and lament it.

 

[cat5ive]

I don't know how to use a stylesheet. I tried to read about it. If I understand correctly, it seem like I have to create XLS file (beside from XML that I already have). So it doesn't look easier to me.
Thank you for suggesting about creating a function. I will do that after I can get my code to work. So can someone please point out to me what's wrong with my code.

Thanks

 

[cat5ive]

Hi,

Where are you DNG (or anyone)? Please help! May be my explaination is not clear?

If my xml file like this, it will turn on the oShowItem

<podata>
 <itemrecord>
 <ponumber>1234</ponumber>
 <invoicenumber></invoicenumber>
[COLOR=red] <itemnumber>abcd</itemnumber> [/color] 
 </itemrecord>

 <itemrecord>
 <ponumber>5678</ponumber>
 <invoicenumber></invoicenumber>
[COLOR=red]<itemnumber></itemnumber> [/color] 

 </itemrecord>
</podata>

But this one will not turn on the oShowItem

<podata>
 <itemrecord>
 <ponumber>1234</ponumber>
 <invoicenumber></invoicenumber>
[COLOR=red]<itemnumber></itemnumber> [/color] 
 </itemrecord>

 <itemrecord>
 <ponumber>5678</ponumber>
 <invoicenumber></invoicenumber>
[COLOR=red]<itemnumber>abcd</itemnumber> [/color] 

 </itemrecord>
</podata>

Thanks in advance

 

[DotNetGnat]

sorry...i was busy replying in other forums...totally forgot about this one...

i am still not sure with the problem you are facing...

i would suggest you to start with the simple 2 level one that was working in the beginning and build it from there step by step until you hit the error again...i think there is something wrong with the looping...i will check into the code...

-DNG

 

[cat5ive]

Hi DNG,

I tried to put everything back and test only those 2 nodes like you suggested. Somehow it did not work too. It gave me the same result (oShowItem only turn on when data is found on the first <itemnumber> tag).


Thanks

 

[DotNetGnat]

show the code that shows only two nodes...and what error are you getting..

-DNG

 

[cat5ive]

Hi,

I did not get any error. It just did not work the way I wanted.
What I wanted to do is to display the data in excel view. But I only want to create a column for the element only if there is data.

For example if my data contain item# then my excel would be like this.

invoice#       p.o.#      item/SKU/Style    col4    col5
1234444        890                           ...     ...
2345           901        ggg                ...     ...

But if my data doesn't have item# at all (all itemnumber tags are empty), then I won't create column for it. So it would look something like this.

invoice#       p.o.#          Store/Div     col4    col5
1234444        890            CAT           ...     ...
2345           901            POP           ...     ...

It did not work because seem like this statement

Set objTest = nodelist(counter).selectSinglenode("itemnumber").firstChild
if Not (objTest is Nothing) Then
will detect that the objTest is nothing if my first tag of itemnumber is empty, regardless if I have data inside the other itemnumber tag or not. So as in my first sample above, the column item/SKU/Style won't get created.

Here is my code.
Thanks.

<%
// START OF LOGIC
// Verify user has logged-on.
  CheckLogin ()

// Set-up variables.
  as400url = "[URL unfurl="true"]http://...?"[/URL]
  accountstring = request ("customer")
  username = session ("username")
  fileid = request ("fileid")
  xlslink = Request.ServerVariables("URL") + "?" + Request.ServerVariables("QUERY_STRING") + "&excelview=true"
  
 

// START BUSINESS LOGIC
        
   queryString = as400url + username + "+landedcost+" + fileid

  
  Set xmldoc = ReadXMLFromAS400 (queryString)

  customername = ReadXML (xmldoc, "//customername")
  entrynumber = ReadXML (xmldoc, "//entrynumber")
  crossreference = ReadXML (xmldoc, "//crossreference")

  set nodelist = xmldoc.selectNodes("//itemrecord")
  
  // Set up columns for excel, only show column if there is data 
  
  dim columnlabels(), columntags(), columnwidths()
  dim objTest 
  redim preserve columnlabels (50)
  redim preserve columntags (50)
  redim preserve columnwidths (50)
      
  i = 0
 
  
  
     columnlabels(i) = "Invoice #"
     oShowInvoice = true
     columnwidths(i) = "70"
     i = i + 1
   
     columnlabels(i) = "P.O.#"
     oShowPONumber = true
     columnwidths(i) = "70"
     i = i + 1
  
  
  Set objTest = nodelist(counter).selectSinglenode("itemnumber").firstChild
  if Not (objTest is Nothing) Then 
    columnlabels(i) = "Item/ SKU/ Style"
	oShowItem = true
    columnwidths(i) = "70"
    i = i + 1
  else
    oShowItem = false
  end if
  
 
    columnlabels(i) = "Store/ Div"
    oShowStore = true
    columnwidths(i) = "70"
    i = i + 1
 
	
  Set objTest = nodelist(counter).selectSinglenode("department").firstChild 
  if Not (objTest is Nothing) Then  
    columnlabels(i) = "Department"
    oShowDept = true
    columnwidths(i) = "70"
    i = i + 1
  else
	oShowDept = false	
  end if
	
  
   
    columnlabels(i) = "HTS No."
    oShowHTS = true
    columnwidths(i) = "70"
    i = i + 1
 
	columnlabels(i) = "Origin Country"
    oShowOriginCntry = true
    columnwidths(i) = "70"
    i = i + 1
  
 
    columnlabels(i) = "MID #"
    oShowMID = true
    columnwidths(i) = "70"
    i = i + 1
 
 
    columnlabels(i) = "SPI"
    oShowSPI = true
    columnwidths(i) = "70"
    i = i + 1
  
 
    columnlabels(i) = "Master BL/AWB #"
    oShowMBL = true
    columnwidths(i) = "70"
    i = i + 1
 	
 
     columnlabels(i) = "House BL/AWB #"
    oShowHBL = true
    columnwidths(i) = "70"
    i = i + 1
  	
  
    columnlabels(i) = "Container #"
    oShowContainer = true
    columnwidths(i) = "70"
    i = i + 1
  
 
    columnlabels(i) = "Container Type"
    oShowContainerType = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "CTN"
    oShowCTN = true
    columnwidths(i) = "70"
    i = i + 1

  
    columnlabels(i) = "Pieces"
    oShowPieces = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "Unit Price"
    oShowUnitPrice = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "Cubic Mtr"
    oShowCBM = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "Weight"
    oShowWeight = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "Value"
    oShowValue = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "Duty"
    oShowDuty = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "Duty Rate (Advalorem)"
    oShowAdvaloremRate = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "Duty Rate (Specific)"
    oShowSpecificRate = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "Duty Rate (Supplemental)"
	oShowSupplementalRate = true
    columnwidths(i) = "70"
    i = i + 1

    columnlabels(i) = "HMF"
    oShowHMF = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "MPF"
    oShowMPF = true
    columnwidths(i) = "70"
    i = i + 1

    columnlabels(i) = "Freight"
    oShowFreight = true
    columnwidths(i) = "70"
    i = i + 1

    columnlabels(i) = "Exam"
    oShowExam = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "Assist Cost"
    oShowAssist = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "Tax"
    oShowTax = true
    columnwidths(i) = "70"
    i = i + 1

    columnlabels(i) = "Cotton Fee"
    oShowCotton = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "Other Fee"
    oShowOtherFee = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "Broker Fee"
    oShowBroker = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "Other Charge"
    oShowOtherCharge = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "ADD #"
    oShowADDNumber = true
    columnwidths(i) = "70"
    i = i + 1
  
    columnlabels(i) = "ADD Duty"
    oShowADDDuty = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "CVD #"
    oShowCVDNumber = true
    columnwidths(i) = "70"
    i = i + 1
 
    columnlabels(i) = "CVD Duty"
    oShowCVDDuty = true
    columnwidths(i) = "70"
    i = i + 1
 
	
 %>

 

[DotNetGnat]

shouldnt this...

Set objTest = [red]nodelist[/red](counter).selectSinglenode("itemnumber").firstChild

be...

Set objTest = [red]podata[/red](counter).selectSinglenode("itemnumber").firstChild

or is that you just gave a sample xml with root node as podata?

-DNG

 

[DotNetGnat]

oops...i guess i was wrong...

time to go to bed...

-DNG

 

[cat5ive]

podata" was just my sample (I wrote my post at home and couldn't remember the real name.) My actual root node name is "landedcostrecord". I already tried using that and it gave me error msg, something like 'type not match'. That's why I use 'nodelist' instead.

Set objTest = landedcostrecord(counter).selectSinglenode("itemnumber").firstChild

May be I need to define some variable or create some object first?

 

[cat5ive]

Hi,

Finally I found out the way to check. This will return # of elements that contain data. So when all my itemnumber elements are empty, it returns 0.

set nodelist = xmldoc.selectNodes("//itemrecord[itemnumber != '']")
dim test
response.write  nodelist.length