How To Delete 2 Table In Single Query?

shah Momin

4y1.1kC#

delete from tblvolunteer
	where volunteerid = @involunteeriD;

	delete  from tblcfgvolunteerfingerprint 
	where volunteerid=@involunteeriD

Sent Me Code

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
This means that your tblVounteer has cascade relation with 3rd table tblattendancelog
so you need to delete the record from this table too
Create procedure UIPKGCONFIGURATION_spDeleteVolunteer
@involunteeriD INT
as
begin
delete from tblcfgvolunteerfingerprint where volunteerid=@involunteeriD;
delete from tblattendancelog where volunteerid=@involunteeriD;
delete from tblvolunteer where volunteerid = @involunteeriD;
end
+3
shah Momin4y ago
i got this Error
(4 rows affected)
Msg 547, Level 16, State 0, Procedure UIPKGCONFIGURATION_spDeleteVolunteer, Line 6 [Batch Start Line 15]
The DELETE statement conflicted with the REFERENCE constraint "fk_tblattendancevolunteerid". The conflict occurred in database "BiometricDB", table "dbo.tblattendancelog", column 'volunteerid'.
The statement has been terminated.

Completion time: 2022-06-18T17:59:03.1911316+05:00
0
Sachin Singh4y ago
test sp by executing it as
exec UIPKGCONFIGURATION_spDeleteVolunteer 12001
+3
Sachin Singh4y ago
if your SP is
Create procedure UIPKGCONFIGURATION_spDeleteVolunteer
@involunteeriD INT
as
begin
delete from tblcfgvolunteerfingerprint where volunteerid=@involunteeriD;
delete from tblvolunteer where volunteerid = @involunteeriD;
end
then there shouldn't be any error, what the error says? please share the screenshot.
+3
shah Momin4y ago
Forgeni Key Name is :- tblvolunteer

+3
shah Momin4y ago
1. public void DeleteVolunteerRecord(int volunteerid)
2. {
3. DatabaseHelper.ExecuteNonQuery("UIPKGCONFIGURATION_spDeleteVolunteer", new List
4. {
5. new SqlParameter("@involunteeriD", SqlDbType.Int)
6. {
7. Direction = ParameterDirection.Input,
8. Value = volunteerid
9. }
10. });
11. }
1. public void DeleteVolunteerRecords(int volunteerid)
2. {
3. this.objConfigurationDAL.DeleteVolunteerRecord(volunteerid);
4. }
this Code Is dll file error get in dll file
+3

Sachin Singh4y ago

You need to reverse the statement to overcome conflict

delete from tblcfgvolunteerfingerprint
where volunteerid=@involunteeriD
delete from tblvolunteer
where volunteerid = @involunteeriD;

shah Momin4y ago

System.Data.SqlClient.SqlException: 'The DELETE statement conflicted with the REFERENCE constraint "fk_tblcfgvolunteerid". The conflict occurred in database "BiometricDB", table "dbo.tblcfgvolunteerfingerprint", column 'volunteerid'.

The statement has been terminated.'

Rajanikant Hawaldar4y ago

You can have foriegn key relationship for tblvolunteer with delete cascade option if you have foriegn key you can alter it or can add it