locked
"System.NullReferenceException" While Trying To Combine Two Forms In Parent-Child Relationship. RRS feed

  • Question

  • Good day.

    Situation

    My goal is to create a series or Parent Child relationships to create nested datatables. I am starting with the first two and can expand as needed. 

    I am trying to create a Parent-Child Relationship between two datatables so that questions and comments of related material can be nested together.

    Per recommendations found online for Parent-Child Relationships, I am trying to utilize a dataset to create a relation between the Primary Key columns of each table and then show the results in a Datagridview.

    In each datatable, My data structure is as follows:

    Col1: PrimaryKeys for each row

    Col2: "Entry_Type" to classify the user input as a question or comment 

    Col3: Sting input from user.

    For testing purposes, I've also added ten rows of generic data in each table in an attempt to avoid 'null' values.

    Error

    On compile, I see 0 Errors and 0 Warnings.

    But when I run the code, my code stops and highlights my dataset code block where I attempt to join columns from the two datatables and I get the following error:

    "System.NullReferenceException: 'Object reference not set to an instance of an object.'

    System.Data.DataTableCollection.this[string].get returned Nothing." 

    Code  

           

    'Create DataTable for Level 1 of Parent Child Entries
            Dim EntriesTable1 As New DataTable("Saved Questions and Comments") 

            'Create Column For PrimaryKeys
            EntriesTable1.Columns.Add("PrimaryKey", Type.GetType("System.Int32"))

            'Create a column to Designate an Input as a Question or Comment; 
            EntriesTable1.Columns.Add("Entry Type", Type.GetType("System.String"))

            'Create a column to collect user questions and comments
            EntriesTable1.Columns.Add("Questions and Comments", Type.GetType("System.String"))

            'Poplute Rows
            For i As Integer = 1 To 10
                EntriesTable1.Rows.Add(i, "String1", "String2")

            Next

    'Create Table for Level2 Entries
            Dim EntriesTable2 As New DataTable("Saved Questions and Comments") 

            'Create Column For PrimaryKeys
            EntriesTable2.Columns.Add("PrimaryKey", Type.GetType("System.Int32"))

            'Create Columns To Designate an Input as a Question or Comment; Test later to make sure Inputs are unique
            EntriesTable2.Columns.Add("Entry_Type", Type.GetType("System.String"))
            EntriesTable2.Columns.Add("Questions and Comments", Type.GetType("System.String"))

            For i2 As Integer = 1 To 10
                EntriesTable2.Rows.Add(i2, "String1", "String2")

            Next

            Dim Entries As New DataSet

           


            'This next entire section of code from "Entries.Relations....))"  is where the error occurs

           

             Entries.Relations.Add("Saved Questions and Comments",
                                  Entries.Tables("EntriesTable1").Columns("PrimaryKey"),
                                  Entries.Tables("EntriesTable2").Columns("PrimaryKey"))

            DataGridView1.DataSource = Entries

    Questions

    1. Why is this error occurring?

    2. Regarding types for primary keys and .Columns.Add function.

    The examples I found for creating primary keys in a datatable all listed   Type.GetType("System.Int32") 

    And for the Table.Columns.Add function I see mainly two types:  Type.GetType("System.Int32")    OR    Type.GetType("System.String")

    It also appears that Type.GetType("System.Int32") is VB.NET equivalent of an "Autonumber" in something like Access. Is this the proper type to call for column of primary keys? Is this indeed the best type to use? I tried simply using a Internet or String and wouldn't compile.

    3. Regarding the Type.GetType("System.Int32") statement, is this funtion only declaring a type or is it also supposed to populate a value in the variable at the same time? If not, how do I populate a value for this type of statement? Because Anytime I Dim another variable as Integer or String and try to make it equal this statement, I get an error. So how do I assign a value to this if it is indeed void?

    Thanks in advance for your assistance.

    With Much Respect,

    Monday, April 2, 2018 10:46 PM

