DataTable Relation - Want to save several Datatables into one xml

Hello.

I have two doubts this last week about the code that was suggested in this forum and I am using (see code below).

Main Form:

Code:

---

Public Class MainForm

Private Sub OpenForm4_Click(sender As Object, e As EventArgs) Handles OpenForm4.Click
        Form4.ShowDialog()

    End Sub
End Class

---

Form4:

Code:

---

Public Class Form4
    '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 = Module1.Dset

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

    Private bsPeople As New BindingSource
    Private bsInvoice As New BindingSource

    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"
            Module1.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

            'Add data bindings
            bsPeople.DataSource = _Dset
            bsPeople.DataMember = "People"

            Me.PersonIDTextBox.DataBindings.Add("Text", bsPeople, "PersonID")
            Me.FullNameTextBox.DataBindings.Add("Text", bsPeople, "FullName")
            Me.rbneighborhoodA.DataBindings.Add("Checked", bsPeople, "neighborhoodA")
            Me.rbneighborhoodB.DataBindings.Add("Checked", bsPeople, "neighborhoodB")
            Me.rbneighborhoodB.DataBindings.Add("Checked", bsPeople, "neighborhoodC")


            bsInvoice.DataSource = bsPeople
            bsInvoice.DataMember = "InvoicePeopleRelation"

            Me.DataGridView1.DataSource = bsInvoice

        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

    Private Sub AddButton_Click(sender As Object, e As EventArgs) Handles AddButton.Click
        bsPeople.AddNew()
    End Sub

    Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        bsPeople.EndEdit()
        bsInvoice.EndEdit()
        _Dset.WriteXml(DataPath & "InvoiceData.XML")
    End Sub

    Private Sub NextButton_Click(sender As Object, e As EventArgs) Handles NextButton.Click

        bsPeople.MoveNext()
    End Sub
End Class


Module1:
    '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

---

Module

Code:

---

Module Module1
    '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))
.Columns.Add(New DataColumn With {
                            .ColumnName = "neighborhoodA",
                            .DataType = GetType(Boolean)})
                .Columns("neighborhoodA").DefaultValue = False
                .Columns.Add(New DataColumn With {
                            .ColumnName = "neighborhoodB",
                            .DataType = GetType(Boolean)})
                .Columns("neighborhoodB").DefaultValue = False
                .Columns.Add(New DataColumn With {
                            .ColumnName = "neighborhoodC",
                            .DataType = GetType(Boolean)})
                .Columns("neighborhoodC").DefaultValue = False

            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))
                .Columns.Add(New DataColumn With {
                            .ColumnName = "OptionA",
                            .DataType = GetType(Boolean)})
                .Columns("OptionA").DefaultValue = False
                .Columns.Add(New DataColumn With {
                            .ColumnName = "OptionB",
                            .DataType = GetType(Boolean)})
                .Columns("OptionB").DefaultValue = False
                .Columns.Add(New DataColumn With {
                            .ColumnName = "OptionC",
                            .DataType = GetType(Boolean)})
                .Columns("OptionC").DefaultValue = False
            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

---

So I thought I'd better ask right here, since I have not yet found the solution on the internet, and it refers to this specific code.

(DOUBT 1)
When I open the form where everything happens (I have a main form, that calls the form where I work with the DataSet (add, save, etc).. I this form (Form4), in the FormLoad Event I call the Module1 where I have the code that creates the Dataset, DatasetShcema and Datatables.

When I open this form (Form4), and then I close it, and I open it again, I have an error on the line the the Form4:

Code:

---

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

---

and in this line in the Module1:

Code:

---

.Tables.Add(New DataTable With {.TableName = "People"})

---

The error indicates that it can not create the table because it already exists. I understand that the problem is that I call the Module1 in FormLoad event, so it creates the Dataset and the DataTables. But if I close the Form4 without doing anything, and open it again it tries to create the DataSet and the Datatables again, because when I closed the form I did not delete the DataSet or the DataTables.

The solution should delete the DataSet when I close the form, right? If so, how do I do this? I only found ways to delete the tables, but I fell into a problem that could not delete the containers of the DataRelation. I do not know how.

(DOUBT 2)
In the Form4 I added three radiobuttons that iniciatty receive False value, they are Boolean type in the DataTable. The user should check only one option. Supposing I check the option neirboorhoodA, and I click save, the code gives an error on the line of code below, which is the first one after neirboorhoodA with value False. In the xml only the True value is saved, should it not save the False as well?

line from Form4 code:

Code:

---

Me.rbneighborhoodB.DataBindings.Add("Checked", bsPeople, "neighborhoodB")

---

How do I solve this?

Thank you very much in advance

Best Regards.

Verônica

First thing, you chould be opening Form4 as follows

Code:

---

Using _Form4 As New Form4
  _Form4.SHowDialog
End Using

---

Quote:

---

Originally Posted by kpmc

First thing, you chould be opening Form4 as follows

Code:

---

Using _Form4 As New Form4
  _Form4.SHowDialog
End Using

---

---

This alteration does not solve the problem I described.

Yeah the code examples you were given were not designed for you to be creating the form more than once. Also this show some of the problems of using a Globe Dataset. Basically your loading the dataset multiple times with the same data. Here is one way to solve the problem,

Add a Public variable in the Module, I called it IsFirstLoad

Code:

---

    'Declare your Data DataSet (this is your Data)
    Public WithEvents Dset As New DataSet
    Public IsFirstLoad As Boolean = True

---

Then modified the Form4 code

Code:

---

            If My.Computer.FileSystem.FileExists(DataPath & "InvoiceData.XML") AndAlso IsFirstLoad Then
                _Dset.ReadXml(DataPath & "InvoiceData.XML")
                IsFirstLoad = False
            End If

            'Sub routine to create DataSet that contains Schema "_DsetSchema"
            If IsFirstLoad Then Module1.SetDsetSchema()

---

I didn't do a lot of testing but it seem to solve the problem. Like I said before I don't have much experience with using XML files as databases so maybe someone else can give you a better solution.

Can't help with the Radio Button problem.