Visual Basic

21-Oct-09 22:51

Hey there

I'm having trouble getting one element out of this XML file..

<sizedata Size= "VALUE" IGNORE THE REST OF THEM/>

here is a fully formed, but shortened XML file:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Root Type="TRoot">
<Date>15/10/2009 12:30:08</Date>
<Folder fullpath="C:\" IsFilesNode="0">
<Name>C:\</Name>
<SizeData Size="74609238013" Allocated="72396726232" Wasted="234062016" CDRom="74727184384" Files="116733" Folders="12272" Compression="3"/>
</Folder>
<Folder fullpath="C:\" IsFilesNode="-1">
<Name>[Files]</Name>
<SizeData Size="18745407271" Allocated="18745561088" Wasted="153817" CDRom="18745475072" Files="69" Folders="0" Compression="1"/>
</Folder>
<Folder fullpath="C:\TEMP_\" IsFilesNode="0">
<Name>TEMP_ALAN</Name>
<SizeData Size="15469174140" Allocated="15489126222" Wasted="19886724" CDRom="15478267904" Files="9570" Folders="1832" Compression="1"/>
</Folder>
<Folder fullpath="C:\TEMP_\mp3\" IsFilesNode="0">
<Name>mp3</Name>
<SizeData Size="11504514137" Allocated="11513361814" Wasted="8829863" CDRom="11508457472" Files="4561" Folders="510" Compression="1"/>
</Folder>
<Folder fullpath="C:\TEMP_\mp3\My Music\" IsFilesNode="0">
<Name>My Music</Name>
<SizeData Size="11247143252" Allocated="11255940398" Wasted="8779436" CDRom="11251054592" Files="4532" Folders="506" Compression="1"/>
</Folder>
<Folder fullpath="C:\TEMP_\mp3\My Music\MP3\" IsFilesNode="0">
<Name>MP3</Name>
<SizeData Size="11074836494" Allocated="11083256268" Wasted="8402930" CDRom="11078576128" Files="4357" Folders="481" Compression="1"/>
</Folder>
<Folder fullpath="C:\TEMP_\mp3\My Music\MP3\MP3\" IsFilesNode="0">
<Name>MP3</Name>
<SizeData Size="9808652194" Allocated="9813168460" Wasted="4507742" CDRom="9810542592" Files="2458" Folders="242" Compression="1"/>
</Folder>
</Root>

The code I have is below:

<code>Try

   Dim odoc As New System.Xml.XmlDocument
   odoc.Load("C:\test\test.xml")
   Dim oXmlLog As System.Xml.XmlElement
   Dim text As String = ""
   For Each oXmlLog In odoc.SelectNodes("Root")
         Dim node As System.Xml.XmlElement
            For Each node In oXmlLog.SelectNodes("Folder")
               Dim fullpath As String = node.Attributes.GetNamedItem("fullpath").Value
               'Dim SizeData As String = node.Attributes.GetNamedItem("SizeData").Value
               Dim subnode = node.SelectSingleNode("Name")
               Dim name As String = subnode.InnerText

               Dim Date_ As XmlElement = odoc.DocumentElement
               Dim Date_time As XmlNodeList = Date_.ChildNodes
               Dim Date__time = (Date_time(0).InnerText)

               text &= Date__time   & fullpath   & name & SizeData & Environment.NewLine
               Next
            Next

            Console.Write(text)
            Console.Read()
         Catch ex As Exception
         Console.Write(ex.ToString())
         Console.Read()
      End Try</code>

But I can't get the SIZE from SIZEDATA out and into a variable, can you help???

Christian Graus21-Oct-09 23:42

21-Oct-09 23:42

InnerText will give you all the inner nodes as well. Use .Value instead.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

nhsal6922-Oct-09 0:19

22-Oct-09 0:19

Thanks for your reply...

Christian Graus wrote:
InnerText will give you all the inner nodes as well. Use .Value instead.

So how should this line be formed then ??

'Dim SizeData As String = node.Attributes.GetNamedItem("SizeData").Value

I have tried:

Dim subnode1 = node.SelectSingleNode("SizeData").Value
Dim Size As String = subnode1.InnerText

But this won't compile...

Any thoughts??

Christian Graus22-Oct-09 0:49

22-Oct-09 0:49

Perhaps if you told me the error, but .Value should return a string, not a node. That's the point, it REPLACES the call to InnerText, which you can't use, it won't work.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

nhsal6922-Oct-09 1:42

22-Oct-09 1:42

Sorry, I'm pretty new to this and am now a bit confused. In the original code above:

Dim fullpath As String = node.Attributes.GetNamedItem("fullpath").Value

Works and returns a value for "FullPath"

Dim SizeData As String = node.Attributes.GetNamedItem("SizeData").Value

Doesn't work and gives an "System.NullReferenceException" Error when compiled.

When that line is commented out the code returns all expected results.

