RandomIntellect 0 Newbie Poster

Hi, I've got two tables ina database which I'm trying to update at the same time. I'm using the code below to update:

Conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=KingfisherDB.mdb;")
        Conn.Open()

        'Define data adaptor and fill data sets
        DA = New OleDbDataAdapter
        DS = New DataSet
        Cmd = New OleDbCommand
        Cmd.Connection = Conn
        DA.SelectCommand = Cmd
        Tables(0) = "tblPersonal"
        Tables(1) = "tblStaff"
        
        'Fill data set tables
        Dim Count As Integer
        For Count = 0 To Tables.GetUpperBound(0)
            DA.SelectCommand.CommandText = "SELECT * FROM " & Tables(Count)
            DA.Fill(DS, Tables(Count))
        Next

        'Map default names to actual names
        DA.TableMappings.Add("Table", "tblPersonal")
        DA.TableMappings.Add("Table1", "tblStaff")
        
        'Create table names to clarify code
        tblPersonal = DS.Tables(0)
        tblStaff = DS.Tables(1)
        
        'Create database relationships for the dataset
        DS.Relations.Add(New DataRelation("PersonalStaff", tblPersonal.Columns("PersonalID"), tblStaff.Columns("PersonalID"), False))
        
        'To get number of staff
        NoStaff = tblStaff.Rows.Count

        'Create Update commands for each table

        'Personal table update
        PersonalUpdateCmd = New OleDbCommand


        PersonalUpdateCmd.CommandText = "UPDATE tblPersonal SET Title = @Title, ForeNames = @ForeNames, Surname = @Surname," & _
            "Gender = @Gender WHERE PersonalID = @PersonalID"
        PersonalUpdateCmd.Connection = Conn
        PersonalUpdateCmd.Parameters.Add("@Title", OleDbType.VarChar, 30, "Title")
        PersonalUpdateCmd.Parameters.Add("@ForeNames", OleDbType.VarChar, 60, "ForeNames")
        PersonalUpdateCmd.Parameters.Add("@Surname", OleDbType.VarChar, 60, "Surname")
        PersonalUpdateCmd.Parameters.Add("@Gender", OleDbType.VarChar, 20, "Gender")
        
        PersonalKeyParam = New OleDbParameter

        PersonalKeyParam = PersonalUpdateCmd.Parameters.Add("@PersonalID", OleDbType.Integer)
        PersonalKeyParam.SourceColumn = "PersonalID"
        PersonalKeyParam.SourceVersion = DataRowVersion.Original

        'Staff table Update
        StaffUpdateCmd = New OleDbCommand

        StaffUpdateCmd.CommandText = "UPDATE tblStaff SET Pos = @Pos, Address = @Address, Postcode = @Postcode, HomeNo = @HomeNo PersonalID = @PersonalID WHERE StaffID = @StaffID"
        StaffUpdateCmd.Connection = Conn
        StaffUpdateCmd.Parameters.Add("@Pos", OleDbType.VarChar, 60, "Pos")
        StaffUpdateCmd.Parameters.Add("@Address", OleDbType.VarChar, 120, "Address")
        StaffUpdateCmd.Parameters.Add("@Postcode", OleDbType.VarChar, 10, "Postcode")
        StaffUpdateCmd.Parameters.Add("@HomeNo", OleDbType.VarChar, 11, "HomeNo")
        
        StaffKeyParam = New OleDbParameter

        StaffKeyParam = StaffUpdateCmd.Parameters.Add("@StaffID", OleDbType.Integer)
        StaffKeyParam.SourceColumn = "StaffID"
        StaffKeyParam.SourceVersion = DataRowVersion.Original

        SForeignKeyParam = New OleDbParameter

        SForeignKeyParam = StaffUpdateCmd.Parameters.Add("@PersonalID", OleDbType.Integer)
        SForeignKeyParam.SourceColumn = "PersonalID"
        SForeignKeyParam.SourceVersion = DataRowVersion.Original

Conn.Close()

I then open a form and enter details to change the data set:

Dim StaffRow As DataRow
Dim DRStaff() As DataRow

        StaffRow = tblPersonal.Rows(CurrentStaff)
        DRStaff = StaffRow.GetChildRows("PersonalStaff")

        DRStaff(0).Item("Pos") = txtJobTitle.Text
        StaffRow.Item("ForeNames") = txtForeNames.Text
        StaffRow.Item("Surname") = txtSurname.Text
        StaffRow.Item("Title") = txtTitle.Text
        StaffRow.Item("Gender") = txtGender.Text
        DRStaff(0).Item("HomeNo") = txtHomeNo.Text
        DRStaff(0).Item("Address") = txtAddress.Text
        DRStaff(0).Item("Postcode") = txtPostcode.Text

        Conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=KingfisherDB.mdb;")
        Conn.Open()

        DA.UpdateCommand = PersonalUpdateCmd
        DA.Update(DS)

        DA.UpdateCommand = StaffUpdateCmd
        DA.Update(DS)

        Conn.Close()

The second update command doesn't do anything, it doesn't give any errors, it just doesn't update. When I update just the parent table it works but when I try to update just the child table it says parameter @Pos has no default value. I have no idea what's going on!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.