[RESOLVED] XML writing problem

[colossus1958]

I have an XML file that I need formatted in a very specific manner, to wit:

<Transactions date="today's date">
<transaction>
<trans-line>
<UnitQty>1</UnitQty>
<Description>Item Name</Description>
<UnitPrice>3.49</UnitPrice>
</trans-line>
<trans-line>
.
.
.
</trans-line>
</transaction>
<transaction>
.
.
.
</transaction>
<Transactions>

This is to be derived from a DataGridView that looks like this:

UnitQty Description UnitPrice
1 Item Name 3.49
X YYYYYYYY Z

and so forth...

I've tried to research how to do this but I'm not getting any results that work.
Can anyone tell me how to write this DGV to an XML file as described and retrieve that XML back to the DGV?
I think I can get the XML to DGV part by using a DataSet (maybe).

[dunfiddlin]

I'm not getting any results that work.

Why would you expect to? You can't derive that format from the DGV because the DGV isn't in that format. The only way you could possibly write in that format would be to manually write the nodes looping through the values of the DGV and the only way you could read it back would be to loop through the values a node at a time. If you want to take advantage of Serialization then you need an object which has an identical structure to the XML you intend to produce.

[colossus1958]

OK I see your point. However, I still need to group the various line items(qty., desc., & price) into a single "piece of data" and then group all of the related "pieces of data" into a single "transaction". I'm not looking for direct serialization(for the reasons you pointed out) but I still have to get from one output(DGV) to the XML as described. If I have to do this "a node at a time", fine. Where I'm lost is: How to do it at all.

[IanRyder]

Hi,

I do not agree with dunfiddlin's post here since you were already on the right track with a DataSet. However, what you will need to do is to create two related tables within the DataSet where the Parent table contains the Date of the transactions and the Child table contains the actual transactions for that date.

The trick here is a two stage process. Stage one is to create an XML file with the correct schema relating to your DataSet and Stage two is to then read that XML file including its Schema into the DataSet each time you want to use it, and then bind these tables to the controls that you want to use.

As an example. Here I create an XML File with Schema from a DataSet:-

Dim DS As New DataSet

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
  DS.DataSetName = "MyTransactionsByDate"
  DS.Tables.Add("TransactionDates")
  DS.Tables.Add("Transactions")
 
  With DS.Tables(0)
    .Columns.Add("TransDateID", GetType(Integer))
    .Columns.Add("TransDate", GetType(Date))
  End With
 
  With DS.Tables(1)
    .Columns.Add("TransDateID", GetType(Integer))
    .Columns.Add("Quantity", GetType(Integer))
    .Columns.Add("Description", GetType(String))
    .Columns.Add("Price", GetType(Decimal))
  End With
 
  DS.Relations.Add("TransID_Transactions", DS.Tables(0).Columns(0), DS.Tables(1).Columns(0))
 
  DS.WriteXml("d:\temp\myXMLData.xml", XmlWriteMode.WriteSchema)
  MsgBox("XML File With Schema Written!")
End Sub

You run this code once to create an initial XML file with the Schema that you need. Now you can read this file whenever you need. Next I read the XML file and display the contents in two DataGridViews:-

Dim DS As New DataSet
Dim bsTransDate As New BindingSource
Dim bsTransactions As New BindingSource

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
  DS.ReadXml("d:\temp\myXMLData.xml", XmlReadMode.ReadSchema)
 
  With DS.Tables(0).Columns(0)
    .AutoIncrement = True
    .AutoIncrementStep = 1
    If DS.Tables(0).Rows.Count > 0 Then
      .AutoIncrementSeed = CInt(DS.Tables(0).Rows(DS.Tables(0).Rows.Count - 1)(0)) + 1
    Else
      .AutoIncrementSeed = 1
    End If
  End With
 
  With bsTransDate
    .DataSource = DS
    .DataMember = "TransactionDates"
  End With
  DataGridView1.DataSource = bsTransDate
  DataGridView1.Columns(0).Visible = False
 
  With bsTransactions
    .DataSource = bsTransDate
    .DataMember = "TransID_Transactions"
  End With
  DataGridView2.DataSource = bsTransactions
  DataGridView2.Columns(0).Visible = False
End Sub

You can now add Transaction Dates to the First DataGridView with the Related Transactions in the second DataGridView. These are related by the hidden Column(0) in each DataGridView which relates to the TransDateID field in the underlying DataSet.

When you have made whatever additions, modifications and deletions that you want you just call additional saves of the DataSet as you need. i.e:-