Am I missing something here as the "fullpath" line in the XML is of the same form as the "SizeData" one.. So why do I get the error???

John M Bundy22-Oct-09 9:07

John M Bundy

22-Oct-09 9:07

You are going about it in a kind of roundabout way (i prefer XPath) but I digress, you were on the correct path with your "subnode" line. Following your method, I just add two lines (your original subnode is the same).

Dim subnode = node.SelectSingleNode("Name")
               Dim subnode2 = node.SelectSingleNode("SizeData")
               Dim SizeData As String = subnode2.Attributes.GetNamedItem("Size").Value

nhsal6922-Oct-09 21:29

22-Oct-09 21:29

Fantastic.. it's working....

here is the final code :

Try

    Dim odoc As New System.Xml.XmlDocument
    odoc.Load("C:\test\10g_2.xml")
    Dim oXmlLog As System.Xml.XmlElement
    Dim text As String = ""
    For Each oXmlLog In odoc.SelectNodes("Root")
        Dim node As System.Xml.XmlElement
        For Each node In oXmlLog.SelectNodes("Folder")
            Dim fullpath As String = node.Attributes.GetNamedItem("fullpath").Value

            'Dim SizeData As String = node.Attributes.GetNamedItem("SizeData").Value

            Dim subnode = node.SelectSingleNode("Name")
            Dim name As String = subnode.InnerText

            Dim subnode2 = node.SelectSingleNode("SizeData")
            Dim SizeData As String = subnode2.Attributes.GetNamedItem("Size").Value


            Dim Date_ As XmlElement = odoc.DocumentElement
            Dim Date_time As XmlNodeList = Date_.ChildNodes
            Dim Date__time = (Date_time(0).InnerText)



            text &= Date__time & " " & fullpath & " " & name & " " & SizeData & Environment.NewLine
        Next
    Next

    Console.Write(text)
    Console.Read()
Catch ex As Exception
    Console.Write(ex.ToString())
    Console.Read()
End Try

Now I just need to get it to pass this info to SQL..
Thanks again...

cmd.executenonquery not accepting nz function

John M Bundy23-Oct-09 4:38

John M Bundy

23-Oct-09 4:38

Just because it might be easier for you to work with, take a look at this method. Drag a datagridview onto your form designer and put in the following code, change the table to see your different output. VS is very good at converting between XML and Datasets and vice-versa. If nothing else, the tables will help you visualize the structure of the XML, you can easily see from this sample that size is on a different 'level' than the rest of the data.

Imports System.IO
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'load xml into a dataset to use here
        Dim dS As New DataSet
        Dim fS As FileStream
        'open the xml file so we can use it to fill the dataset
        fS = New FileStream("C:\test\10g_2.xml", FileMode.Open)
        'fill the dataset
        Try
            dS.ReadXml(fS)
        Catch ex As Exception
            MsgBox(ex)
        Finally
            fS.Close()
        End Try
        Me.DataGridView1.DataSource = dS.Tables(2)

    End Sub
End Class

rabih.tawil21-Oct-09 20:57

21-Oct-09 20:57

Hello Community,
I have the following query:

SELECT Inventory.Inventory_Id, Inventory.Inventory_description, Inventory.Inventory_stock_count, nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS CurrentlyOut, [Inventory_stock_count]-Nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS NetHere
FROM Inventory;

This query runs perfectly under data access,

But when i put it under vb.net

my query1 = "SELECT Inventory.Inventory_Id, Inventory.Inventory_description, Inventory.Inventory_stock_count, nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS CurrentlyOut, [Inventory_stock_count]-Nz((SELECT Sum(Order.item_qty) AS SumOfitem_qty
FROM [Order]
WHERE Order.From_date<=Date() AND (Order.To_date)>=Date()
AND Order.Item_ID="I" & Inventory.Inventory_Id),0) AS NetHere
FROM Inventory"


cmd.commadtext = myquery1

cmd.executenonquery()

VB.net compiler do not seem to like Nz function that is same as isnull in sql or NVL in oracle & i see this error

Undefined function 'nz' in expression.

it seems that vb.net is not being able to understand the nz function that replace with 0 . nz replaces null values with 0. i really need nz to be present in the query.

what is a way around this?

Best regards.

Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com

Ashfield21-Oct-09 21:11

Ashfield

21-Oct-09 21:11

1. You need extra quotes around the I part of ="I" (both places) as this will be terminating your string, I'm suprised it compiled

2. cmd.executenonquery()is not for selects, it does not return a data reader, its for update type statements where there is no recordset returned

3. What nz does I don't know, its not a sql command I've come across, so can't help on that, but the other two may get you somewhere.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

rabih.tawil21-Oct-09 21:27

21-Oct-09 21:27

Order.Item_ID='I'

Sorry about that, that is the way i wrote it in vb.

you need to understand that when i execute the query without NZ it's fine...

