I need help filling a dataset

See more:

VB

I am trying to get this to where I can pass values and do either updates or inserts. However, I am still getting mismatched columns when I run the code. For instance, a column from the death table will end up in the profile table and it will kick back this column is not in this data table "profile".

Imports System.Data.SqlClient

Module Insertmodule

    Dim dt As New DataTable
    Dim ds As New DataSet
    Dim dtr As DataRow
    'connect to 
    Dim sqlconn1 As New SqlConnection("sqlstring")
    Dim sqladaptor = New SqlDataAdapter
    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqladaptor)

    Public Sub Insert()

        sqlconn1.Open()

        'here we want to call each table table name to confirm as accept all new updated data
        sqladaptor.SelectCommand = New SqlCommand("Select * from Profile", sqlconn1)
        sqladaptor.Fill(ds, "Profile")

        sqladaptor.SelectCommand.CommandText = " Select * from Death"
        sqladaptor.Fill(ds, "Death")

        sqladaptor.SelectCommand.CommandText = " Select * from Cemetery_reference"
        sqladaptor.Fill(ds, "Cemetery_reference")

        sqladaptor.SelectCommand.CommandText = " Select * from Burial"
        sqladaptor.Fill(ds, "Burial")

        'you can add as many dtr items as you need per dataset

    End Sub
    public Sub profile(Individual_ID As String, First_name As String, Middle_name As String, Last_name As String, Sex As String, Race As String, Place_of_birth As String, State_of_death As String, County_of_death As String, Date_of_birth As String)

        Insert()
        'Profile
        dtr = ds.Tables("Profile").NewRow()
        dtr.Item("Individual_ID") = "77"
        dtr.Item("First_name") = ""
        dtr.Item("Middle_name") = ""
        dtr.Item("Last_name") = ""
        dtr.Item("Sex") = ""
        dtr.Item("Race") = ""
        dtr.Item("Place_of_birth") = ""
        dtr.Item("State_of_birth") = ""
        dtr.Item("County_of_birth") = ""
        dtr.Item("Date_of_birth") = ""
        ds.Tables("Profile").Rows.Add(dtr)

        sqladaptor.InsertCommand = cb.GetInsertCommand()
        sqladaptor.Update(ds.Tables("Profile"))

        sqlconn1.Close()
        sqlconn1.Dispose()
        sqladaptor.Dispose()

    End Sub

    Sub death()
        Insert()
        'Death
        dtr = ds.Tables("Death").NewRow()
        dtr.Item("Individual_ID") = ""
        dtr.Item("Place_of_death") = ""
        ds.Tables("Death").Rows.Add(dtr)

        sqladaptor.InsertCommand = cb.GetInsertCommand()
        sqladaptor.Update(ds.Tables("Death"))

        sqlconn1.Close()
        sqlconn1.Dispose()
        sqladaptor.Dispose()

    End Sub

    Sub Cemetery_reference()
        'Cemetery_reference
        dtr = ds.Tables("Cemetery_reference").NewRow()
        ' dtr.Item("Individual_ID") = ""
        'dtr.Item("Burial_date") = "1/28/2019"
        ' dtr.Item("Place_of_death") = ""
        ds.Tables("Cemetery_reference").Rows.Add(dtr)

        sqladaptor.InsertCommand = cb.GetInsertCommand()
        sqladaptor.Update(ds.Tables("Cemetery_reference"))

        sqlconn1.Close()
        sqlconn1.Dispose()
        sqladaptor.Dispose()
    End Sub

    Sub burials()
        'Burials
        dtr = ds.Tables("Burials").NewRow()
        dtr.Item("Individual_ID") = "77"
        dtr.Item("Burial_date") = "1/28/2019"
        dtr.Item("Place_of_death") = ""
        ds.Tables("Death").Rows.Add(dtr)

        sqladaptor.InsertCommand = cb.GetInsertCommand()
        sqladaptor.Update(ds.Tables("Burials"))

        sqlconn1.Close()
        sqlconn1.Dispose()
        sqladaptor.Dispose()

    End Sub

End Module

What I have tried:

I have tried to section off each piece of code to make it easier to handle errors.