I have a .Net 2003 WinForms application. I have a form that uses a SQLDataAdapter along with a Stored procedure to make inserts to a table in my SQL Server 2000 database. The table has an Instead Of Trigger for Inserts that checks a business rule and either raises an error or makes the insert using the data in the Inserted table.
When the error is raised in the Trigger, it is not picked up by the SQlDataAdapter. My application procedes like the insert was made, but in actuality the server raised the error and the insert is not made. I also tried placing data in the insert that would pass the business rule but would cause a foreign key constraint violation. In this case the Trigger procedes with the insert using the data in the Inserted table but the insert fails and a foreign key contraint error is raised, but again my DataAdapter does not pick up this error and my application procedes as if the insert was made, even though it was not.
Does anyone know of a way to configure the DataAdapter so it will pick up errors either raiesed in a trigger or generated during the execution of a SQL statement in a trigger? The trigger is working fine and I have verified this by entering data directly into my SQL Table, in which case the errors are generated and displayed. Any help would be greatly appreciated. Here is my trigger code:
All parameters and other properties of the DataAdapter have been set in desing mode. I removed the try-catch block that was trapping for a SQLException to see if that was the problem. Even without the try-catch no unhandled errors occur. Here is my update code:
When the error is raised in the Trigger, it is not picked up by the SQlDataAdapter. My application procedes like the insert was made, but in actuality the server raised the error and the insert is not made. I also tried placing data in the insert that would pass the business rule but would cause a foreign key constraint violation. In this case the Trigger procedes with the insert using the data in the Inserted table but the insert fails and a foreign key contraint error is raised, but again my DataAdapter does not pick up this error and my application procedes as if the insert was made, even though it was not.
Does anyone know of a way to configure the DataAdapter so it will pick up errors either raiesed in a trigger or generated during the execution of a SQL statement in a trigger? The trigger is working fine and I have verified this by entering data directly into my SQL Table, in which case the errors are generated and displayed. Any help would be greatly appreciated. Here is my trigger code:
Code:
ALTER Trigger trgHouseholdPrimaryCheck
ON tblHousehold
Instead of Insert
As
Declare @Relation varchar(20)
Declare @CaseId integer
Set @Relation = (Select Household_Relationship From Inserted)
Set @CaseId = (Select Household_CaseId From Inserted)
Select Household_Id From tblHousehold Where Household_CaseId = @CaseId And Household_Relationship
= 'Primary Client'
If @@Rowcount > 0 And @Relation = 'Primary Client'
Begin
RAISERROR(50001,16,1)
end
Else
INSERT INTO [CaseManagement].[dbo].[tblHousehold]([Household_CaseId], [Household_Active],
[Household_FirstName], [Household_LastName], [Household_MiddleName], [Household_SuffixName],
[Household_Relationship], [Household_HomePhone], [Household_WorkPhone], [Household_ContactPhone],
[Household_CellPhone], [Household_DateAdded], [Household_InactiveDate], [Household_MailStreetPo],
[Household_MailAptNum], [Household_MailCity], [Household_MailState], [Household_MailZip], [Household_SSN],
[Household_SecureRoom], [Household_FraudRisk], [Household_EmploymentTraining], [Household_WorkRestrictions],
[Household_WorkLimitations], [Household_MaritalStatus], [Household_DOB], [Household_School],
[Household_Grade], [Household_Felony], [Household_FelonyDetails], [Household_ParoleProbation],
[Household_ParoleProbationOfficer], [Household_ParoleProbationAddress], [Household_ParoleProbationCity],
[Household_ParoleProbationState], [Household_ParoleProbationZip], [Household_ParoleProbationDetails],
[Household_BranchofMilitary], [Household_RankAtDischarge], [Household_MilitaryStartDate],
[Household_MilitaryEndDate], [Household_DischargeType], [Household_Comments], [Household_UpdatedBy],
[Household_UpdatedOn], [Household_CreatedBy], [Household_CreatedOn])SELECT [Household_CaseId],
[Household_Active], [Household_FirstName], [Household_LastName], [Household_MiddleName],
[Household_SuffixName], [Household_Relationship], [Household_HomePhone], [Household_WorkPhone],
[Household_ContactPhone], [Household_CellPhone], [Household_DateAdded], [Household_InactiveDate],
[Household_MailStreetPo], [Household_MailAptNum], [Household_MailCity], [Household_MailState],
[Household_MailZip], [Household_SSN], [Household_SecureRoom], [Household_FraudRisk],
[Household_EmploymentTraining], [Household_WorkRestrictions], [Household_WorkLimitations],
[Household_MaritalStatus], [Household_DOB], [Household_School], [Household_Grade], [Household_Felony],
[Household_FelonyDetails], [Household_ParoleProbation], [Household_ParoleProbationOfficer],
[Household_ParoleProbationAddress], [Household_ParoleProbationCity], [Household_ParoleProbationState],
[Household_ParoleProbationZip], [Household_ParoleProbationDetails], [Household_BranchofMilitary],
[Household_RankAtDischarge], [Household_MilitaryStartDate], [Household_MilitaryEndDate],
[Household_DischargeType], [Household_Comments], [Household_UpdatedBy], [Household_UpdatedOn],
[Household_CreatedBy], [Household_CreatedOn] FROM InsertedAll parameters and other properties of the DataAdapter have been set in desing mode. I removed the try-catch block that was trapping for a SQLException to see if that was the problem. Even without the try-catch no unhandled errors occur. Here is my update code:
Code:
Private Sub UpdateDataSet()
Dim dtChangesHousehold As dsHouseholdView.tblHouseholdDataTable = New dsHouseholdView.tblHouseholdDataTable
Me.BindingContext(Me.DsHouseholdView1.tblHousehold).EndCurrentEdit()
dtChangesHousehold = CType(DsHouseholdView1.Tables("tblHousehold").GetChanges(DataRowState.Added Or DataRowState.Modified),dsHouseholdView.tblHouseholdDataTable)
If (Not (dtChangesHousehold) Is Nothing) Then
SqlDataAdapterHousehold.UpdateCommand.Connection = cnn
SqlDataAdapterHousehold.InsertCommand.Connection = cnn
SqlDataAdapterHousehold.Update(dtChangesHousehold)
DsHouseholdView1.Merge(dtChangesHousehold)
DsHouseholdView1.AcceptChanges()
MessageBox.Show("Your updates have been successfuly submitted to the database!", "Database Update Successful!")
Else
MessageBox.Show("There are no updates to the Case data to submit!")
Exit Sub
End If
End Sub