Can't save new row to table
I'm trying to save a new row using the following code: It's a schema adapted
almost exactly from one given me by Kevin Yu some time ago. One difference
is the inclusion of the ID field. If I don't include it I get an error
message. I also have an addhandler onCAIrowupdated. The msgbox in that
gives the right answer for the table's ID = 0, since it would be the first
entry.
However, right after the msgbox I get an error.
Message: You cannot add or change a record because a related record is
required in table 'PointsOfView'. The line number indicated is
intModified = daCAI.Update(tblCAI)
Of the six fields in ComceptAssignmentInstances, four have 1 to many
relationships with with table: TopicID, PointOfVew, ReportType, and
ConceptID. This is an Access database. I carefully examined the
relationship table, and PointOfView is identical with all the others.
So I put in the following message boxes
Both showed values 0, because I hadn't changed them, and the error message
showed. The second time I changed the comboxes selected items and the save
was successful.
Two problems. The first is the known but of having to first move the
selected value of of comboboxes to 1 and then back to 0. But where and how
can I do it once I click the save button?
The second problem is that the ID value in the saved table was -1. Since ID
is an autoincrementing long integer, why didn't it have value 0?
thanks,
dennist685
Private Sub btnSaveC1_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSaveC1.Click
Try
daCAI.Fill(DsCAI1, "ConceptAssignmentInstances")
Dim tblCAI As New dsCAI.ConceptAssignmentInstancesDataTable
Dim rowCAI As dsCAI.ConceptAssignmentInstancesRow
rowCAI = tblCAI.NewConceptAssignmentInstancesRow
rowCAI.ID = -1
rowCAI.TopicID = iTopicID
rowCAI.BeginText = txtConceptText.SelectionStart
rowCAI.ConceptID = lstConcepts1.SelectedValue
rowCAI.ReportType = Me.cboRptTypeCncpt.SelectedValue
rowCAI.PointOfView = Me.cboPOVCncpt.SelectedValue
'rowCAI.ReportType = Me.cboRptTypeCncpt.SelectedValue
MsgBox("report type " & rowCAI.ReportType.ToString)
MsgBox("point of view " & rowCAI.PointOfView.ToString)
tblCAI.AddConceptAssignmentInstancesRow(rowCAI)
daCAI.InsertCommand = New OleDb.OleDbCommand("INSERT INTO
ConceptAssignmentInstances(ID, TopicID,
BeginText,ConceptID,ReportType,PointOfView) values (?,?,?,?,?,?)", cnCAI)
'daCAI.InsertCommand = New OleDb.OleDbCommand("INSERT INTO
ConceptAssignmentInstances( TopicID,
BeginText,ConceptID,ReportType,PointOfView) values (?,?,?,?,?)", cnCAI)
daCAI.InsertCommand.Parameters.Add("@ID",
OleDb.OleDbType.Integer, 4, "ID")
daCAI.InsertCommand.Parameters.Add("@TopicID",
OleDb.OleDbType.Integer, 4, "TopicID")
daCAI.InsertCommand.Parameters.Add("@BeginText",
OleDb.OleDbType.Integer, 4, "BeginText")
daCAI.InsertCommand.Parameters.Add("@ConceptID",
OleDb.OleDbType.Integer, 4, "ConceptID")
daCAI.InsertCommand.Parameters.Add("@ReportType",
OleDb.OleDbType.Integer, 4, "ReportType")
daCAI.InsertCommand.Parameters.Add("@PointOfView",
OleDb.OleDbType.Integer, 4, "PointOfView")
AddHandler daCAI.RowUpdated, AddressOf OnCAIRowUpDated
Dim intModified As Integer
intModified = daCAI.Update(tblCAI)
Dim sOutput As String
sOutput = "Modified " & intModified & "
ConceptAssignmentInstances"
Dim s As String = ControlChars.CrLf
Catch ex As OleDbException
If ex.Errors(0).SQLState = "3022" Then
Else
Dim errorMessages As String
errorMessages += "Message: " & ex.Errors(0).Message &
ControlChars.CrLf _
& "NativeError: " & ex.Errors(0).NativeError &
ControlChars.CrLf _
& "Source: " & ex.Errors(0).Source &
ControlChars.CrLf _
& "SQLState: " & ex.Errors(0).SQLState &
ControlChars.CrLf _
& "StackTrace: " & ex.stacktrace & "The form
will be closed"
MsgBox(errorMessages)
Me.Close()
End If
Catch ex As DBConcurrencyException
MessageBox.Show(ex.Message & "StackTrace: " & ex.stacktrace,
"Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error)
Me.Close()
Catch ex As Exception
MessageBox.Show(ex.Message & "StackTrace: " & ex.stacktrace,
"Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error)
Me.Close()
End Try
Me.cnCAI.Close()
End Sub