Answers

  • 1) You didn't add the Tables to the Dataset.

    2) No you must set the column up as an auto incrementing entity  "System.Int32" is the data type

    3) If you set the auto incrementing column up it will populate.  Look up Auto Incrementing MSSQL, seed direction, seed value.


    Robert McArthur


    Monday, April 2, 2018 10:51 PM
  • Hi A.O,

    From you two table data, one is EntriesTable1, another is EntriesTable2, I did not see that these two tables conform to the relationship between parent and child.

    I do one example about parent-child relation.

     Private tblCustomer As DataTable
        Private tblOrder As DataTable
        Private tblDataSet As DataSet
        Private Sub FrmTablerelation_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            tblCustomer = New DataTable("tblCustomer")
            tblOrder = New DataTable("tblOrder")
            tblDataSet = New DataSet()
    
            tblCustomer.Columns.Add("ID", GetType(Integer))
            tblCustomer.Columns.Add("CustomerName", GetType(String))
    
            tblOrder.Columns.Add("ID", GetType(Integer))
            tblOrder.Columns.Add("Order", GetType(String))
            tblOrder.Columns.Add("CustomerID", GetType(Integer))
    
            tblCustomer.Rows.Add(1, "Jane Doe")
            tblCustomer.Rows.Add(2, "John Smith")
            tblCustomer.Rows.Add(3, "Richard Roe")
    
            tblOrder.Rows.Add(1, "Order1.1", 1)
            tblOrder.Rows.Add(2, "Order1.2", 1)
            tblOrder.Rows.Add(3, "Order1.3", 1)
            tblOrder.Rows.Add(4, "Order2.1", 2)
            tblOrder.Rows.Add(5, "Order3.1", 3)
            tblOrder.Rows.Add(6, "Order3.2", 3)
    
            tblDataSet.Tables.Add(tblCustomer)
            tblDataSet.Tables.Add(tblOrder)
            tblDataSet.Relations.Add("CustOrderRelation", tblCustomer.Columns("ID"), tblOrder.Columns("CustomerID"))
    
            Dim bsCustomer As BindingSource = New BindingSource()
            bsCustomer.DataSource = tblDataSet
            bsCustomer.DataMember = "tblCustomer"
    
            Dim bsOrder As BindingSource = New BindingSource()
            bsOrder.DataSource = bsCustomer
            bsOrder.DataMember = "CustOrderRelation"
    
            dgvCustomer.DataSource = bsCustomer
            dgvOrder.DataSource = bsOrder
    
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 3, 2018 8:02 AM
  • Hello,

    Note that the null can be from a problem with data, not the actual code were the best way to find out is running test outside of code in MS-Access query editor or SQL-Server using SQL-Server Management Studio or via creating SELECTS via adding a new query via the Visual Studio Server Explorer.

    If you would like to see a working solution using one form for a master-detail relationship using SQL-Server as the database look at my MSDN code sample. Since all the data logic is in a data class you could modify the code to work with two or more forms. Now this code sample goes as follows.

    • Customer table is the master table
    • Orders are the details to customers
    • Orders is the master table to items e.g. Order Details.

    Remove Order Details and we have a normal master-detail. The code sample also shows how to do add/edit and deletes.

    Screenshot for editing over the main form.

    Using the exact same model I have another MSDN code example that is bare bone to learn how to do only the relations. Note in the screenshot below the red squares are showing the primary key for each table which is helpful for validating everything is working correctly.

    The database tables for the above

    If you need an example with MS-Access, see my MSDN code sample which also uses the same pattern as the ones for SQL-Server.

    I also included a special debug window


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, April 3, 2018 9:57 AM
  • Thanks for your excellent analysis and advice, Cherry. 

    You identified a problem in my code and also provided sample code that models the correct convention so I can see how to make it work.  I also really like the animated screenshots. They help to see how it comes together. This is a very useful response. Thank you for your time and effort. 

    With Much Respect,

    Tuesday, April 3, 2018 4:31 PM
  • FABULOUS RESPONSE....

    Thank you so much, Karen. It has been many years since I've developed an app, as I am sure it is quite apparent. And my skills are very very rusty, lol. So, I am basically rebuilding my knowledge from scratch with new technologies and frameworks.

    I have not done much work with SQL yet, but it is one of the platforms I am wanting to build my skills with as well. So thank you for that recommendation.

    That's why yours, and Cherry's, responses are so useful. You give potential solutions. You also provide a variety of resources to help me understand things including screenshots, sample code, tools, links to resources, hints, etc. that can easily be researched and investigated. 

    I'll have to do some looking into the resources you provided. God bless you all and thanks again.

    I really appreciate the excellent assistance.

    With Much Respect,

    Brent@Alpha and Omega Business Solutions

     

    Tuesday, April 3, 2018 4:56 PM

