DataTable Relation - Want to save several Datatables into one xml

Hi,

I am using a DataSet to save my data into a .xml file and then read this data.

My program has several Windows forms, the idea is each one will be a Datatable in my DataSet.

I created Datatable1 to the firsr form. So far it is all good. I can save the data, and ready it back to the form.
My problem is when I also try to save a second Datatable2, that belongs to my DataSet and has a relation with the first one.

The idea is: Datatable1 works in one form, after saving the information, I jump to a second windows form, and then I also save the data the DataTable2 into the same XML file that I have already created. But, instead of adding information in the XML file, it is replacing the data from the DataTable1 for one in the Datatable2.

I know I am doing something wrong, but I could not understand yet how to manage it.

Some help would be great.

-- The code I use to save is: ( I used the property DataBiding to conect the controls with the datatable)

Code:

---

DataTable1BindingSource.EndEdit()
DataSeta.DataTable1.Rows.Add()

        Try
            DataSet.WriteXml(myXMLFilePath)
            MsgBox("Data Saved Successfully!")
 
        Catch ex As Exception
            MsgBox("Something went wrong.. Erro: " & ex.ToString)
        End Try

---

I am using this same code to both forms. I do not know if the problem is in this, or in the relation itself.


I appreciate the help.

I would say you have supplied maybe 15% of the code we could otherwise use to help you with your problem.

You should only have to read the data in when the application starts and use the same dataset in all your forms.

As far as I know, the WriteXML is a pretty simple beast, as the resulting XML file is also pretty simple. From your description, it sounds like you are entangling the tables in the dataset. How that is happening isn't related to the code shown, though. What you'd have to show would be more complicated and hard to show. In fact, I'm not quite sure where you should start, at this point, so answer this question:

How many tables are there in the dataset at the time you call WriteXML? I suspect that the answer is 1, but I don't know if there are other tables that are not related to the problem, but which are also in the dataset.

Quote:

---

Originally Posted by Shaggy Hiker

As far as I know, the WriteXML is a pretty simple beast, as the resulting XML file is also pretty simple. From your description, it sounds like you are entangling the tables in the dataset. How that is happening isn't related to the code shown, though. What you'd have to show would be more complicated and hard to show. In fact, I'm not quite sure where you should start, at this point, so answer this question:

How many tables are there in the dataset at the time you call WriteXML? I suspect that the answer is 1, but I don't know if there are other tables that are not related to the problem, but which are also in the dataset.

---

The part I have working have only 1 DataTable. The idea is save the datatable of each form when the user finish entering the information, instead of wait all Datatables be filled to save in the end.

Create the DataSet, including all the DataTables and DataRelations, at the outset. You can then just call WriteXml on that DataSet to save everything at any time. You simply populate the existing DataTables as you go. If you need to learn how to create related tables in a DataSet, follow the CodeBank link in my signature below and check out my thread on Master/Detail Data-Binding.

If there is only one datatable, then it certainly makes sense that only one datatable will be saved. If each form is to have a different datatable, then there would be one datatable per form in the dataset. They will all be saving.

Yeah, it sounds like the OP is expecting "DataSet.WriteXml(myXMLFilePath)" command to Append the table the second time it's called. But that's just a guess, like kpmc said, you really do need to provide more information.

I will share with you one of my solutions I recently created, and I just took some time to add some comments. This approach will allow you to 'append' tables and/or columns on the fly and will not overwrite your source XML or data therein. Please read carefully the comments and learn from this solution, do not just copy/paste your way to 6 pack abs

FormMain: (start form)

Code:

---

