I have one table which i update from 2 different subs having the same command syntex with different values. With first sub the update works fine but with 2nd sub the update changes the table but give an error.[
Error:Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints]
After googling it and exhausting all online resources, read thru some solutions i am still having the problem. Though I think deleting and recreating dataadapter and dataset would not make a difference, I have deleted and recreated both 3 times. Here is the code and please point out my "foolishness".
I have made it working for me without error (without knowing the side effects of my act) by setting Enforce Constraints property of dataset to False. Please advise me
first sub (this update works fine)
Code:
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand
Try
conScheduler.Open()
With cmd
.Connection = conScheduler
.CommandType = CommandType.Text
.CommandText = "UPDATE tblcrew SET Active = '" & "Yes" & "' " & " WHERE firstName ='" & txtFname.Text & "' AND lastname ='" & txtLName.Text & "'"
.ExecuteNonQuery()
.CommandText = "UPDATE tblcrew SET StartDate = '" & "0" & "' " & " WHERE firstName ='" & txtFname.Text & "' AND lastname ='" & txtLName.Text & "'"
.ExecuteNonQuery()
.CommandText = "UPDATE tblcrew SET EndDate = '" & "0" & "' " & " WHERE firstName ='" & txtFname.Text & "' AND lastname ='" & txtLName.Text & "'"
.ExecuteNonQuery()
EndWith
Catch ex As Exception
MessageBox.Show(ex.Message, ex.GetType.ToString)
ExitSub
EndTry
conScheduler.Close()
2nd sub (this gives error)
Code:
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand
Try
conScheduler.Open()
With cmd
.Connection = conScheduler
.CommandType = CommandType.Text
.CommandText = "UPDATE tblcrew SET Active = '" & "NO" & "' " & " WHERE crewID ='" & txtID.Text & "'"
.ExecuteNonQuery()
.CommandText = "UPDATE tblcrew SET StartDate = '" & "mydate1" & "' " & "WHERE crewID ='" & txtID.Text & "'"
.ExecuteNonQuery()
.CommandText = "UPDATE tblcrew SET EndDate = '" & "mydate3" & "' " & "WHERE crewID ='" & txtID.Text & "'"
.ExecuteNonQuery()
EndWith
Catch ex As Exception
MessageBox.Show(ex.Message, ex.GetType.ToString)
ExitSub
EndTry
conScheduler.Close()
Error when updating table