All replies

  • Good day.

    Situation

    My goal is to create a series or Parent Child relationships to create nested datatables. I am starting with the first two and can expand as needed. 

    I am trying to create a Parent-Child Relationship between two datatables so that questions and comments of related material can be nested together.

    Per recommendations found online for Parent-Child Relationships, I am trying to utilize a dataset to create a relation between the Primary Key columns of each table and then show the results in a Datagridview.

    In each datatable, My data structure is as follows:

    Col1: PrimaryKeys for each row

    Col2: "Entry_Type" to classify the user input as a question or comment 

    Col3: Sting input from user.

    For testing purposes, I've also added ten rows of generic data in each table in an attempt to avoid 'null' values.

    Error

    On compile, I see 0 Errors and 0 Warnings.

    But when I run the code, my code stops and highlights my dataset code block where I attempt to join columns from the two datatables and I get the following error:

    "System.NullReferenceException: 'Object reference not set to an instance of an object.'

    System.Data.DataTableCollection.this[string].get returned Nothing." 

    Code  

           

    'Create DataTable for Level 1 of Parent Child Entries
            Dim EntriesTable1 As New DataTable("Saved Questions and Comments") 

            'Create Column For PrimaryKeys
            EntriesTable1.Columns.Add("PrimaryKey", Type.GetType("System.Int32"))

            'Create a column to Designate an Input as a Question or Comment; 
            EntriesTable1.Columns.Add("Entry Type", Type.GetType("System.String"))

            'Create a column to collect user questions and comments
            EntriesTable1.Columns.Add("Questions and Comments", Type.GetType("System.String"))

            'Poplute Rows
            For i As Integer = 1 To 10
                EntriesTable1.Rows.Add(i, "String1", "String2")

            Next

    'Create Table for Level2 Entries
            Dim EntriesTable2 As New DataTable("Saved Questions and Comments") 

            'Create Column For PrimaryKeys
            EntriesTable2.Columns.Add("PrimaryKey", Type.GetType("System.Int32"))

            'Create Columns To Designate an Input as a Question or Comment; Test later to make sure Inputs are unique
            EntriesTable2.Columns.Add("Entry_Type", Type.GetType("System.String"))
            EntriesTable2.Columns.Add("Questions and Comments", Type.GetType("System.String"))

            For i2 As Integer = 1 To 10
                EntriesTable2.Rows.Add(i2, "String1", "String2")

            Next


            'This next entire section of code from "Entries.Relations....))"  is where the error occurs

            Dim Entries As New DataSet
            Entries.Relations.Add("Saved Questions and Comments",
                                  Entries.Tables("EntriesTable1").Columns("PrimaryKey"),
                                  Entries.Tables("EntriesTable2").Columns("PrimaryKey"))

            DataGridView1.DataSource = Entries

    Questions

    1. Why is this error occurring?


    2. The examples I found for creating primary keys in a datatable all listed   Type.GetType("System.Int32") 

    And for the Table.Columns.Add function I see mainly two types:  Type.GetType("System.Int32")    OR    Type.GetType("System.String")

    It also appears that Type.GetType("System.Int32") is VB.NET equivalent of an "Autonumber" in something like Access. Is this the proper type to call for column of primary keys? Is this indeed the best type to use? I tried simply using a Internet or String and wouldn't compile.

    3. Regarding the Type.GetType("System.Int32") statement, is this funtion only declaring a type or is it also supposed to populate a value in the variable at the same time? If not, how do I populate a value for this type of statement? Because Anytime I Dim another variable as Integer or String and try to make it equal this statement, I get an error. So how do I assign a value to this if it is indeed void?

    Thanks in advance for your assistance.

    With Much Respect,

    • Merged by Stanly Fan Wednesday, April 11, 2018 6:36 AM the same thread
    Monday, April 2, 2018 10:39 PM
  • 1) You didn't add the Tables to the Dataset.

    2) No you must set the column up as an auto incrementing entity  "System.Int32" is the data type

    3) If you set the auto incrementing column up it will populate.  Look up Auto Incrementing MSSQL, seed direction, seed value.


    Robert McArthur


    Monday, April 2, 2018 10:51 PM
  • Hi,

    >>1. Why is this error occurring?

    There are more than one error in your code, please refer to the following solution:

    1, Please change the name to "EntriesTable1" instead of "Saved Questions and Comments":

        'Dim EntriesTable1 As New DataTable("Saved Questions and Comments")
        Dim EntriesTable1 As New DataTable("EntriesTable1")

    Also for "EntriesTable2":

        Dim EntriesTable2 As New DataTable("EntriesTable2")

    2, You need to add the two tables to the dataset:

        Dim Entries As New DataSet
        Entries.Tables.Add(EntriesTable1)
        Entries.Tables.Add(EntriesTable2)

    3, You need to bind a datatable to the datagridview to display the records instead of dataset:

        DataGridView1.DataSource = Entries.Tables(1)

    So the complete code:

        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            'Create DataTable for Level 1 of Parent Child Entries
            'Dim EntriesTable1 As New DataTable("Saved Questions and Comments") 
            Dim EntriesTable1 As New DataTable("EntriesTable1")
            'Create Column For PrimaryKeys
            EntriesTable1.Columns.Add("PrimaryKey", Type.GetType("System.Int32"))
            'Create a column to Designate an Input as a Question or Comment; 
            EntriesTable1.Columns.Add("Entry Type", Type.GetType("System.String"))
            'Create a column to collect user questions and comments
            EntriesTable1.Columns.Add("Questions and Comments", Type.GetType("System.String"))
            'Poplute Rows
            For i As Integer = 1 To 10
                EntriesTable1.Rows.Add(i, "String1", "String2")
            Next
    
            Dim EntriesTable2 As New DataTable("EntriesTable2")
            'Create Column For PrimaryKeys
            EntriesTable2.Columns.Add("PrimaryKey", Type.GetType("System.Int32"))
            'Create Columns To Designate an Input as a Question or Comment; Test later to make sure Inputs are unique
            EntriesTable2.Columns.Add("Entry_Type", Type.GetType("System.String"))
            EntriesTable2.Columns.Add("Questions and Comments", Type.GetType("System.String"))
            For i2 As Integer = 1 To 10
                EntriesTable2.Rows.Add(i2, "String1", "String2")
            Next
    
            'This next entire section of code from "Entries.Relations....))"  is where the error occurs
            Dim Entries As New DataSet
            Entries.Tables.Add(EntriesTable1)
            Entries.Tables.Add(EntriesTable2)
    
            Entries.Relations.Add("Saved Questions and Comments",
                                  Entries.Tables("EntriesTable1").Columns("PrimaryKey"),
                                  Entries.Tables("EntriesTable2").Columns("PrimaryKey"))
            DataGridView1.DataSource = Entries.Tables(1)
        End Sub
    

    >>2,  Is this the proper type to call for column of primary keys?

    The type of primary key is not limited as long as it is unique.

    >>3, Regarding the Type.GetType("System.Int32") statement, is this funtion only declaring a type or is it also supposed to populate a value in the variable at the same time?

    It just declares a type and does not fill in any data, you can add some records like:

        EntriesTable1.Rows.Add(i, "String1", "String2")

    Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 3, 2018 3:08 AM
  • Hi A.O,

    From you two table data, one is EntriesTable1, another is EntriesTable2, I did not see that these two tables conform to the relationship between parent and child.

    I do one example about parent-child relation.

     Private tblCustomer As DataTable
        Private tblOrder As DataTable
        Private tblDataSet As DataSet
        Private Sub FrmTablerelation_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            tblCustomer = New DataTable("tblCustomer")
            tblOrder = New DataTable("tblOrder")
            tblDataSet = New DataSet()
    
            tblCustomer.Columns.Add("ID", GetType(Integer))
            tblCustomer.Columns.Add("CustomerName", GetType(String))
    
            tblOrder.Columns.Add("ID", GetType(Integer))
            tblOrder.Columns.Add("Order", GetType(String))
            tblOrder.Columns.Add("CustomerID", GetType(Integer))
    
            tblCustomer.Rows.Add(1, "Jane Doe")
            tblCustomer.Rows.Add(2, "John Smith")
            tblCustomer.Rows.Add(3, "Richard Roe")
    
            tblOrder.Rows.Add(1, "Order1.1", 1)
            tblOrder.Rows.Add(2, "Order1.2", 1)
            tblOrder.Rows.Add(3, "Order1.3", 1)
            tblOrder.Rows.Add(4, "Order2.1", 2)
            tblOrder.Rows.Add(5, "Order3.1", 3)
            tblOrder.Rows.Add(6, "Order3.2", 3)
    
            tblDataSet.Tables.Add(tblCustomer)
            tblDataSet.Tables.Add(tblOrder)
            tblDataSet.Relations.Add("CustOrderRelation", tblCustomer.Columns("ID"), tblOrder.Columns("CustomerID"))
    
            Dim bsCustomer As BindingSource = New BindingSource()
            bsCustomer.DataSource = tblDataSet
            bsCustomer.DataMember = "tblCustomer"
    
            Dim bsOrder As BindingSource = New BindingSource()
            bsOrder.DataSource = bsCustomer
            bsOrder.DataMember = "CustOrderRelation"
    
            dgvCustomer.DataSource = bsCustomer
            dgvOrder.DataSource = bsOrder
    
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 3, 2018 8:02 AM
  • It seems you mix up a lot which is not reality. You tell a lot which simply is not true.  

    I was trying to write a text what you miss, but you write in fact only what you think it is. 

    Maybe better to tell your goal, creating a dataset with primary keys only for in memory use is in 2018 a little bit from 15 years ago. 


    Success
    Cor

    Tuesday, April 3, 2018 8:36 AM
  • Hello,

    Note that the null can be from a problem with data, not the actual code were the best way to find out is running test outside of code in MS-Access query editor or SQL-Server using SQL-Server Management Studio or via creating SELECTS via adding a new query via the Visual Studio Server Explorer.

    If you would like to see a working solution using one form for a master-detail relationship using SQL-Server as the database look at my MSDN code sample. Since all the data logic is in a data class you could modify the code to work with two or more forms. Now this code sample goes as follows.

    • Customer table is the master table
    • Orders are the details to customers
    • Orders is the master table to items e.g. Order Details.

    Remove Order Details and we have a normal master-detail. The code sample also shows how to do add/edit and deletes.

    Screenshot for editing over the main form.

    Using the exact same model I have another MSDN code example that is bare bone to learn how to do only the relations. Note in the screenshot below the red squares are showing the primary key for each table which is helpful for validating everything is working correctly.

    The database tables for the above

    If you need an example with MS-Access, see my MSDN code sample which also uses the same pattern as the ones for SQL-Server.

    I also included a special debug window


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, April 3, 2018 9:57 AM
  • Thanks Robert. I appreciate the advice and the resources.

    With Much Respect,

    Tuesday, April 3, 2018 4:26 PM
  • Thanks for your excellent analysis and advice, Cherry. 

    You identified a problem in my code and also provided sample code that models the correct convention so I can see how to make it work.  I also really like the animated screenshots. They help to see how it comes together. This is a very useful response. Thank you for your time and effort. 

    With Much Respect,

    Tuesday, April 3, 2018 4:31 PM
  • FABULOUS RESPONSE....

    Thank you so much, Karen. It has been many years since I've developed an app, as I am sure it is quite apparent. And my skills are very very rusty, lol. So, I am basically rebuilding my knowledge from scratch with new technologies and frameworks.

    I have not done much work with SQL yet, but it is one of the platforms I am wanting to build my skills with as well. So thank you for that recommendation.

    That's why yours, and Cherry's, responses are so useful. You give potential solutions. You also provide a variety of resources to help me understand things including screenshots, sample code, tools, links to resources, hints, etc. that can easily be researched and investigated. 

    I'll have to do some looking into the resources you provided. God bless you all and thanks again.

    I really appreciate the excellent assistance.

    With Much Respect,

    Brent@Alpha and Omega Business Solutions

     

    Tuesday, April 3, 2018 4:56 PM
  • Ok, so according to your post what is a more "modern" solution?

    What is the error in thinking?

    What tools can I use?

    What resources can I refer to?

    What example code will help me find the solution?

    With Much Respect,

    Tuesday, April 3, 2018 5:12 PM
  • You use a solution, but do you already know what you want to solve?

    Has it to do with databases or something like that, maybe soap, maybe......................

    If you tell what you are after than mostly you get a better answer. The dataset is from 2002, our website is completely overloaded with it. But therefore it is not a fix for everything. 

    Here a an example what you seemed to meant on our website since about 2010.

    http://www.vb-tips.com/DataGridRelation.aspx

    Therefore tell what you want to solve and not that you want to know how to use an horse and cart to do your business in the city of NY. 

    And the error in thinking is that you make assumptions which simply are not true. 


    Success
    Cor









    Tuesday, April 3, 2018 6:10 PM