Run-time error (-2147217900(80040e14)

[previoustoolboxuser]

Advertisement

Close

Believe the above message looks familiar to some of you gurus. My problem is, I want to DELETE a record from a table(has only one column), upon the click of the DELETE button on my form, but keep geting the following error message. Run-time error (-2147217900(80040e14) [Microsoft][ODBC SQL Servr Driver][SQL Server]DELETE statement conflicted with COLUMN REFERENCE constraint ‘FK_AllowaCommissionAgencyTypes’. The conflict occured in database ‘Agencies’, table ‘tblAllowableCommissionItems’, coulmn ‘AgencyType’. I can’t trap the above error number and customize it, because it fails on the following line of code on the Delete procedure in my source code: rsDeleteRecord.Open strSQL, cnAgency, adOpenKeyset, adLockOptimistic For your infor. there is also another table that uses the same field I’m trying to Delete. In that table, the field is the primary key. Is there any way to trap this error message and customize it to say that the record cannot be deleted because the field is used in another table, or is there another way around it? Best regards, Elizabeth Dumu Business Solutions Section BSP Information Technology Department Phone: 3009 509 Fax: 3250 001 Email: Edumu@bsp.com.pg http://www.bsp.com.pg ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Subject to any responsibilities implied by law and which cannot be excluded, the Bank of South Pacific Limited (““BSP””) is not liable to you or to any third party to whom you forward this email or who seeks to rely on information contained in this email, for any loss, damages, liabilities, claims and expenses (including but not limited to legal costs and defense or settlement costs) whatsoever arising out of or referable to material contained in this email whether in contract or tort including negligence, statute or otherwise. BSP does not warrant the accuracy, adequacy, or completeness of materialcontained in this email (including but not limited to any attachments to this email) . ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[previoustoolboxuser]

Advertisement

Close

Hi Edgar, thank you very much for all the time you’ve taken to reply to my
query. I see that you have a heart to help anyone who is in need of it? I am
a trainee Development Programmer in VB and what you have shared has helped
me alot. What are you? I’d love to know, because your way of explaining
things is well contructed and to the point.

Advertisement

Close

Now, back to my problem, after discussion with my DBA, he thinks that if the
user really wants to delete the records from this particular table, he
first needs to delete other related records from other tables(where the
field is a Foreign Key), then delete this particular record. Otherwise, an
error is raised and the error number is customized to put out a meaningful
message, then returns to the form.

At first I told you that I could not be able to even trap the error and get
the error number, this was because, 1. I did not raise the error, 2. this
was because I had the “Break on all Errors” checked on the
Tools/Options/General instead of having the “Break on Unhandled Errors”
checked. Now the working code looks like the one below:

Private Sub cmdDeleteRecord_Click()
On Error GoTo ErrH

Me.MousePointer = vbHourglass

Call validateInput

Select Case intMessage
Case 0
If MsgBox(“Confirm to Delete Record.”, vbInformation + vbYesNo,
“Agency Application : Agency Type Deletion”) = vbYes Then
Call DeleteRecord
MsgBox "The Record has been successfully Deleted. ",
vbInformation + vbOKOnly, “Agency Application : Agency Type Parameters”
cmdRefreshScreen.Enabled = True
Call cmdRefreshScreen_Click
Else
Call cmdClearScreen_Click
cmdRefreshScreen.Enabled = False
End If

'Agency Type was not entered
Case 1
Beep
MsgBox “An Agency Type is required. Please Enter.”,
vbExclamation + vbOKOnly, “Agency Processing Application : Agency Type
Parameteres Maintenance Screen”
Me.MousePointer = vbDefault
cntlSearchOptions(0).SetFocus

'Agency Description was not entered
Case 2
Beep
MsgBox “An Agency Type Description is required. Please
Enter.”, vbExclamation + vbOKOnly, “Agency Processing Application : Agency
Type Maintenance Screen”
cntlSearchOptions(2).SetFocus
Case Else
End Select

cntlSearchOptions(0).SetFocus
Me.MousePointer = vbDefault
Exit Sub

ErrH:
Beep
Me.MousePointer = vbDefault
Select Case Err.Number
Case -2147217900
MsgBox “Cannot Delete Agency Type because related records exists
in the Database. Refer to Database Administrator.”, vbExclamation +
vbOKOnly, Err.Source
Case Else
MsgBox Err.Number & ", " & Err.Description
cntlSearchOptions(0).SetFocus
End Select
End Sub

'deletes record
Private Function DeleteRecord()
On Error GoTo ErrH
Dim strSQL As String

strSQL = "Delete From tblAgencyTypes " _
& “Where AgencyType = '” & strOldAgencyType & "’ "

'calls the GlobalDeleteRecord function
Call GlobalDeleteRecord(strSQL)
Exit Function

ErrH:
Err.Raise Err.Number, Err.Source, Err.Description
End Function

'deletes existing database record
Public Function GlobalDeleteRecord(strSQL As String)
On Error GoTo ErrMsg
Dim rsDeleteRecord As Recordset

Set rsDeleteRecord = New Recordset

rsDeleteRecord.CursorLocation = adUseClient
cnAgency.BeginTrans
rsDeleteRecord.Open strSQL, cnAgency, adOpenKeyset, adLockOptimistic
cnAgency.CommitTrans

Set rsDeleteRecord = Nothing
Exit Function

ErrMsg:
cnAgency.RollbackTrans
Err.Raise Err.Number, Err.Source, Err.Description
End Function

I tried running the Application, it runs fine, now throwing out the error
message.

Thank you so much for your help Edgar.

Cheers.

---

Elizabeth,

1. There is no active error handler in the GlobalDeleteRecord procedure.
2. The fact that you are using a Recordset.Open to execute the SQL may have
   a further bearing on the problem.

I would advise you to:

1. Enable the error-handler (uncomment the On Error GoTo ErrMsg) in the
   GlobalDeleteRecord procedure.
2. Change the code to use cnAgency.Execute strSQL at the minimum, if you do
   not want to create and use an ADODB.Command object (which is the preferred
   method used by most seasoned VB programmers)
3. Write more code in your error-handler to loop through cnAgency.Errors
   collection and print each error to the debug (Immediate) window. Like:

ErrMsg:
cnAgency.RollbackTrans
Dim i As Long

For i = 0 To cnAgency.Errors.Count - 1
Debug.Print cnAgency.Errors(i).Number, cnAgency.Errors(i).NativeError,
_
cnAgency.Errors(i).Description, cnAgency.Errors(i).Source
Next
'(other code omitted for clarity)

You can then copy the list of errors from the Immediate window and paste
them in a mail to the list. I suspect that the error number you are looking
for is not the actual error number returned by the ODBC provider for a
FOREIGN KEY violation, but this approach will confirm / disprove that.

Also, if you do not mind a little feedback:

1. It is good practice not to call event-handlers from the rest of your
   code:
   Call cmdRefreshScreen_Click
   Call cmdClearScreen_Click
   Instead, you could create private procedures in your form that do the work
   currently coded in these event-handlers, and call these procedures from the
   event-handler as well as the other places in your code.

2. In your Select Case, you are doing
   cntlSearchOptions(2).SetFocus
   but outside the Select, you are doing a
   cntlSearchOptions(0).SetFocus
   which will be executed always, thus your cntlSearchOptions(2) will never
   wind up with the focus, only the 0th element in that control array.

3. In DeleteRecord, you use a variable strOldAgencyType:
   strSQL = "Delete From tblAgencyTypes " _
   & “Where AgencyType = '” & strOldAgencyType & "’ "
   which is not shown in this code; I assume you are setting it correctly and
   will not bother asking that it contains correct data.

4. The BeginTrans and Rollback/CommitTrans are very healthy practice, IMHO,
   and I encourage the use of transactions, especially where you are doing
   several data updates which have to be committed as a unit. Good work!! In
   fairness, though, I must point out that fatal errors like this particular
   error you are getting, result in any implicit transaction being rolled back
   anyway, even if you do not have the xxxTrans statements in your VB code.
   Still, sooner or later, the benefits of adopting this approach will pay off,
   so I do not advise you to remove the transaction control statements.

[previoustoolboxuser]

CHECK FOREIGN KEY ON THAT TABLE

— Elizabeth Dumu via visualbasic-l

[previoustoolboxuser]

Elizabeth,

1. There is no active error handler in the GlobalDeleteRecord procedure.
2. The fact that you are using a Recordset.Open to execute the SQL may have
   a further bearing on the problem.

I would advise you to:

1. Enable the error-handler (uncomment the On Error GoTo ErrMsg) in the
   GlobalDeleteRecord procedure.
2. Change the code to use cnAgency.Execute strSQL at the minimum, if you do
   not want to create and use an ADODB.Command object (which is the preferred
   method used by most seasoned VB programmers)
3. Write more code in your error-handler to loop through cnAgency.Errors
   collection and print each error to the debug (Immediate) window. Like:

ErrMsg:
cnAgency.RollbackTrans
Dim i As Long

For i = 0 To cnAgency.Errors.Count - 1
Debug.Print cnAgency.Errors(i).Number, cnAgency.Errors(i).NativeError,
_
cnAgency.Errors(i).Description, cnAgency.Errors(i).Source
Next
'(other code omitted for clarity)

You can then copy the list of errors from the Immediate window and paste
them in a mail to the list. I suspect that the error number you are looking
for is not the actual error number returned by the ODBC provider for a
FOREIGN KEY violation, but this approach will confirm / disprove that.

Also, if you do not mind a little feedback:

1. It is good practice not to call event-handlers from the rest of your
   code:
   Call cmdRefreshScreen_Click
   Call cmdClearScreen_Click
   Instead, you could create private procedures in your form that do the work
   currently coded in these event-handlers, and call these procedures from the
   event-handler as well as the other places in your code.

2. In your Select Case, you are doing
   cntlSearchOptions(2).SetFocus
   but outside the Select, you are doing a
   cntlSearchOptions(0).SetFocus
   which will be executed always, thus your cntlSearchOptions(2) will never
   wind up with the focus, only the 0th element in that control array.

3. In DeleteRecord, you use a variable strOldAgencyType:
   strSQL = "Delete From tblAgencyTypes " _
   & “Where AgencyType = '” & strOldAgencyType & "’ "
   which is not shown in this code; I assume you are setting it correctly and
   will not bother asking that it contains correct data.

4. The BeginTrans and Rollback/CommitTrans are very healthy practice, IMHO,
   and I encourage the use of transactions, especially where you are doing
   several data updates which have to be committed as a unit. Good work!! In
   fairness, though, I must point out that fatal errors like this particular
   error you are getting, result in any implicit transaction being rolled back
   anyway, even if you do not have the xxxTrans statements in your VB code.
   Still, sooner or later, the benefits of adopting this approach will pay off,
   so I do not advise you to remove the transaction control statements.

Regards,
Edgar.

[previoustoolboxuser]

1: Do not open a resultset when you are using an action query (INSERT,
UPDATE, DELETE), use the .Execute() method of either a command object or the
connection instead.

2: The error seems to indicate that the table is used as a “parent” in a
referential integrity relationship - i.e. another table has a foreign key
referencing the one you are deleting from. In this case, you must first
delete the related rows (“children”).

HTH,
Tore.

[previoustoolboxuser]

Hi Edgar, please refer following code as per your request:

Private Sub cmdDeleteRecord_Click()
On Error GoTo ErrH

Me.MousePointer = vbHourglass

Call validateInput

Select Case intMessage
Case 0
If MsgBox(“Confirm to Delete Record.”, vbInformation +
vbYesNo, “Agency Application : Agency Type Deletion”) = vbYes Then
Call DeleteRecord
MsgBox "The Record has been successfully Deleted. ",
vbInformation + vbOKOnly, “Agency Application : Agency Type Parameters”
cmdRefreshScreen.Enabled = True
Call cmdRefreshScreen_Click
Else
Call cmdClearScreen_Click
cmdRefreshScreen.Enabled = False
End If

'Agency Type was not entered
Case 1
Beep
MsgBox “An Agency Type is required. Please Enter.”,
vbExclamation + vbOKOnly, “Agency Processing Application : Agency Type
Parameteres Maintenance Screen”
Me.MousePointer = vbDefault
cntlSearchOptions(0).SetFocus

'Agency Description was not entered
Case 2
Beep
MsgBox “An Agency Type Description is required. Please
Enter.”, vbExclamation + vbOKOnly, “Agency Processing Application : Agency
Type Maintenance Screen”
cntlSearchOptions(2).SetFocus
Case Else
End Select

cntlSearchOptions(0).SetFocus
Me.MousePointer = vbDefault
Exit Sub

ErrH:
Me.MousePointer = vbDefault
MsgBox Err.Description & ", " & Err.Number
End Sub

‘deletes record
Private Function DeleteRecord()
On Error GoTo ErrH
Dim strSQL As String
On Error GoTo ErrH
strSQL = "Delete From tblAgencyTypes " _
& “Where AgencyType = '” & strOldAgencyType & "’ "

'calls the GlobalDeleteRecord function
Call GlobalDeleteRecord(strSQL)

Exit Function

ErrH:
Me.MousePointer = vbDefault
MsgBox Err.Description & ", " & Err.Number
End Function

'deletes existing database record
Public Function GlobalDeleteRecord(strSQL As String)
'On Error GoTo ErrMsg
Dim rsDeleteRecord As Recordset

Set rsDeleteRecord = New Recordset

rsDeleteRecord.CursorLocation = adUseClient
cnAgency.BeginTrans
’ On Error GoTo ErrMsg
rsDeleteRecord.Open strSQL, cnAgency, adOpenKeyset, adLockOptimistic
'On Error GoTo ErrMsg

cnAgency.CommitTrans

Set rsDeleteRecord = Nothing
Exit Function

ErrMsg:
cnAgency.RollbackTrans
If Err.Number = -2147217900 Then
MsgBox “Cannot Delete Record. There is related Data in another
table.”, vbExclamation + vbOKOnly, “Agency Processing Application : Agency
Type Maintenance Screen”
Else
MsgBox Err.Description & ", " & Err.Number
End If
’ Err.Raise Err.Number, Err.Source, Err.Description
End Function

Best regards,

Elizabeth Dumu
Business Solutions Section
BSP Information Technology Department

Phone: 3009 509
Fax: 3250 001
Email: Edumu@bsp.com.pg

http://www.bsp.com.pg

Subject to any responsibilities implied by law and which cannot be excluded, the Bank of South Pacific Limited (“BSP”) is not liable to you or to any third party to whom you forward this email or who seeks to rely on information contained in this email, for any loss, damages, liabilities, claims and expenses (including but not limited to legal costs and defense or settlement costs) whatsoever arising out of or referable to material contained in this email whether in contract or tort including negligence, statute or otherwise.
BSP does not warrant the accuracy, adequacy, or completeness of materialcontained in this email
(including but not limited to any attachments to this email) .

[previoustoolboxuser]

Hmmmm…?? That’s funny… Let’s see the code in your Delete button event
handler (post the entire thing, with the existing error handlers and all…)

Regards,
Edgar.