How To Delete 2 Table In Single Query?

Question

delete from tblvolunteer where volunteerid = @involunteeriD; delete from tblcfgvolunteerfingerprint where volunteerid=@involunteeriD Sent Me Code

Sachin Singh · 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

Sachin Singh · Answer

You need to reverse the statement to overcome conflict delete from tblcfgvolunteerfingerprint where volunteerid=@involunteeriD delete from tblvolunteer where volunteerid = @involunteeriD;

Sachin Singh · Answer

test sp by executing it as exec UIPKGCONFIGURATION_spDeleteVolunteer 12001

Sachin Singh · Answer

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.

shah Momin · Answer

Forgeni Key Name is :- tblvolunteer

shah Momin · Answer

public void DeleteVolunteerRecord( int volunteerid) { DatabaseHelper.ExecuteNonQuery( "UIPKGCONFIGURATION_spDeleteVolunteer" , new List { new SqlParameter( "@involunteeriD" , SqlDbType.Int) { Direction = ParameterDirection.Input, Value = volunteerid } }); } public void DeleteVolunteerRecords( int volunteerid) { this .objConfigurationDAL.DeleteVolunteerRecord(volunteerid); } this Code Is dll file error get in dll file

Rajanikant Hawaldar · Answer

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

shah Momin · Answer

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.'

shah Momin · Answer

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