locked
ID column from an XML file comes into dataset wrong / not at all... RRS feed

  • Question

  • Hello,

    I am doing an integration with Peachtree and have been able to export the customers from Peachtree to an xml file, I then read this into a dataset and for now am just spitting the data to a textbox. This is all working great, except the data for column 'ID' is wrong, its coming out as 0, 1, 2, 3, etc. instead of what is in the XML file as VR816-01-0101, VR816-02-0102, VR816-03-0201, VR816-04-0202, etc.  At first glance it looks like its using the number from the middle of the actual data, however the actual data starts at 1 and the data in the list starts with 0.

    Code:

          Dim ds As New DataSet
          ds.ReadXml(My.Application.Info.DirectoryPath & "\Customers.xml")
    
          Dim s As New System.Text.StringBuilder()
          If ds.Tables.Count > 0 Then
            For Each c As DataColumn In ds.Tables(0).Columns
              s.AppendFormat("{0}{1}{1}", c.Caption, ControlChars.Tab)
            Next
            s.AppendLine()
            For Each r As DataRow In ds.Tables(0).Rows
              For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
                If r(i).Equals(DBNull.Value) Then
                  s.AppendFormat("{0}{1}{1}", "<null>", ControlChars.Tab)
                Else
                  s.AppendFormat("{0}{1}{1}", r(i), ControlChars.Tab)
                End If
              Next
              s.AppendLine()
            Next
          End If
          ds.Dispose()
          Me.txtTest.Text = s.ToString


    The Dataset output:

    PAW_Customer_Id		Name		ID_Replacement		type		
    0		Templetate Investments Ltd. (101)		0		paw:customer		
    1		Mr. Breen Egan (102)		0		paw:customer		
    2		Sharon and Merwin Chercover		0		paw:customer		
    3		Earl and Rosalee Hardin		0		paw:customer		
    4		Wendy (BaoHuan) Sung		0		paw:customer		
    
    

    XML Data:

    <PAW_Customers xmlns:paw="urn:schemas-peachtree-com/paw8.02-datatypes"
    xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2000/10/XMLSchema-datatypes">
    <PAW_Customer xsi:type="paw:customer">
    <ID xsi:type="paw:id">VR816-01-0101</ID>
    <Name>Templetate Investments Ltd. (101)</Name>
    <ID_Replacement>0</ID_Replacement>
    </PAW_Customer>
    <PAW_Customer xsi:type="paw:customer">
    <ID xsi:type="paw:id">VR816-02-0102</ID>
    <Name>Mr. Breen Egan (102)</Name>
    <ID_Replacement>0</ID_Replacement>
    </PAW_Customer>
    <PAW_Customer xsi:type="paw:customer">
    <ID xsi:type="paw:id">VR816-03-0201</ID>
    <Name>Sharon and Merwin Chercover</Name>
    <ID_Replacement>0</ID_Replacement>
    </PAW_Customer>
    <PAW_Customer xsi:type="paw:customer">
    <ID xsi:type="paw:id">VR816-04-0202</ID>
    <Name>Earl and Rosalee Hardin</Name>
    <ID_Replacement>0</ID_Replacement>
    </PAW_Customer>
    <PAW_Customer xsi:type="paw:customer">
    <ID xsi:type="paw:id">VR816-05-0301</ID>
    <Name>Wendy (BaoHuan) Sung</Name>
    <ID_Replacement>0</ID_Replacement>
    </PAW_Customer>
    </PAW_Customers>

     

    The problem is the PAW_Customer_Id, it looks like .net is just generating this column... What happened to the "ID" column? If i ask for it directly DataRow("ID") it says "The column ID does not exist"

     

    Any ideas?

    • Moved by Liliane Teng Tuesday, December 14, 2010 9:14 AM (From:Visual Basic General)
    Tuesday, December 7, 2010 9:38 PM

Answers

  • Hello DrDaveMaher,

    Thanks for your post.

    Based on your description, I recommend you could repost on XML and the .NET Framework forum for more better support becuase more this aspect experts live there.

    http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/threads
    (XML and the .NET Framework)

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 10, 2010 7:15 AM
  • This was moved out of the VB forum.

    You can ask this in an XML forum (as suggested).

    Or I can move it into a specific MSDN forum if you'd like.

    This is regarding your code:

      Dim ds As New DataSet
          ds.ReadXml(My.Application.Info.DirectoryPath & "\Customers.xml")
    
          Dim s As New System.Text.StringBuilder()
          If ds.Tables.Count > 0 Then
            For Each c As DataColumn In ds.Tables(0).Columns
              s.AppendFormat("{0}{1}{1}", c.Caption, ControlChars.Tab)
            Next
            s.AppendLine()
            For Each r As DataRow In ds.Tables(0).Rows
              For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
                If r(i).Equals(DBNull.Value) Then
                  s.AppendFormat("{0}{1}{1}", "<null>", ControlChars.Tab)
                Else
                  s.AppendFormat("{0}{1}{1}", r(i), ControlChars.Tab)
                End If
              Next
              s.AppendLine()
            Next
          End If
          ds.Dispose()
          Me.txtTest.Text = s.ToString
    

     


    Ed Price a.k.a User Ed, Microsoft Experience Program Manager (Blog, Twitter, Wiki)
    Thursday, December 29, 2011 11:11 PM

All replies

  • Hello DrDaveMaher,

    Thanks for your post.

    Based on your description, I recommend you could repost on XML and the .NET Framework forum for more better support becuase more this aspect experts live there.

    http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/threads
    (XML and the .NET Framework)

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 10, 2010 7:15 AM
  •  Is PAW_Customer_Id a autonumber in your table
    Friday, December 10, 2010 9:26 AM
  • This was moved out of the VB forum.

    You can ask this in an XML forum (as suggested).

    Or I can move it into a specific MSDN forum if you'd like.

    This is regarding your code:

      Dim ds As New DataSet
          ds.ReadXml(My.Application.Info.DirectoryPath & "\Customers.xml")
    
          Dim s As New System.Text.StringBuilder()
          If ds.Tables.Count > 0 Then
            For Each c As DataColumn In ds.Tables(0).Columns
              s.AppendFormat("{0}{1}{1}", c.Caption, ControlChars.Tab)
            Next
            s.AppendLine()
            For Each r As DataRow In ds.Tables(0).Rows
              For i As Integer = 0 To ds.Tables(0).Columns.Count - 1
                If r(i).Equals(DBNull.Value) Then
                  s.AppendFormat("{0}{1}{1}", "<null>", ControlChars.Tab)
                Else
                  s.AppendFormat("{0}{1}{1}", r(i), ControlChars.Tab)
                End If
              Next
              s.AppendLine()
            Next
          End If
          ds.Dispose()
          Me.txtTest.Text = s.ToString
    

     


    Ed Price a.k.a User Ed, Microsoft Experience Program Manager (Blog, Twitter, Wiki)
    Thursday, December 29, 2011 11:11 PM