dcsimg
Page 2 of 2 FirstFirst 12
Results 41 to 64 of 64

Thread: DataTable Relation - Want to save several Datatables into one xml

  1. #41
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,432

    Re: DataTable Relation - Want to save several Datatables into one xml

    Quote Originally Posted by kpmc View Post
    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
    Not trying to confuse the OP. Just pointing out that XML is not a database. If the OP want to learn how to develop Relational Database Management Systems then XML is the wrong tool. If the OP wants to use an Xml file, that's fine with me.

  2. #42
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,013

    Re: DataTable Relation - Want to save several Datatables into one xml

    Code:
     '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))
    Looking at your code briefly, this doesnt make sense. You are checking _Dset for a relation then adding it to _DsetSIDE I can't think of a good reason to have 2 DataSet's in a solution.

  3. #43
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,013

    Re: DataTable Relation - Want to save several Datatables into one xml

    Not trying to confuse the OP. Just pointing out that XML is not a database. If the OP want to learn how to develop Relational Database Management Systems then XML is the wrong tool. If the OP wants to use an Xml file, that's fine with me.
    I dont want to feel that I steered this into a direction away from OP, which was relational datatables inclusive to XML.

  4. #44

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Red face Re: DataTable Relation - Want to save several Datatables into one xml

    For my application saving the information in a XML is enough. Each project will have just a few data to save, no need for a DataBase.

    Thanks for the video suggestion We4dbt

    kpcm, you said I could use
    Code:
    TextBox1.DataBindings.Add("Text", MyTableBS, "MyColumn")
    to DataBind the control property to the BindingSource/DataProperty(column name). I tried and is giving an error in this line.


    First I did like this,
    Code:
    txtName.DataBindings.Add(txtName.Text, dtProjetoBindingSource, "NameProject")
    but then I wnet back here adn lookedup more about it and changed for:
    Code:
    txtName.DataBindings.Add("Text", dtProjetoBindingSource, "NameProject")
    It is not the location, I have checked and it is supposed to save in the folder I am choosing in the running time. But the file is not generated. I desibled these lines to check, they are the problem.

  5. #45
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,432

    Re: DataTable Relation - Want to save several Datatables into one xml

    I'm going to let kpmc work you through this project. I just want to say you need to provide more information so he can provide the help you need.

    If there is an error, ALWAYS tell us what the error is and what line of code causes the error.

    Don't just post one single line of code, it provides no context of what happened before.
    Code:
    txtName.DataBindings.Add("Text", dtProjetoBindingSource, "NameProject")
    We have no idea what changes you've made to the code since the last time you posted it.

    The better you describe the situation and the more information you provide, the better the help you will receive

  6. #46

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Quote Originally Posted by wes4dbt View Post
    I'm going to let kpmc work you through this project. I just want to say you need to provide more information so he can provide the help you need.

    If there is an error, ALWAYS tell us what the error is and what line of code causes the error.

    Don't just post one single line of code, it provides no context of what happened before.
    Code:
    txtName.DataBindings.Add("Text", dtProjetoBindingSource, "NameProject")
    We have no idea what changes you've made to the code since the last time you posted it.

    The better you describe the situation and the more information you provide, the better the help you will receive
    Ok. I get it. So here we go.

    This is my ModuleData:
    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 = "dtProject"})
                With .Tables("dtProject")
                    .Columns.Add(New DataColumn With {
                                 .ColumnName = "IDProject",
                                 .AutoIncrement = True,
                                 .DataType = GetType(Integer)})
                    .Columns.Add("Name", GetType(String))
                    .Columns.Add("DateCreation", GetType(Date))
                    .Columns("DateCreation").DefaultValue = GetDateTime_instante()
                    .Columns.Add("Directory", GetType(String))
                    .Columns.Add("Type", GetType(String))
                    .Columns.Add("Description", GetType(String))
    
                End With
    
                .Tables.Add(New DataTable With {.TableName = "dtOptionA"})
                With .Tables("dtOptionA ")
                    .Columns.Add(New DataColumn With {
                                 .ColumnName = "IDOptionA ",
                                 .AutoIncrement = True,
                                 .DataType = GetType(Integer)})
                    .Columns.Add("ProjectID", GetType(Integer))
                    .Columns.Add(New DataColumn With {
                                 .ColumnName = "CalculationA",
                                 .DataType = GetType(Boolean)})
                    .Columns("CalculationA ").DefaultValue = False
                    .Columns.Add(New DataColumn With {
                                 .ColumnName = " CalculationB",
                                 .DataType = GetType(Boolean)})
                    .Columns("CalculationB").DefaultValue = False
                   
            End With
    
        End Sub
    
    End Module
    And this is the Code in my main form:
    Code:
    Public Class MainForm
    
        Private WithEvents dtProjectBindingSource As New BindingSource
    
        'Create a var to the DataSet
        Dim _Dset As DataSet = ModuleData.Dset
    
        'Create a var to the DataSet that contains schema only
        Dim _DsetSchemaAs DataSet = ModuleData.DsetSchema
    
    
        Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles Me.Load
    
    'gives a start value to the varuable myXMLFilePath
            myXMLFilePath = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "Xmlfile.xml"
    
            dtProjectBindingSource.DataSource = _Dset.Tables("dtProject")
    
            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("ProjectOptionARelation") Then
                    _Dset.Relations.Add(New DataRelation("ProjectOptionARelation ",
                                                     _Dset.Tables("dtProject").Columns("IDProject"),
                                                     _Dset.Tables("dtOptionA").Columns("IDOptionA"), True))
                End If
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
    
        End Sub
    
        'Button to open the folder where the user will save the XML file
        Private Sub btnProcurar_Click(sender As Object, e As EventArgs) Handles btnProcurar.Click
            If (FolderBrowserDialog1.ShowDialog() = DialogResult.OK) Then
    
                myXMLFolderPath = FolderBrowserDialog1.SelectedPath
                txtDiretory.Text = myXMLFolderPath
    
     
     myXMLFilePath = FolderBrowserDialog1.SelectedPath & "\" & “myXMLFile.xml”
            End If
    
        End Sub
    
        Private Sub tsNew_Click(sender As Object, e As EventArgs) Handles tsNew.Click
            dtProjectBindingSource.AddNew()
     
        End Sub
    
        Private Sub tsSave_Click(sender As Object, e As EventArgs) Handles tsSave.Click
            Me.Validate()  
    
              
            'checks to see if there is a file with the same name in the chosen directory
            If File.Exists(myXMLFilePath) Then
                If MsgBox("A file with this name already exists in this directory. Do you want to overwrite?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, " ") = MsgBoxResult.Yes Then
                    File.Delete(myXMLFilePath)
                End If
            Else
            End If
    
            dtProjectBindingSource.EndEdit()
    
    
            Try
                'DataBind the control property to the BindingSource/DataProperty(column name)
                txtNameProject.DataBindings.Add("Text", dtProjectBindingSource, "Name")
                txtCreation.DataBindings.Add("Text", dtProjectBindingSource, "DateCreation")
                txtDirectory.DataBindings.Add("Text", dtProjectBindingSource, "Directory")
                cmbType.DataBindings.Add("Text", dtProjectBindingSource, "Type")
                txtDescription.DataBindings.Add("Text", dtProjectBindingSource, "Description")
    
                _Dset.WriteXml(myXMLFilePath)
                MsgBox("Data saved sussessfully.")
    
            Catch ex As Exception
                MsgBox("Something went wrong, 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 = "Escolha arquivo para abrir",
           .Filter = "XML (*.xml)|*.xml",
           .FilterIndex = 0,
           .InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)}
    
            If ofd.ShowDialog = DialogResult.OK Then
    
    
                 'extract the address of the file
                Dim filename As String = ofd.FileName 
                myXMLFilePath = filename
    
                If (File.Exists(filename)) Then
                    _Dset.ReadXml(myXMLFilePath)
                    ' dtProjectBindingSource.DataSource = _Dset.dtProject
    
                    txtNomeArquivo.Text = Path.GetFileName(myXMLFilePath)
    
               End If
    
               End If
    
        End Sub
    
        Private Sub tsDelete_Click(sender As Object, e As EventArgs)
            My.Computer.FileSystem.DeleteFile(myXMLFilePath)
    
        End Sub
    
        Private Sub tsNextForm_Click(sender As Object, e As EventArgs) Handles tsNextForm.Click
            If cmbType.Text = "Option A" Then
                frm_OptionA.ShowDialog()
                Me.Close()
            Else        If cmbType.Text = "Option B" Then
                frm_OptionB.ShowDialog()
                Me.Close()
    
    
            End If
        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(myXMLFilePath, XmlWriteMode.WriteSchema)
                End If
    
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Sub
    
    End Class
    When I run the program, I am able to fill the form with the Data. But When I Click Save I get this error:Name:  error.jpg