DS.WriteXml("d:\temp\myXMLData.xml", XmlWriteMode.WriteSchema)

Hope that helps.

Cheers,

Ian

[colossus1958]

I tried your approach and got the following result:

Invalid 'Key' node inside constraint named: TransID_Transactions.

The process stops at: DS.ReadXML(PATH, XMLReadMode.ReadSchema)

All I did was click on Button3 to create the file, then I clicked on Button2 to read the file into the DGVs. Any ideas?
I'll post the code as I have it written:

Imports System.Xml
Public Class Form1
Dim PATH As String = "C:\Users\Owner\My Documents\Visual Studio 2012\Projects\Test-XML-for-Blue\XMLTEST.xml"
Dim DS As New DataSet
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click, Button1.Click
DS.DataSetName = "MyTransactionsByDate"
DS.Tables.Add("TransactionDates")
DS.Tables.Add("Transactions")
With DS.Tables(0)
.Columns.Add("TransDateID", GetType(Integer))
.Columns.Add("TransDate", GetType(Date))
End With
With DS.Tables(1)
.Columns.Add("TransDateID", GetType(Integer))
.Columns.Add("UnitQty", GetType(Integer))
.Columns.Add("Description", GetType(String))
.Columns.Add("UnitPrice", GetType(Decimal))
End With
DS.Relations.Add("TransID_Transactions", DS.Tables(0).Columns(0), DS.Tables(1).Columns(0))
DS.WriteXml(PATH, XmlWriteMode.WriteSchema)
MsgBox("XML File With Schema Written!")
Button3.Visible = False
End Sub

Dim bsTransDate As New BindingSource
Dim bsTransactions As New BindingSource

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
DS.ReadXml(PATH, XmlReadMode.ReadSchema)
With DS.Tables(0).Columns(0)
.AutoIncrement = True
.AutoIncrementStep = 1
If DS.Tables(0).Rows.Count > 0 Then
.AutoIncrementSeed = CInt(DS.Tables(0).Rows(DS.Tables(0).Rows.Count - 1)(0)) + 1
Else
.AutoIncrementSeed = 1
End If
End With
With bsTransDate
.DataSource = DS
.DataMember = "TransactionDates"
End With
DGV1.DataSource = bsTransDate
DGV1.Columns(0).Visible = False
With bsTransactions
.DataSource = bsTransDate
.DataMember = "TransID_Transactions"
End With
DGV2.DataSource = bsTransactions
DGV2.Columns(0).Visible = False
End Sub
Private Sub ButtonSave_Click(sender As Object, e As EventArgs)
DS.WriteXml(PATH, XmlWriteMode.WriteSchema)
End Sub
End Class

[IanRyder]

Hi,

You have got this error due to the fact that you have tried to read the XML file into the same DataSet in which you created the the XML Schema thereby effectively duplicating the schema within the DataSet and causing the error.

So, to use this correctly you would do the following:-

1) Run the project you have written and create the XML File with the correct schema by clicking Button3

2) Close the project and delete the code that created the XML schema. You only need to run this once and then this code becomes redundant (unless you ever wanted to re-create the XML file in the future)

3) Run the project as many times as you like now reading the XML file ONCE ONLY and writing the XML file as many times as you need.

Hope that sorts things for you.

Cheers,

Ian

BTW, when posting code, please use the Advanced button to encase your code in Code tags for readability.

[colossus1958]

Thanks it worked. Kinda helps if you pay attention to what people tell you (sorry). At any rate I have one more question. I need to find a way to have the date in DGV1 posted automatically by the program and I'm not sure how to approach it. Any hints would be appreciated. Thank you for your efforts so far.

PS: Why are the Code Tags in "Advanced"?

[IanRyder]

Hi,

You will need to code one of the DataGridView's events to do this depending on when you want to add the date. As an example you could code the RowValidated event to add the date. In this example I add a date to the 3rd cell in a row that has been added.

Private Sub DataGridView1_RowValidated(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
  If Not DataGridView1.Rows(e.RowIndex).IsNewRow AndAlso IsNothing(DataGridView1.Rows(e.RowIndex).Cells(2).Value) Then
    DataGridView1.Rows(e.RowIndex).Cells(2).Value = Now
  End If
End Sub

PS: Why are the Code Tags in "Advanced"?

You will have to ask the Forum Admins about that one.

Hope that helps.

Cheers,

Ian

[colossus1958]

That ought to get everything accomplished. I don't think I'll ask the Forum Admins about the Code Tags though. Seema trivial now that I think about it. Anyway, thanks a lot you've been very helpful.