Public Class FormMain
    'Path where your XML Data will live
    Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\InvoiceData\"

    'Create a var to the DataSet
    Dim _Dset As DataSet = ModuleData.Dset

    'Create a var to the DataSet that contains schema only
    Dim _DsetSchema As DataSet = ModuleData.DsetSchema

    Private Sub FormMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Width = Screen.PrimaryScreen.Bounds.Width - 75
        Height = Screen.PrimaryScreen.Bounds.Height - 75
        CenterToParent()

        Try
            'If path to souce doesnt exist, create it
            If Not My.Computer.FileSystem.DirectoryExists(DataPath) Then My.Computer.FileSystem.CreateDirectory(DataPath)

            'If source path and XML exist, read it in "_Dset" (this is your data)
            If My.Computer.FileSystem.FileExists(DataPath & "InvoiceData.XML") Then _Dset.ReadXml(DataPath & "InvoiceData.XML")

            'Sub routine to create DataSet that contains Schema "_DsetSchema"
            ModuleData.SetDsetSchema()

            'Sub routine that compares
            LoadSchema(_DsetSchema, _Dset)

            'Add relations if they do not exist
            If Not _Dset.Relations.Contains("InvoicePeopleRelation") Then
                _Dset.Relations.Add(New DataRelation("InvoicePeopleRelation",
                                                _Dset.Tables("People").Columns("PersonID"),
                                                _Dset.Tables("Invoice").Columns("PersonID"), True))
            End If

            If Not _Dset.Relations.Contains("InvoiceItemsRelation") Then
                _Dset.Relations.Add(New DataRelation("InvoiceItemsRelation",
                                    _Dset.Tables("Invoice").Columns("InvoiceID"),
                                    _Dset.Tables("Items").Columns("InvoiceID"), True))
            End If


        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub LoadSchema(SourceDS As DataSet, TargetDS As DataSet)
        'Boolean to flag if there are Schema Changes
        Dim SchemaChanged As Boolean = False


        Try
            'Iterate tables in _DsetSchema
            For Each Tbl As DataTable In SourceDS.Tables

                'Check if table exists in _Dset (your data), if not add it
                If Not TargetDS.Tables.Contains(Tbl.TableName) Then TargetDS.Tables.Add(New DataTable With {.TableName = Tbl.TableName})

                'Iterate columns within table iteration
                For Each Dcol As DataColumn In Tbl.Columns

                    'If column doesnt exist then add it
                    If Not TargetDS.Tables(Tbl.TableName).Columns.Contains(Dcol.ColumnName) Then
                        TargetDS.Tables(Tbl.TableName).Columns.Add(New DataColumn With {
                                                                  .ColumnName = Dcol.ColumnName,
                                                                  .DataType = Dcol.DataType,
                                                                  .AutoIncrement = Dcol.AutoIncrement})

                        'If columns were added then schema has changed, set flag to true
                        SchemaChanged = True
                    End If
                Next
            Next

            'If schema flad is true,'rewrite' the XML (your data)
            If SchemaChanged Then
                _Dset.WriteXml(DataPath & "InvoiceData.XML", XmlWriteMode.WriteSchema)
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Class

---

ModuleData: (where your dataset/schema lives)

Code:

---

Module ModuleData
    'Declare your Data DataSet (this is your Data)
    Public WithEvents Dset As New DataSet

    'Declare your Schema DataSet (Schema only)
    Public WithEvents DsetSchema As New DataSet

    'Sub Routine to create your Schema
    Public Sub SetDsetSchema()
        With DsetSchema
            .Tables.Add(New DataTable With {.TableName = "People"})
            With .Tables("People")
                .Columns.Add(New DataColumn With {
                            .ColumnName = "PersonID",
                            .AutoIncrement = True,
                            .DataType = GetType(Integer)})
                .Columns.Add("DateCreate", GetType(Date))
                .Columns("DateCreate").DefaultValue = Now.Date
                .Columns.Add("FullName", GetType(String))
                .Columns.Add("Company", GetType(String))
                .Columns.Add("Email", GetType(String))
                .Columns.Add("Phone", GetType(String))
                .Columns.Add("Address", GetType(String))
                .Columns.Add("City", GetType(String))
                .Columns.Add("State", GetType(String))
                .Columns.Add("ZIP", GetType(String))
            End With

            .Tables.Add(New DataTable With {.TableName = "Invoice"})
            With .Tables("Invoice")
                .Columns.Add(New DataColumn With {
                            .ColumnName = "InvoiceID",
                            .AutoIncrement = True,
                            .DataType = GetType(Integer)})
                .Columns.Add("PersonID", GetType(Integer))
                .Columns.Add("FullName", GetType(String))
                .Columns.Add("Company", GetType(String))
            End With

            .Tables.Add(New DataTable With {.TableName = "Items"})
            With .Tables("Items")
                .Columns.Add(New DataColumn With {
                            .ColumnName = "ItemID",
                            .AutoIncrement = True,
                            .DataType = GetType(Integer)})
                .Columns.Add("InvoiceID", GetType(Integer))
                .Columns.Add("PersonID", GetType(Integer))
                .Columns.Add("ItemDesc", GetType(String))
            End With
        End With
    End Sub

End Module

---

I suppose I could submit this to the vault as well...

I have created one DataSet (did through Add/New Item/DataSet)
In the DataSet.xsd Window I created my tables and the relation between them)

I am calling WriteXML on that DataSet.

I read your thread on Master/Detail Data-Binding, jmcilhinney, but I am still confused about what I am doing wrong.



I describe exactly what I did in my exemple program attached ()

