Delete Validation of Master Record

Question

Delete Validation of Master Record

yusufforu

Newbie

Points: 6
More actions
May 9, 2011 at 7:33 am

#239122

I have Table of ClientMaster and 4 Tables of Purchase, Sale, Receive, Transfer.
ClientID is passing to all 4 Tables.
On deletion of ClientMaster, i am using a validation that if any Client is using in any of 4 table , its through a message that "You Can't Delete this Record !". and here i am checking one by one to all tables is there any other idea to check this.
i am using SQL Server 2005 and VB 6.
ClientMaster : ID , ClientName, Address .......
tblPurchase : ID, ClientID, InvoiceNo........
tblReceive : ID, ClientID, InvoiceNo........
tblTransfer : ID, ClientID, InvoiceNo........
Thanks & regards,
Yusuf

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

you could define the optional ON DELETE CASCADE options of the foreign keys to do the delete (or setting to NULL) automatically for you, but i'm not sure what the question is;

your validation has some business logic that decides whether you are allowed to delete or not, right?

other than a static list, you might consider using the results of sp_fkeys, or querying the sysforeignkeys view to get the list of dependencies instead.

can you clarify your question a little bit ?"

is there any other idea to check this. wasn't enough for me to grab the concepty yet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yusufforu Newbie Points: 6 More actions · Answer 2

i just want to check that this record should be delete or not.

ClientMaster

id clientname

1 aaa

2 bbb

3 ccc

4 ddd

5 eee

and in sale table 2 client id have used in transaction

id clientid productname

1 3 abc

2 5 xyz

it may be on other tables

now any one want to delete record no 3 or 5, it should not

for this i have used a checking point in VB6 Code

i just want better solution for this

there may be 4 or 40 tables.

Lowell SSC Guru Points: 323755 More actions · Answer 3

so no business logic? just if a record exists, refuse deletion?

in that case, you don't need to do anything, really...just try and delete, and if it fails due to a foreign key constraint violation , or any other error for that matter, return "you cannot delete child records exist", else "Delete successful"

just use VB6's On Error Resume next,and check for errors right after you run the delete statement to know which message to return.

you can wrap it in a transaction as wel if you want.

Private Sub DeleteStuff(ByVal TableName As String, ByVal ColumnName As String, ByVal ColumnValue As String)

On Error Resume Next

Dim MyConnection As ADODB.Connection

MyConnection.ConnectionString = "data source=MainServer;initial catalog=ProductionDB;user id=AnyUserExceptSA;password=NotARealPassword;Trusted_Connection=False;Application Name=Ghost In The Machine;"

Dim sql As String

MyConnection.Open

sql = "DELETE FROM " & TableName & " WHERE " & ColumnName & " ='" & ColumnValue & "'"

MyConnection.Execute (sql)

If err.Number <> 0 Then

MsgBox "Cannot Delete From " & TableName & "; One or more Child Records Exist!"

Else

MsgBox "Data Deleted From " & TableName & "; "

End If

End Sub

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!