i am taking the cmd.executenonquery in a dataset & i can see the values normaly in a grid but the problem is that the blank values returned when not replaced with 0 are making me a problem with calculations.

Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com

Mycroft Holmes21-Oct-09 21:11

Mycroft Holmes

21-Oct-09 21:11

nz is an old Access/VB function that does not exist in SQL server. what you need is ISNULL()

Sum(ISNULL(Order.item_qty,0))

rabih.tawil21-Oct-09 21:22

21-Oct-09 21:22

this is the query i tested with:

select sum(ISNULL(inventory_id,0)) from inventory

this is the error:

Wrong number of arguments used with function in query expression 'SUM(ISNULL(inventory_id,0)'

Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com

Mycroft Holmes21-Oct-09 21:33

Mycroft Holmes

21-Oct-09 21:33

Sorry, I use SQL Server, I have no idea what the syntax for Oracle is. However you are on the right track, SQL Server has Books On Line which is installed with every client, I imagine Oracle something similar.

rabih.tawil21-Oct-09 21:36

21-Oct-09 21:36

Sorry maybe you misunderstood me prior...

Frontend: vb.net
Backend: MS access 2007

it seems that vb.net does not like ISNULL too...

Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com

Christian Graus21-Oct-09 22:03

21-Oct-09 22:03

rabih.tawil wrote:
it seems that vb.net does not like ISNULL too...

I don't think you understand your own code at all. VB is just passing 'ISNULL' to Access, it has no opinion on it at all. You need to read up on Access SQL, but Access is not that great, I would not be surprised to find it was missing some stuff.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

rabih.tawil21-Oct-09 22:22

21-Oct-09 22:22

Christian Graus wrote:
I don't think you understand your own code at all

Look my friend, this is a 3 month project, i think i understand exactly what i'm doing, just because it has not come accross you before does not mean i don't know what i'm doing...

Christian Graus wrote:
VB is just passing 'ISNULL' to Access

OH REALLY? wow great discovery!! now explain to me, why VB wont pass NZ function directly to access???

Christian Graus wrote:
it has no opinion on it at all.

I am sorry to tell you my friend, NO you are wrong, the sql query u send will execute depends on the access driver.

Christian Graus wrote:
ou need to read up on Access SQL

The query executes successfully in access.. no need to read anything about access.

Christian Graus wrote:
but Access is not that great, I would not be surprised to find it was missing some stuff.

Humm, that is your opinion, remember MS access is not a small application made over night for a quick criticism even if one is an mvp. however, the customer wanted MS access so i give him access Smile | :)

Share your knowledge it's one way to achieve immortality
http://www.rabihtawil.com
http://blog.rabihtawil.com
http://www.coderisland.com

Christian Graus21-Oct-09 23:15

21-Oct-09 23:15

rabih.tawil wrote:
Look my friend, this is a 3 month project, i think i understand exactly what i'm doing, just because it has not come accross you before does not mean i don't know what i'm doing...

ROTFL. OK, perhaps you just meant the opposite of what you said. I apologise.

rabih.tawil wrote:
OH REALLY? wow great discovery!! now explain to me, why VB wont pass NZ function directly to access???

Now it seems to me like you have no idea after all. VB IS going to pass your code directly to Access. What else would it do with them ?

rabih.tawil wrote:
I am sorry to tell you my friend, NO you are wrong, the sql query u send will execute depends on the access driver.

Wrong. The generic driver will use ODBC ( I think ), but whatever it uses, it will NOT interpret the SQL, it will pass it to the database.

rabih.tawil wrote:
Humm, that is your opinion, remember MS access is not a small application made over night for a quick criticism even if one is an mvp.

No, it's a POS that was developed a bit at a time, over time, with no thought to conformance to SQL standards.

rabih.tawil wrote:
however, the customer wanted MS access so i give him access

Well, fine. So now you need to learn what the Access SQL dialect is like, and use it accordingly

I did a google, and Access does appear to support ISNULL.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

Andy_L_J21-Oct-09 22:06

Andy_L_J

21-Oct-09 22:06

From here webcheatsheet[^]

In Access, the IsNull function returns a boolean value that indicates whether or not an expression contains no valid data (Null).

The syntax for the IsNull function is:
IsNull ( expression )

In Access, the expression argument can be any numeric or string expression. The IsNull function returns True (-1) if the expression contains the Null value; otherwise, it returns False (0). Null is not the same as a zero-length string, which is often referred to as a null string.

Important: Using the IsNull function is the only way from within Access to determine whether or not an expression contains a null value.
Example

IsNull(null)                returns TRUE
IsNull("WebCheatSheet")     returns FALSE
IsNull(2006)                returns FALSE

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Christian Graus21-Oct-09 23:41

21-Oct-09 23:41

Andy_L_J wrote:
IsNull("WebCheatSheet") returns FALSE
IsNull(2006) returns FALSE

LOL - looks like you got the same google hit I did.

Christian Graus

Driven to the arms of OSX by Vista.

Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.