1. I created my forms, with the objects I need (TextBox, Radio Buttons, ComboBox, etc..)
2. I created my DataSet (going to Add/New Item/DataSet
3. Opened the DataSet Window and Created my Tables (dtproject is the parent one)
4. Created the Relation between tables
5. Back to the form, I clicked in the objects and changed the DataBiding Property (Text to the textbox/and selected value to the combo box) to the correspondent field in the correspondent table. Form1 is realted to Table dtProject. Form2 to dtOption A and Form 3 to DT Option B.
6. Created the Button New, Save and Next in the Form1
7. In the Form2: I have 3 radiobuttons, want to save which one the user chooses.
8. In the Form3: I have a Drag and Drop Function, the user can drag pictureboxes into a Panel. Want to save the pictures were dragged into the panel, and in which order. the function gives me an array, that I want to save, and access later because it will tell the order to open other forms (did not put theses other forms in the exemple to simplify).
In this form, I did not try to save, or created the DataTable properly, because I do not know how. What brings me to the questions below.
8.a ) By the way, how I save an array in my DataTable? Which Fields I need to have in the Datatable.?


I use the same code to New and Save Buttons in Form 1, 2.


Thanks

Quote:

---

Originally Posted by kpmc

I will share with you one of my solutions I recently created, and I just took some time to add some comments. This approach will allow you to 'append' tables and/or columns on the fly and will not overwrite your source XML or data therein. Please read carefully the comments and learn from this solution, do not just copy/paste your way to 6 pack abs

(...)

---

I will look your code to see if it works somehow for me.. thank you.

Well you tried to provide a good description, but you still haven't shown us the necessary code. The description is a good start but we need to see how you've tried to implement it. We can't tell what mistakes you've made in your code without seeing the code. btw - if you've setup a relation between the tables in your dataset designer then you don't need to do it again manually. Did you drag and drop the tables onto the forms from the Datasources Window?

Quote:

---

Originally Posted by wes4dbt

Well you tried to provide a good description, but you still haven't shown us the necessary code. The description is a good start but we need to see how you've tried to implement it. We can't tell what mistakes you've made in your code without seeing the code. btw - if you've setup a relation between the tables in your dataset designer then you don't need to do it again manually. Did you drag and drop the tables onto the forms from the Datasources Window?

---

I sent the application in a DropBox Link!! This one >>

I could not attach the file to the forum post.

I thought this was better than just paste the code since I did some configuration in the Visual Studio DataSet and DataBiding Property of the controls.

That second link gave a 404, but the first one had some things in it that have to be removed. We don't allow linking to compiled code. If you could please remove the bin and Obj folders, then re-post the link, that would be appreciated. The issue with compiled code is that it can hide anything. We've been burned by that before, hence the rule. I removed the second link, since there was something wrong with it, so you'd just get puzzled responses. Likely it was meant to be the same as the other link, but it wasn't.

You don't actually need the bin and obj folders. If you delete them, then they get created automatically by VS the next time you build the project. Therefore, deleting them is painless for all involved.

Quote:

---

Originally Posted by Shaggy Hiker

That second link gave a 404, but the first one had some things in it that have to be removed. We don't allow linking to compiled code. If you could please remove the bin and Obj folders, then re-post the link, that would be appreciated. The issue with compiled code is that it can hide anything. We've been burned by that before, hence the rule. I removed the second link, since there was something wrong with it, so you'd just get puzzled responses. Likely it was meant to be the same as the other link, but it wasn't.

You don't actually need the bin and obj folders. If you delete them, then they get created automatically by VS the next time you build the project. Therefore, deleting them is painless for all involved.

---

Undestood. I am sorry. Now I was able to attach the file. Attachment 164977

Can't open the example.zip or extract the files. Just post the relevant code.

Quote:

---

Originally Posted by wes4dbt

Can't open the example.zip or extract the files. Just post the relevant code.

---

The code is basically the one in my first message.

Code:

---

ublic Class Form1

    Private myXMLFilePath As String = "C:\Users\veron\Desktop\myXMLFile.xml"

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Me.Validate()

        Try
            DataSet1.WriteXml(myXMLFilePath)
            MsgBox("Saved Successfully!")

        Catch ex As Exception
            MsgBox("Something went wrong. Data not saved. Erro: " & ex.ToString)
        End Try

    End Sub

    Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
        Try

            DataSet1.dtProject.AdddtProjectRow(DataSet1.dtProject.NewdtProjectRow())
            DtProjectBindingSource.MoveLast()

        Catch message As Exception
            MessageBox.Show(message.Message)
            DataSet1.dtProject.RejectChanges()
        End Try
    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If cmbType.Text = "Option A" Then
            Form2.ShowDialog()
            Me.Close()
        ElseIf cmbType.Text = "Option B" Then
            Form3.ShowDialog()
            Me.Close()

        End If

    End Sub
