-
Jan 25th, 2019, 04:17 PM
#41
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by kpmc
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.
-
Jan 25th, 2019, 04:19 PM
#42
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.
-
Jan 25th, 2019, 04:22 PM
#43
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.
-
Jan 25th, 2019, 04:23 PM
#44
Thread Starter
Lively Member
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.
-
Jan 25th, 2019, 07:23 PM
#45
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
-
Jan 25th, 2019, 08:02 PM
#46
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by wes4dbt
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:
Last edited by veronica.sa; Jan 25th, 2019 at 08:21 PM.
-
Jan 25th, 2019, 08:18 PM
#47
Thread Starter
Lively Member
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.
-
Jan 25th, 2019, 08:19 PM
#48
Thread Starter
Lively Member
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.
-
Jan 25th, 2019, 11:18 PM
#49
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
-
Jan 25th, 2019, 11:24 PM
#50
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
-
Jan 26th, 2019, 02:11 AM
#51
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.
-
Jan 26th, 2019, 10:42 AM
#52
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by ChrisE
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?
-
Jan 26th, 2019, 10:44 AM
#53
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by kpmc
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.
-
Jan 26th, 2019, 03:28 PM
#54
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.
-
Jan 26th, 2019, 03:37 PM
#55
Thread Starter
Lively Member
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.
-
Jan 26th, 2019, 06:47 PM
#56
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.
-
Jan 28th, 2019, 04:26 AM
#57
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by veronica.sa
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.
-
Jan 28th, 2019, 07:13 AM
#58
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
Originally Posted by wes4dbt
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
-
Jan 28th, 2019, 07:15 AM
#59
Thread Starter
Lively Member
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.
-
Jan 28th, 2019, 02:05 PM
#60
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.
-
Feb 15th, 2019, 02:31 PM
#61
Thread Starter
Lively Member
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.
-
Feb 15th, 2019, 02:51 PM
#62
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
-
Feb 16th, 2019, 10:02 AM
#63
Thread Starter
Lively Member
Re: DataTable Relation - Want to save several Datatables into one xml
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.
-
Feb 16th, 2019, 10:18 PM
#64
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|