SqlDataAdapter not picking up SQL errors.

[Manch]

I have a SqlDataAdapter that is not picking up Sql exceptions from the SQL Server database such as foreign key constrain violations. When I call the update method of the dataadapter for either inserts or updates, it just fails silently. If I do the update directly from the SQL table the errors are being generated by the server. The ContinueUpdateOnError property for my adapter is set to false. The adapter was picking up SQL exceptions just fine earlier, so I must have changed something by accident. I have another adapter in my project that is picking up exceptions just fine, so it is not of application wide scope.

Does anyone have any idea what might be causing this behavior? Any help would be greatly appreciated.

 

[woyler]

what do you have in the catch statement of the call to the SQL server?

 

[Manch]

I was using a nested try catch block as follows.

Try
Catch ccEx As Data.DBConcurrencyException
<Code>
Catch sqlEx As SqlException
<Code>
Catch ex As Exception
<Code>
End Try

It was working fine and I am using the same scheme in other forms and it is working there. I removed this try catch from this particular update procedure to see if it was causing the problem. Since I am not getting an unhandled exception back from the server, it does not appear that it was the problem.

Here is my update code

<Code>
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
'There are changes that need to be made, so attempt to update the datasource by
'calling the update method and passing the dataset and any parameters.
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
</Code>

Thanks for looking at this.

 

[woyler]

When it was there, did it fall into any of the catch blocks?

 

[Manch]

I just realized what is causing it. I am using an InsteadOf Trigger in SQL Server for Inserts on this table. The Trigger checks for a condition and then either Raises and error or completes the Insert using the Inserted table. It seems that the .Net does not catch any sql errors that occur on statements executed in a trigger. Here is my trigger.

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 Inserted

Sorry, I feel stupid that I did not catch this right off. I guess I will have to use @@ERROR in the stored procedure that fires the trigger and then use a return value to send error info. If you know of a way to trap sql errors in vb.net from a statement in a trigger please let me know.

Manch