Updating a Relational Table ADO.Net

[Qamgine]

I am trying to update two tables. The first is the Date table which has the actual date and the second is the Batch table which has a link to the Date table through a foreign key. Here is the definition.

DOS Table
'---------
DOSID ' Primary Key
DateString 'Actual Date

Batch Table
'----------
BatchID 'Primary Key
DateID 'Foreign Key to Date Table
BatchName 'String Value

I have a relation setup as follows:

dsDocuData.Relations.Add("BatchDOS", dsDocuData.Tables("DOS").Columns("DOSID"), dsDocuData.Tables("Batch").Columns("DOSID"))

I can create a new record in Date Table with the following Code:

        Public Function AddNew(ByVal vDate As Date) As Long
            Try
                '<<Add New DOS Record>>
                '========================
                'Create a blank row to add values.
                Dim dt_NewRow As DataRow = dsDocuData.Tables("DOS").NewRow

                '\\DOS
                dt_NewRow("DateString") = vDate

                'Add New Row to Table
                '--------------------
                dsDocuData.Tables("DOS").Rows.Add(dt_NewRow)

                'Update Recordset
                '----------------
                daDOS.Update(dsDocuData, "DOS")
                daDOS.AcceptChanges()

                Return dt_GetIdentity.ExecuteScalar.ToString

            Catch ex As Exception
                Throw New Exception("Unable to add new record." & vbCrLf & ex.Message, ex)
            End Try
        End Function

This works fine and I am returned the new DOS ID that is auto generated with the Execute.Scalar command.

I then try to create a new Batch record and use the new Date ID that I just entered with the following code.

Public Function AddNew(ByVal vFacilityID As Long, ByVal vDOSID As Long, ByVal vNumScanned As Integer, ByVal vStoreDirectory As String) As Long
            Try
                '<<Add New Batch Record>>
                '========================
                'Create a blank row to add values.
                Dim bt_NewRow As DataRow = dsDocuData.Tables("Batch").NewRow
                '\\FacilityID
                bt_NewRow("FacilityID") = vFacilityID
                '\\DOSID
                bt_NewRow("DOSID") = vDOSID
                '\\NumScanned
                bt_NewRow("NumScanned") = vNumScanned
                '\\StoreDirectory
                bt_NewRow("StoreDirectory") = vStoreDirectory

                'Add New Row to Table
                '--------------------
                [b]dsDocuData.Tables("Batch").Rows.Add(bt_NewRow)[/b]

                'Update Recordset
                '----------------
                daBatch.Update(dsDocuData, "Batch")
                dsDocuData.AcceptChanges()

                Return bt_GetIdentity.ExecuteScalar.ToString
            Catch ex As Exception
                Throw New Exception("Unable to add new record." & vbCrLf & ex.Message, ex)
            End Try
        End Function

I keep getting an error message
"ForeignKeyConstraint BatchDOS requires the child key values (1) to exist in the parent table."
Where (1) is the DOSID of the new Date entered. This error occurs at the following line:

dsDocuData.Tables("Batch").Rows.Add(bt_NewRow) highlighted above.

It seems as though the DOS table isn't updating, but I ran a query on the table and I can see the new date there. Do I need to update my relationships? I appreciate any help you can provide on this.

Thank you

 

[ralphtrent]

Most likly its what you said. The relationship is not aware of the change, maybe writing an SP to do the updates then rebuilding the relationship would help.

 

[Qamgine]

Thanks Ralph. Define what you mean by a SP please.

Thank you again.