Views: 176
Size:  28.3 KB
    Last edited by veronica.sa; Jan 25th, 2019 at 08:21 PM.

  7. #47

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Not trying to confuse the OP. Just pointing out that XML is not a database. If the OP want to learn how to develop Relational Database Management Systems then XML is the wrong tool. If the OP wants to use an Xml file, that's fine with me.
    NOt confused about this. I know a XML is not a DataBase.
    Last edited by veronica.sa; Jan 25th, 2019 at 08:23 PM.

  8. #48

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Looking at your code briefly, this doesnt make sense. You are checking _Dset for a relation then adding it to _DsetSIDE I can't think of a good reason to have 2 DataSet's in a solution.
    Yes, I know. I had changed the name and was changing back and one was missed.
    Last edited by veronica.sa; Jan 25th, 2019 at 08:23 PM.

  9. #49
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,013

    Re: DataTable Relation - Want to save several Datatables into one xml

    have you had any experience with breakpoints? If not, now is a good time to start.
    You should see what the value of your datapath is
    myXMLFilePath = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "Xmlfile.xml"
    Would most likely look something like this: C:\Users\{username}\DocumentsXmlfile.xml
    Code:
    Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Xmlfile.xml"
    May make a bit more sense

  10. #50
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,013

    Re: DataTable Relation - Want to save several Datatables into one xml

    I'm going to let kpmc work you through this project
    By all means Wes, help where you can. Certainly do not rely on me

  11. #51
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,406

    Re: DataTable Relation - Want to save several Datatables into one xml

    well i have to agree with wes -wrong tool-
    unless you are an experienced XML Develp. you will be running around in circles and kpmc
    has took you way bejond ..just Helping a bit ..or putting you in the right direction.

    my advice .. use a Database ..MySql, Access to start with
    Last edited by ChrisE; Jan 26th, 2019 at 02:14 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #52

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Quote Originally Posted by ChrisE View Post
    well i have to agree with wes -wrong tool-
    unless you are an experienced XML Develp. you will be running around in circles and kpmc
    has took you way bejond ..just Helping a bit ..or putting you in the right direction.

    my advice .. use a Database ..MySql, Access to start with
    If I were to use a DataBase, it would have to be some free. And that does not require that the user have to install another program besides in the setup of my system. Is there a Database like this?

  13. #53

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Quote Originally Posted by kpmc View Post
    have you had any experience with breakpoints? If not, now is a good time to start.
    You should see what the value of your datapath is

    Would most likely look something like this: C:\Users\{username}\DocumentsXmlfile.xml
    Code:
    Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\Xmlfile.xml"
    May make a bit more sense
    Any comment on line 157, where the problem is pointed? The syntax seems to be ok, so I do not know how to make it works.


    This is the error I am getting now. I checked the columns names in case it is case sensitive, and the names are equal.

    System.ArgumentException: 'Cannot bind to the property or column Name on the DataSource.
    Parameter name: dataMember'
    Last edited by veronica.sa; Jan 26th, 2019 at 03:35 PM.

  14. #54
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,432

    Re: DataTable Relation - Want to save several Datatables into one xml

    It doesn't look like kpmc is around today so I'll take a look at your code and get back to you. You never answered the question about using Break Points to help you debug your code (if you don't know about break points you should learn, they're a very valuable tool).

    kpmc was trying to show you that you needed a "" in your data path, "\Xmlfile.xml". I can tell you right now that you doing that data binding in the wrong place, you don't want to wait till after the data has been entered, do it in the form load event.

  15. #55

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    You never answered the question about using Break Points to help you debug your code (if you don't know about break points you should learn, they're a very valuable tool).
    I think I missed this question. I will look at it.

    kpmc was trying to show you that you needed a "" in your data path, "\Xmlfile.xml".
    I corrected it. Thanks.

    I can tell you right now that you doing that data binding in the wrong place, you don't want to wait till after the data has been entered, do it in the form load event.
    Thanks, wes4dbt. I was with this doubt. Tried in the Form Load, and get the same error.

  16. #56
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,432

    Re: DataTable Relation - Want to save several Datatables into one xml

    I added the databinding and the Add and Next functions to kpmc example. Got it working, you should be able to adapt it to you project.

    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")
    
                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
    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))
                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
    For some reason when you add a new record to the People datatable, you have Save the new record before you can add Invoice data for that new Person. Maybe I've done something wrong an kpmc can fix that issue.

    The form has five controls,

    PersonIDTextBox
    FulNameTextBox
    DataGridView1
    AddButton
    SaveButton
    NextButton

    edit - You have a few places in your where there are blank spaces added to names, this needs to be fixed.

    .Tables("dtOptionA "
    .ColumnName = "IDOptionA "
    .ColumnName = " CalculationB"

    There might be more.

    Also, the DataGridView will only display CheckBoxes the first time you run the program. After that they lose that format and only display the word true or false. The IDOptionA field stops autogenerating. there's probably fixes for these thing but XML is not my strong suit.
    Last edited by wes4dbt; Jan 26th, 2019 at 10:29 PM.

  17. #57
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,406

    Re: DataTable Relation - Want to save several Datatables into one xml

    Quote Originally Posted by veronica.sa View Post
    If I were to use a DataBase, it would have to be some free. And that does not require that the user have to install another program besides in the setup of my system. Is there a Database like this?
    you can create a Access Database (has to .mdb) with code, either SQL or with Powershell script would create the DB and Table(s)

    with .mdb Jet.4.0
    Access doesn't have to be installed on the PC, if the version is Access accdb. it wont work
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  18. #58

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Quote Originally Posted by wes4dbt View Post
    Got it working, you should be able to adapt it to you project.
    I adapted and it is saving.


    For some reason when you add a new record to the People datatable, you have Save the new record before you can add Invoice data for that new Person. Maybe I've done something wrong an kpmc can fix that issue.

    The IDOptionA field stops autogenerating. there's probably fixes for this thing but XML is not my strong suit.
    I will test it in my adaptation and see if I am getting the same.

    Thanks

  19. #59

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Thanks For the answer CrisE, I will study about it to be able to apply it in the future.

  20. #60
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,432

    Re: DataTable Relation - Want to save several Datatables into one xml

    Another option for a database is SQLite. No installation required, just put the libraries is the same folder as the apps exe. Nice free management studio called SqLiteStudio for creating the databases.

  21. #61

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: 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
    Last edited by veronica.sa; Feb 15th, 2019 at 02:45 PM.

  22. #62
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,013

    Re: DataTable Relation - Want to save several Datatables into one xml

    First thing, you chould be opening Form4 as follows
    Code:
    Using _Form4 As New Form4
      _Form4.SHowDialog
    End Using

  23. #63

    Thread Starter
    Lively Member
    Join Date
    Aug 2017
    Location
    Brazil
    Posts
    87

    Re: DataTable Relation - Want to save several Datatables into one xml

    Quote Originally Posted by kpmc View Post
    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.

  24. #64
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,432

    Re: DataTable Relation - Want to save several Datatables into one xml

    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.
    Last edited by wes4dbt; Feb 16th, 2019 at 11:12 PM.

Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width