VB & Database, Checking if a delete will succeed (without doing the delete)

Checking if a delete will succeed (without doing the delete)

Author	Message
Graham Morri #1 / 6	Checking if a delete will succeed (without doing the delete) How (in ADO) can I check if deletion of a row (in SQL Server 2000) is likely to succeed without doing the delete and seeing if it worked? The tables have a variety of foreign key constraints and I would like to show the user what he is allowed to delete so he can make a choice. Doing the delete and rolling back the transaction seems a bit extreme, as does running sp_depends and trying to analyze the output.
Tue, 18 Nov 2003 00:11:43 GMT

DaveSat #2 / 6	Checking if a delete will succeed (without doing the delete) If you have a low volume application/database, doing the delete and trapping the error (error code 547). For example, if the user is maintaining reference data used for picklists. for a perfect user interface - you would need to look for each row in all the tables that reference it and then only enable the delete button if there are no references. -- HTH, David Satz Principal Software Engineer Hyperion Solutions ->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS (Please reply to group only - emails answered rarely) ----------------------------------------------------------------- Quote: > How (in ADO) can I check if deletion of a row (in SQL Server 2000) is likely > to succeed without doing the delete and seeing if it worked? > The tables have a variety of foreign key constraints and I would like to > show the user what he is allowed to delete so he can make a choice. > Doing the delete and rolling back the transaction seems a bit extreme, as > does running sp_depends and trying to analyze the output.
Tue, 18 Nov 2003 00:36:30 GMT

Mary Chipma #3 / 6	Checking if a delete will succeed (without doing the delete) Do a SELECT first, display the rows, then prompt for a delete. You can use the NextRecordset method of an ADO recordset object if there's more than one result set involved. -- Mary Microsoft Access Developer's Guide to SQL Server http://www.amazon.com/exec/obidos/ASIN/0672319446 On Thu, 31 May 2001 17:11:43 +0100, "Graham Morris" Quote: >How (in ADO) can I check if deletion of a row (in SQL Server 2000) is likely >to succeed without doing the delete and seeing if it worked? >The tables have a variety of foreign key constraints and I would like to >show the user what he is allowed to delete so he can make a choice. >Doing the delete and rolling back the transaction seems a bit extreme, as >does running sp_depends and trying to analyze the output.
Tue, 18 Nov 2003 01:16:47 GMT

Gert-Jan Stri #4 / 6	Checking if a delete will succeed (without doing the delete) Quote: > Doing the delete and rolling back the transaction seems a bit extreme, as > does running sp_depends and trying to analyze the output. That is exactly what I would do. The results are guaranteed to be correct. It might seem extreme to you, but doing transactions and checking data integrity is what an RDBMS is all about. I would not hesitate to use it. Gert-Jan
Tue, 18 Nov 2003 07:03:27 GMT

Graham Morri #5 / 6	Checking if a delete will succeed (without doing the delete) Quote: > > Doing the delete and rolling back the transaction seems a bit extreme, as > > does running sp_depends and trying to analyze the output. > That is exactly what I would do. The results are guaranteed to be > correct. It might seem extreme to you, but doing transactions and > checking data integrity is what an RDBMS is all about. I would not > hesitate to use it. > Gert-Jan Thanks for the reply. Essentially I'm checking some business rules to see what can be deleted, and some of these business rules are in the form of foreign key constraints. Obviously I don't want to duplicate them in code.
Tue, 18 Nov 2003 21:02:20 GMT

Erland Sommarsk #6 / 6	Checking if a delete will succeed (without doing the delete) Quote: > Thanks for the reply. Essentially I'm checking some business rules to see > what can be deleted, and some of these business rules are in the form of > foreign key constraints. Obviously I don't want to duplicate them in code. It all depends. If it is sufficient for you to give the user the error message: "You cannnot delete this records, because it is being referenced", an attempted DELETE is certainly the easiest way to go. But if the user wants to know what is referencing, it's another matter. You can of course parse the error message and then translate the table name to something which makes sense to the user. But if the user wants to know exactly which record that is referencing, then you need to do the checks yourself. Personally, I'm lean towards the position that the constraints in the database mainly for checking that the client developers are doing things right. -- Erland Sommarskog, Abaris AB SQL Server MVP
Thu, 20 Nov 2003 22:48:23 GMT

Page 1 of 1

[ 6 post ]