handle Delete from Grid view have 2 tables wit relationships

Question

0.00/5 (No votes)

See more:

I Want handle Delete from Grid view 2 tables that have relationship with another table

when i want to delete row from the first table
an error accrued

The DELETE statement conflicted with the REFERENCE constraint "FK_TryIt_GuestsTable". The conflict occurred in database "Tourist", table "dbo.TryIt", column 'BookingNo'.
The statement has been terminated.

so i want to create message when i want to delete row & this row have another values in rows of another table

(((you must delete all Booking in the Second table that related with this Client )))

first table :
IDBooking (P.K) Type Guest Hotel Address

Second Table
ID(P.K) IdBooking (F.K) Number Night Number Rooms

first table :

IDBooking (P.K)  Type  Guest Hotel Address
1
2
3
4

Second Table

ID(P.K) IdBooking (F.K)  Number Night Number Rooms
1	1
2	1
3	1
4	2
5	2
6	2
7	2
8	3

----------------
if i want to delete the row 4 in the First table i can
but
if i want to delete the row 1 in the First table i Must
delete Rows(1-2-3) in the Second Table

i want to handel this error to dont appear to the client

Posted 18-Nov-13 1:49am

radals

Updated 18-Nov-13 5:00am

Nelek

v2

Add a Solution

Comments

kabifarm 18-Nov-13 7:59am

why not create delete statement that will delete all related information from both tables at once

like delete from table 1 where code = 1
and delete from table where code = 1.1,2.1,3.1

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tom Marvolo Riddle · Answer 1 · 2013-11-18T01:51:00

Solution 1

Put this code in catch block:

Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Cannot be Deleted because it is used in other Pages!!!');", true);

Posted 18-Nov-13 1:51am

Tom Marvolo Riddle

Comments

Tom Marvolo Riddle 18-Nov-13 8:04am

If you don't want to show this to client then get the exception from catch block and assigned it to a label and make it visible false

thatraja · Answer 2 · 2013-11-18T02:06:00

------------------------------------------------
first table :
------------------------------------------------
IDBooking (P.K) Type Guest Hotel Address
------------------------------------------------
1
2
3
4
------------------------------------------------
Second Table
------------------------------------------------
ID(P.K) IdBooking (F.K) Number Night Number Rooms
------------------------------------------------
1       1
2       1
3       1
4       2
5       2
6       2
7       2
8       3
------------------------------------------------

When you delete the IDBooking(ID = 1) from first table, you should two any of the following.
0) Check the ID exists in the second table, if exists then show alert like you have mentioned in your question.
You must delete all Booking in the Second table that related with this Client.
Sample query to find existence of records

SQL

SELECT * FROM second-table WHERE IdBooking=@ID

Check this article to show alerts
Use & Call RegisterStartUpScript, RegisterClientScript and Client-Side Script[^]

1) Delete the matching record[IDBooking(ID = 1)] from second table, then delete the record from first table. And display message to end user.
Sample query to delete records

SQL

DELETE FROM second-table WHERE IdBooking=@ID

SQL

DELETE FROM first-table WHERE IdBooking=@ID

But it depends on requirement.