End Class

---

I am trying to send the application because it is important to get the whole picture in this case. I did not created the DataTables through code, I used the DataSet Designer.

I think I corrupted the file while trying to reduce the size to fit in the attachment. I am sorry. One last attempt.

I deleted the folders bin and obj, upload in my oneDrive account. The file has about 1Mb. I tested download and open this one twice.

https://cocufrjbr-my.sharepoint.com/...wOr-g?e=UB6avM

Couldn't download it, antivirus found "URL:MAL". Looks like all it contained was your solution file, none of the form files.

Don't see anything major wrong with what you posted but you said the problem was when you saved the data from form2 it over wrote the data from form1. so we need to see form2 code.

His problem is mostlikely due to the fact he is reading the XML into form2 as well whilst it is still open in Form1. You need to either put 1 dataset in a module like i showed or pass the dataset to from2 as an argument from Form1 to Form2

Quote:

---

Originally Posted by wes4dbt

Couldn't download it, antivirus found "URL:MAL". Looks like all it contained was your solution file, none of the form files.

Don't see anything major wrong with what you posted but you said the problem was when you saved the data from form2 it over wrote the data from form1. so we need to see form2 code.

---

It is exactly the same.

Quote:

---

His problem is mostlikely due to the fact he is reading the XML into form2 as well whilst it is still open in Form1. You need to either put 1 dataset in a module like i showed or pass the dataset to from2 as an argument from Form1 to Form2

---

In your code your declare a DataSet and the DataTables. Do I need to do it? I thought if I create a DataSet as I did it would work for all forms, but it seems it does not. So intead of using the DataSet Designer, should I define it in the code?.
I am not getting how the DataSet works at all for the whole application.

The second you drag a dataset from the designer and plop it I can no longer help. I didnt realize that is what you were doing.

Quote:

---

Originally Posted by kpmc

The second you drag a dataset from the designer and plop it I can no longer help. I didnt realize that is what you were doing.

---

The thread goal is to help to understand how to save data from different forms into the same DataSet and Save it all in a XML file. I really need to understand how this works.

I described the Form with the DragandDrop to try to get help too in how to save an array into a DataTable. I did not mean to change the subject of this thread, I could ask it in another thread if you think is better.

The example I put is pretty straightforward. You declare your dataset in a module and you can access that dataset from any form. The schema dataset just allows you to drop in tables and/or columns, so the next time you run the app and the schema dataset has more tables/columns than the Dataset it will just add them. There are several ways to accomplish such a thing, this is just one way. If you do not intend on adding more columns or tables then the procedure can be drastically simplified. To use the dataset in all your form you can create the dataset in a module as I have shown, or when you're calling the show/showdialog method to forms you can pass a refernce to the dataset to the form class being created.

Quote:

---

Originally Posted by veronica.sa

It is exactly the same.

---

If it's exactly the sane then that's your problem. Your using the same datatable as in form1, of course when you write the dataset to the XML then you'll only have data from one datatable, because that's all you have. When you write the dataset to the XML file it overwrites the previous file, it doesn't append.

Quote:

---

Originally Posted by wes4dbt

If it's exactly the sane then that's your problem. Your using the same datatable as in form1, of course when you write the dataset to the XML then you'll only have data from one datatable, because that's all you have. When you write the dataset to the XML file it overwrites the previous file, it doesn't append.

---

I meant the same logic, but with the name of the other DataTable. Look

Code:

---

  Private myXMLFilePath As String = "C:\Users\veron\Desktop\myXMLFile.xml"

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Me.Validate()

        Try
            DataSet1.WriteXml(myXMLFilePath)
            MsgBox("Saved Successfully!")

        Catch ex As Exception
            MsgBox("Something went wrong. Data not saved. Erro: " & ex.ToString)
        End Try

    End Sub

    Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
        Try

            DataSet1.dtOptionA.AdddtOptionARow(DataSet1.dtOptionA.NewdtOptionARow())
            DtOptionABindingSource.MoveLast()

        Catch message As Exception
            MessageBox.Show(message.Message)
            DataSet1.dtOptionA.RejectChanges()
        End Try
    End Sub

---

