Help with Relational Error I'm getting please?

[Denaes]

Error: 
System.Data.OleDb.OleDbException: You cannot add or change a record because a related record is required in table 'Schedule'.
  at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
  at SchedulerWorking.modProcedures.CheckSave(TextBox textbox) in C:\Documents and Settings\MiniSlink\Desktop\Schedule Program\SchedulerWorking\modProcedures.vb:line 72

 

Thats the error I'm getting by trapping the dataAdaptor.Update method with a Try...Catch statement.

 

Now, I understand why its doing it.

 

I have a relationship in the database between the two tables. The related column is the "ScheduleID".

 

Now, I have a schedule with an ID of 0. I also set the ScheduleID on this table to 0 - the relation.

 

I get the error about it being in a relationship, even though I made a valid choice.

 

When I use Access, I can just type in all the fields and the number 0 for the relationship and it works just dandy.

 

I'm wondering what I should do to get around this?

 

Should I not have relationships IN the database itself, but create the relationships as I use them by using JOINs in my SQL Select statements?

 

Or is there another easier way to do this?

[quwiltw]

Can you post your update code?

[Denaes]

Private Sub dadScheduleSetup()
       'Delete SQL Statment
       Const STR_SQL_Schedule_DELETE_ID As String = "DELETE FROM Schedule WHERE ScheduleID=?"
       'Select SQL Statement
       Const STR_SQL_Schedule_SELECT_ALL As String = "SELECT * FROM Schedule"
       'Insert SQL Statement
       Const STR_SQL_Schedule_INSERT As String = "INSERT INTO Schedule " & _
           "(ScheduleID, AreaID, CustomerID, AppUserCreate, AppUserUpdate, [Date], StartTime, " & _
           "EndTime, ActualStartTime, ActualEndTime, Price, AmountPaid, Notes) " & _
           "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
       'Update SQL Statement
       Const STR_SQL_Schedule_UPDATE As String = "UPDATE Schedule SET " & _
           "AreaID = ?, CustomerID = ?, AppUserCreate = ?, AppUserUpdate = ?, " & _
           "[Date] = ?, StartTime = ?, EndTime = ?, ActualStartTime = ?, ActualEndTime = ?, " & _
           "Price = ?, AmountPaid = ?, Notes = ? WHERE (ScheduleID = ?)"

       'Schedule DataAdapter Commands
       Dim cmmScheduleSelect As New OleDbCommand(STR_SQL_Schedule_SELECT_ALL, cnnData)
       Dim cmmScheduleDelete As New OleDbCommand(STR_SQL_Schedule_DELETE_ID, cnnData)
       Dim cmmScheduleInsert As New OleDbCommand(STR_SQL_Schedule_INSERT, cnnData)
       Dim cmmScheduleUpdate As New OleDbCommand(STR_SQL_Schedule_UPDATE, cnnData)
       Dim prmSQLDeleteSchedule, prmSQLInsertSchedule, prmSQLUpdateSchedule As OleDbParameter

       'Initialize and instantiate Data Adapter
       dadSchedule = New OleDbDataAdapter(STR_SQL_Schedule_SELECT_ALL, cnnData)

       'Set data adapter command properties
       With dadSchedule
           .SelectCommand = cmmScheduleSelect
           .DeleteCommand = cmmScheduleDelete
           .UpdateCommand = cmmScheduleUpdate
           .InsertCommand = cmmScheduleInsert
       End With

       'Add Delete command paremeters
       prmSQLDeleteSchedule = dadSchedule.DeleteCommand.Parameters.Add( _
           "@ScheduleID", OleDbType.Integer, Nothing, "ScheduleID")
       prmSQLDeleteSchedule.Direction = ParameterDirection.Input
       prmSQLDeleteSchedule.SourceVersion = DataRowVersion.Original

       'Add Update command paremeters
       With cmmScheduleUpdate.Parameters
           .Add("@AreaID", OleDbType.Integer, Nothing, "AreaID")
           .Add("@CustomerID", OleDbType.Integer, Nothing, "CustomerID")
           .Add("@AppUserCreate", OleDbType.VarChar, 25, "AppUserCreate")
           .Add("@AppUserUpdate", OleDbType.VarChar, 25, "AppUserUpdate")
           .Add("@Date", OleDbType.VarChar, 10, "Date")
           .Add("@StartTime", OleDbType.VarChar, 4, "StartTime")
           .Add("@EndTime", OleDbType.VarChar, 4, "EndTime")
           .Add("@ActualStartTime", OleDbType.VarChar, 4, "ActualStartTime")
           .Add("@ActualEndTime", OleDbType.VarChar, 4, "ActualEndTime")
           .Add("@Price", OleDbType.VarChar, 10, "Price")
           .Add("@AmountPaid", OleDbType.VarChar, 10, "AmountPaid")
           .Add("@Notes", OleDbType.VarChar, 250, "Notes")
       End With
       prmSQLUpdateSchedule = dadSchedule.UpdateCommand.Parameters.Add( _
           "@ScheduleID", OleDbType.Integer, Nothing, "ScheduleID")
       prmSQLUpdateSchedule.Direction = ParameterDirection.Input
       prmSQLUpdateSchedule.SourceVersion = DataRowVersion.Original

       'Add Insert command paremeters
       With cmmScheduleInsert.Parameters
           .Add("@ScheduleID", OleDbType.Integer, Nothing, "ScheduleID")
           .Add("@AreaID", OleDbType.Integer, Nothing, "AreaID")
           .Add("@CustomerID", OleDbType.Integer, Nothing, "CustomerID")
           .Add("@AppUserCreate", OleDbType.VarChar, 25, "AppUserCreate")
           .Add("@AppUserUpdate", OleDbType.VarChar, 25, "AppUserUpdate")
           .Add("@Date", OleDbType.VarChar, 10, "Date")
           .Add("@StartTime", OleDbType.VarChar, 4, "StartTime")
           .Add("@EndTime", OleDbType.VarChar, 4, "EndTime")
           .Add("@ActualStartTime", OleDbType.VarChar, 4, "ActualStartTime")
           .Add("@ActualEndTime", OleDbType.VarChar, 4, "ActualEndTime")
           .Add("@Price", OleDbType.VarChar, 10, "Price")
           .Add("@AmountPaid", OleDbType.VarChar, 10, "AmountPaid")
           .Add("@Notes", OleDbType.VarChar, 250, "Notes")
       End With
   End Sub

 