Well this is why I keep saying to post the code and you still haven't posted the necessary code and this is post #26. I need to see what dataset datatables are filled, probably in the form load event. But I'll take another guess, sense this was created by dragging the datatable to the form then the only datatable that is getting filled is dtOptionA, which means that dtProject is empty. so when you write the dataset to the XML file the only thing in the XML file is the dtoptionA data, because that's the only information in the dataset. The dataset on Form1 and the Dataset on form2 are 2 different instances.

Quote:

---

Originally Posted by wes4dbt

Well this is why I keep saying to post the code and you still haven't posted the necessary code and this is post #26. I need to see what dataset datatables are filled, probably in the form load event. But I'll take another guess, sense this was created by dragging the datatable to the form then the only datatable that is getting filled is dtOptionA, which means that dtProject is empty. so when you write the dataset to the XML file the only thing in the XML file is the dtoptionA data, because that's the only information in the dataset. The dataset on Form1 and the Dataset on form2 are 2 different instances.

---

What I sent of code is actually what I have of code.
I created the DataSet clicking with right button in the project in the Solution Explorer window, then Add/DataSet. Then I created the DataTables in this DataSet Designer Window.

When I change the DataBiding property o my fields in the form it creates a DataTableBiding and a DataSet1 objects in the form. See pictures. Attachment 164997Attachment 164999

I wont have time till tomorrow to look at this. But your not loading the data from the XML file into your dataset.

Anyway try this,

In the form load event put,

Code:

---

    Private Sub Form1_Load(ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles MyBase.Load

        'If the file exists, then read the data from the Xml file
        '  into the DataSet
        If My.Computer.FileSystem.FileExists(myXmlFilePath) = True Then
            DataSet1.ReadXml(myXmlFilePath)
        End If

    End Sub

---

Do this for each form (you have to fill the dataset otherwise you will lose all the data that's in the Xml file the next time you write to it.)

Maybe Shaggy or kpmc can chime in, they have more experience working with Xml file than I do.

Every aspect of a XML solution is pretty well covered in the example I provided, this includes reading it in, rewriting if schema changes while preserving data, and even DataRelation example. The fact you have neglected to put forth a single bit of effort to make it work without Mr. Wizard tells me I am wasting my time with having you understand.

Cheers

Quote:

---

Originally Posted by kpmc

Every aspect of a XML solution is pretty well covered in the example I provided, this includes reading it in, rewriting if schema changes while preserving data, and even DataRelation example. The fact you have neglected to put forth a single bit of effort to make it work without Mr. Wizard tells me I am wasting my time with having you understand.

Cheers

---

I did not neglect your example. I look at it. I see that works, but I am trying to understand how DataSet, the relation between DataTables, Forms and the whole thing works.
I want to learn to solve the problem, not just copy your solution without understanding, which was your advice by the way. I could copy it, change the tables to my purpose and be happy with, it would be the easy way. But I would continue not knowing how the whole thing works.

Why can't I make work using the DataSet Designer? Is doing everything through code is better? In my humble opinion, for a small project it would be great, if I have a few tables only. But my project will have in the end a dozen tables, with lots os columns. Correct me if I am wrong, but I think it is easier to not get lost using the Designer view, than have the Tables all define just in code. I am just starting using DataSet, I do not know yet the answer of each way is better. That is way I asked for help. And I appreciate all the effort of each one that respectfully posted their contributions here. But I am still struggling to understand.

Sincerely regards

What youre asking is subject to opinion. "Is it better to use Mr. Wizard?" Is the question I read. My opinion is that A pilot can use auto-pilot, but if he doesnt know how to fly he will never land the thing. In technical terms, for me it is much more clear to see the table schema right in front of me to where I can add columns and tables and not have to click the deisgner objects and root through collections, that is a pain. Maybe it is slightly easier through the table designer, but thats just not my style. It adds layers to the solutions you dont need and in my opinion creates a mess. Someone else will have a different opinion, which is best? There is no best...

Quote:

---

Originally Posted by kpmc

I will share with you one of my solutions I recently created, and I just took some time to add some comments. This approach will allow you to 'append' tables and/or columns on the fly and will not overwrite your source XML or data therein. Please read carefully the comments and learn from this solution, do not just copy/paste your way to 6 pack abs

FormMain: (start form)

Code:

---

Public Class FormMain
    'Path where your XML Data will live
    Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\InvoiceData\"

    'Create a var to the DataSet
    Dim _Dset As DataSet = ModuleData.Dset

    'Create a var to the DataSet that contains schema only
    Dim _DsetSchema As DataSet = ModuleData.DsetSchema

    Private Sub FormMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Width = Screen.PrimaryScreen.Bounds.Width - 75
        Height = Screen.PrimaryScreen.Bounds.Height - 75
        CenterToParent()

        Try
            'If path to souce doesnt exist, create it
            If Not My.Computer.FileSystem.DirectoryExists(DataPath) Then My.Computer.FileSystem.CreateDirectory(DataPath)

            'If source path and XML exist, read it in "_Dset" (this is your data)
            If My.Computer.FileSystem.FileExists(DataPath & "InvoiceData.XML") Then _Dset.ReadXml(DataPath & "InvoiceData.XML")

            'Sub routine to create DataSet that contains Schema "_DsetSchema"
            ModuleData.SetDsetSchema()

            'Sub routine that compares
            LoadSchema(_DsetSchema, _Dset)

            'Add relations if they do not exist
            If Not _Dset.Relations.Contains("InvoicePeopleRelation") Then
                _Dset.Relations.Add(New DataRelation("InvoicePeopleRelation",
                                                _Dset.Tables("People").Columns("PersonID"),
                                                _Dset.Tables("Invoice").Columns("PersonID"), True))
            End If

            If Not _Dset.Relations.Contains("InvoiceItemsRelation") Then
                _Dset.Relations.Add(New DataRelation("InvoiceItemsRelation",
                                    _Dset.Tables("Invoice").Columns("InvoiceID"),
                                    _Dset.Tables("Items").Columns("InvoiceID"), True))
            End If


        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub LoadSchema(SourceDS As DataSet, TargetDS As DataSet)
        'Boolean to flag if there are Schema Changes
        Dim SchemaChanged As Boolean = False


        Try
            'Iterate tables in _DsetSchema
            For Each Tbl As DataTable In SourceDS.Tables

                'Check if table exists in _Dset (your data), if not add it
                If Not TargetDS.Tables.Contains(Tbl.TableName) Then TargetDS.Tables.Add(New DataTable With {.TableName = Tbl.TableName})

                'Iterate columns within table iteration
                For Each Dcol As DataColumn In Tbl.Columns

                    'If column doesnt exist then add it
                    If Not TargetDS.Tables(Tbl.TableName).Columns.Contains(Dcol.ColumnName) Then
                        TargetDS.Tables(Tbl.TableName).Columns.Add(New DataColumn With {
                                                                  .ColumnName = Dcol.ColumnName,
                                                                  .DataType = Dcol.DataType,
                                                                  .AutoIncrement = Dcol.AutoIncrement})

                        'If columns were added then schema has changed, set flag to true
                        SchemaChanged = True
                    End If
                Next
            Next

            'If schema flad is true,'rewrite' the XML (your data)
            If SchemaChanged Then
                _Dset.WriteXml(DataPath & "InvoiceData.XML", XmlWriteMode.WriteSchema)
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Class

---

ModuleData: (where your dataset/schema lives)

Code:

---

Module ModuleData
    'Declare your Data DataSet (this is your Data)
    Public WithEvents Dset As New DataSet

    'Declare your Schema DataSet (Schema only)
    Public WithEvents DsetSchema As New DataSet

    'Sub Routine to create your Schema
    Public Sub SetDsetSchema()
        With DsetSchema
            .Tables.Add(New DataTable With {.TableName = "People"})
            With .Tables("People")
                .Columns.Add(New DataColumn With {
                            .ColumnName = "PersonID",
                            .AutoIncrement = True,
                            .DataType = GetType(Integer)})
                .Columns.Add("DateCreate", GetType(Date))
                .Columns("DateCreate").DefaultValue = Now.Date
                .Columns.Add("FullName", GetType(String))
                .Columns.Add("Company", GetType(String))
                .Columns.Add("Email", GetType(String))
                .Columns.Add("Phone", GetType(String))
                .Columns.Add("Address", GetType(String))
                .Columns.Add("City", GetType(String))
                .Columns.Add("State", GetType(String))
                .Columns.Add("ZIP", GetType(String))
            End With

            .Tables.Add(New DataTable With {.TableName = "Invoice"})
            With .Tables("Invoice")
                .Columns.Add(New DataColumn With {
                            .ColumnName = "InvoiceID",
                            .AutoIncrement = True,
                            .DataType = GetType(Integer)})
                .Columns.Add("PersonID", GetType(Integer))
                .Columns.Add("FullName", GetType(String))
                .Columns.Add("Company", GetType(String))
            End With

            .Tables.Add(New DataTable With {.TableName = "Items"})
            With .Tables("Items")
                .Columns.Add(New DataColumn With {
                            .ColumnName = "ItemID",
                            .AutoIncrement = True,
                            .DataType = GetType(Integer)})
                .Columns.Add("InvoiceID", GetType(Integer))
                .Columns.Add("PersonID", GetType(Integer))
                .Columns.Add("ItemDesc", GetType(String))
            End With
        End With
    End Sub

End Module

---

I suppose I could submit this to the vault as well...

---

In your example, how do you relate the DataTables with the Form Controls (TextBoxes and etc)? How do I say to the program that I want the Text in the TextBox txtFullName, i.g., to be saved in the Column FullName? I could not see that link in the code. I appreciate the attention.

You would create a bindingsource to the tables.

Code:

---

Private WithEvents MyTableBS As New BindingSource

---

Then set its datasource.

Code:

---

MyTableBS.DataSource = Dset.Tables("MyTable")

---

Then DataBind the control property to the BindingSource/DataProperty(column name)

Code:

---

TextBox1.DataBindings.Add("Text", MyTableBS, "MyColumn")

---

I got it. I will try it here. Thanks

Quote:

---

Originally Posted by kpmc

You would create a bindingsource to the tables.

Code:

---

Private WithEvents MyTableBS As New BindingSource

---

Then set its datasource.

Code:

---

MyTableBS.DataSource = Dset.Tables("MyTable")

---

Then DataBind the control property to the BindingSource/DataProperty(column name)

Code:

---

TextBox1.DataBindings.Add("Text", MyTableBS, "MyColumn")

---

---

I declared this line in the beginning of my class. It is this right?

Code:

---

Public Class Form1
Private WithEvents MyTableBS As New BindingSource
.
.
.
End Class

---

Is this line in the FormLoad event?

Code:

---

MyTableBS.DataSource = Dset.Tables("MyTable")

---

I tried to add a row in the Table but the way I was going it giving me an error. How do I add a row in the table? I guess it is more simple than this:

Code:

---

Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
        Try

            _DSet.MyTable.AddMyTableRow(_DSet.MyTable.NewMyTableRow())
            DtOptionABindingSource.MoveLast()

        Catch message As Exception
            MessageBox.Show(message.Message)
            _DSet.MyTable.RejectChanges()
        End Try
    End Sub

---

Yes, Yes, and you dont add a row to the datatable. You 'add' it to the bindingsource which handles all the methods on the datatable for you. Call AddNew on the bindingsource. Any column in the table that is not bound to a textbox or other control can be set by casting the Bindingsource ListItem as datarowview and setting it without control as seen in this example. otherwise the DataBinding on the control will handle that for you.
When you are saving your data (WriteXML) you want to call .EndEdit on the bindingsource first

Code:

---

        With MyTableBS
            .AddNew()
            'to alter columns that arent bound to textbox   
            DirectCast(.Current, DataRowView)("ColumnName") = "Whatever"
        End With

---

My Module. Created one table so far.

Code:

---

Module ModuleData

    'Declare your Data DataSet (this is your Data)
    Public WithEvents Dset As New DataSet

    'Declare your Schema DataSet (Schema only)
    Public WithEvents DsetSchema As New DataSet

    'Sub Routine to create your Schema
    Public Sub SetDsetSchema()
        With DsetSchema
            .Tables.Add(New DataTable With {.TableName = "dtProjext"})
            With .Tables("dtProject")
                .Columns.Add(New DataColumn With {
                            .ColumnName = "IDProject",
                            .AutoIncrement = True,
                            .DataType = GetType(Integer)})
                .Columns.Add("Name", GetType(String))
                .Columns.Add("CreationDate", GetType(Date))
                .Columns("CreationDate").DefaultValue = Now.Date
                .Columns.Add("DiretoryProjeto", GetType(String))
                .Columns.Add("TypoProject", GetType(String))
                .Columns.Add("Description", GetType(String))

            End With

---

my main form code: In this form I have button to save new project, and one to open a project. It is not working yet, it is not creating the file.
Could you take a look please?. See if I put any code wrong.

' I am using a global variable to keep the file path called myXMLFilePath, wich has the complete path to the file. Basically the user type the name of the file and chose the folder, and it generates the path. this part I did not pasted in the code below

Code:

---

Public Class MainForm

    Private WithEvents dtProjectBindingSource As New BindingSource
   
   
    'Path where your XML Data will live
    ' Dim myXMLFilePath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\InvoiceData\"

    'Create a var to the DataSet
    Dim _Dset As DataSet = ModuleData.Dset

    'Create a var to the DataSet that contains schema only
    Dim _DsetSchema As DataSet = ModuleData.DsetSchema

 
    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles Me.Load

        dtProjetoBindingSource.DataSource = _Dset.Tables("dtProjeto")

        Try

            'Sub routine to create DataSet that contains Schema "_DsetSchema"
            ModuleData.SetDsetSchema()

            'Sub routine that compares
            LoadSchema(_DsetSchema, _Dset)

            'Add relations if they do not exist
            If Not _Dset.Relations.Contains("ProjetoUnidadeRelation") Then
                _DsetSIDE.Relations.Add(New DataRelation("ProjetoUnidadeRelation",
                                                _Dset.Tables("dtProjeto").Columns("IDProjeto"),
                                                _Dset.Tables("dtUnidade").Columns("IDUnidade"), True))
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
end sub

 
 Private Sub tsSave_Click(sender As Object, e As EventArgs) Handles tsSave.Click
        Me.Validate()

        'check if there is a file with the same name in the chosen directory
        If File.Exists(myXMLFilePath) Then
            If MsgBox("Um arquivo com este nome já existe nesse diretório. Deseja substituir?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, " ") = MsgBoxResult.Yes Then
                File.Delete(myXMLFilePath)
     
        End If

        dtProjetoBindingSource.EndEdit()


        Try
            txtName.DataBindings.Add(txtName.Text, dtProjetoBindingSource, "NameProject")
            txtCreation.DataBindings.Add(txtCreation.Text, dtProjetoBindingSource, "CreationDate")
            txtDirectory.DataBindings.Add(txtDirectory.Text, dtProjetoBindingSource, "Directory")
            cmbTypeProject.DataBindings.Add(cmbTypeProject.Text, dtProjetoBindingSource, "TypeProject")
            txtDescription.DataBindings.Add(txtDescription.Text, dtProjetoBindingSource, "Description")

            _Dset.WriteXml(myXMLFilePath)
            MsgBox("Data saved successfully")

         
        Catch ex As Exception
            MsgBox("Some error occurred, your data was not saved. Erro: " & ex.ToString)
        End Try

    End Sub

Private Sub tsOpen_Click(sender As Object, e As EventArgs) Handles tsOpen.Click
        Dim ofd As New OpenFileDialog With {
      .Title = "Choose a file to open",
      .Filter = "XML (*.xml)|*.xml",
      .FilterIndex = 0,
      .InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)}

        If ofd.ShowDialog = DialogResult.OK Then

            Dim filename As String = ofd.FileName 'extract the address of the file
            myXMLFilePath = filename


            If (File.Exists(filename)) Then
                    _Dset.ReadXml(myXMLFilePath)
            End If


        End If
end sub

---

Firstly, comment out all your try blocks. Dont use them unless youre expecting errors in your production build and need to handle errors, there really is no good reason to use them during debug/inception, in my opinion.

I dont have a lot of time currently. What path are you expecting to see the resulting XML. As it stands in the code youre showing the XML should reside in your documents folder in a subfolder named InvoiceData

Quote:

---

I did not neglect your example. I look at it. I see that works, but I am trying to understand how DataSet, the relation between DataTables, Forms and the whole thing works.
I want to learn to solve the problem, not just copy your solution without understanding, which was your advice by the way. I could copy it, change the tables to my purpose and be happy with, it would be the easy way. But I would continue not knowing how the whole thing works.

---

I see you've decided to change your approach and that's fine with me. You'll learn more about the objects involved. But if you want to learn how to work or setup a Relational Database then Xml is the wrong choice. Xml isn't even a database, it's just a flat file or text file. With some clever manipulation you can create a relational system but why not just use a Relational database Management System to begin with? Like Sql Server, MySql, Sqlite or Access.

As for using a datasource and drag and drop, I use them all the time, it saves a tremendous amount of time. But it will be helpful to learn to create dataset, datatable, commands, relations, datareaders and dataadapters manually.

If you'd like more information on creating a One to Many system I would suggest checking out this video by Beth Massi https://www.youtube.com/watch?v=lpnDUC6ilHY

It's old (think Vs 2008 or VS 2010) but nothing much has changed, except the VS IDE layout. If you like that video, she has many more, just google Beth Massi Forms Over Data videos.

Quote:

---

With some clever manipulation you can create a relational system

---

Wes, don't confuse the poor lad even more. There is absolutely no manipulation adding a data relation to XML file, there is only adding the DataRelation. It is exactly the same for any connection based DBM. The biggest difference is that with XML it will write the DataRelation to the XML, thus you need not try to add it again. This is why in my example it first checks for relations prior to adding them.

I think you should first ask him/her what the application of the dataset is prior to suggesting a DBM. There are plenty of applications why a simple XML based dataset is spot-on