This is the procedure I use to setup the data adapter.

 

dadSchedule.Update is what I use to call the update/insert/delete

 

The problem I have is when I try to update/insert a record on the table from a datagrid. Delete works fine.

 

I basically have one of these set up for each data adaptor in the database (about 9 right now). Each table has its own dataAdaptor, which (for me) confuses the relationship issue a little.

 

I'd really appreciate any help.

[quwiltw]

can you post CheckSave()?

[Denaes]

   Public Sub CheckSave(ByVal textbox As TextBox)
       MsgBox("Initiating Save")
       If dstDatabase.HasChanges Then
           Dim dstChanges As New DataSet
           Try
               dstChanges = dstDatabase.GetChanges
           Catch ex As Exception
               textbox.Text = "Error: " & vbCrLf & ex.ToString
           End Try

           If dstChanges.HasErrors Then
               dstChanges.RejectChanges()
           Else
               Try
                   dadAppUser.Update(dstDatabase, "AppUser")
                   dadArea.Update(dstDatabase, "Area")
                   dadCustomers.Update(dstDatabase, "Customers")
                   dadCustomerStatus.Update(dstDatabase, "CustomerStatus")
                   dadCustomerType.Update(dstDatabase, "CustomerType")
                   dadInstructors.Update(dstDatabase, "Instructors")
                   dadSchedule.Update(dstDatabase, "Schedule")
                   dadScheduleToInstructors.Update(dstDatabase, "ScheduleToInstructors")
                   dadtblState.Update(dstDatabase, "tblState")
                   dadUserLevel.Update(dstDatabase, "UserLevel")
               Catch ex As Exception
                   textbox.Text = "Error: " & vbCrLf & ex.ToString
               End Try
           End If
           dstChanges.Dispose()
       End If
   End Sub

 

Its basically just checking for changes, if there are changes, moves them to another dataset, then processes the changes back to the database through the dataAdapters.

 

I did the passing of the text box so the error would appear in a textbox if there was an error, to make it easier to troubleshoot.

 

Really, all but two tables delete, update and insert perfectly. Its just this table and another table with a relationship that have this error.

[Denaes]

Anyone?

 

Anyone have any ideas or knowledge of relational database updating?

 

Should I have relationships in my typed dataset as well?

 

Should I be querrying and updating the relationship rather than individual tables?

 

Updating/Inserting the child table is just fine. I just have troubles with the parent table. I'll choose a valid id value that exists on the related child table and I get the above error in my first post.

 

Anyone have a sample program, tutorial or knowledge to help out? Please?

[Denaes]

If anyone cares, I think I found the problem.

 

I was doing a batch update to the database - but not refreshing the dataset. What the dataset would assign as an ID of 4 the database would give a 3 - which would get in the way of the relationship.

 

I changed the updating procedure from one general one which updated them all at once, to one that passed a datatable into the procedure, updates the database with only that table and then refreshes the dataset which was updated.