locked
How to delete from two tables RRS feed

  • Question

  • User646364117 posted

     I have a reorderlist that is using a sqlDataSource to do select, update, delete. The existing delete is

    DeleteCommand="DELETE FROM tblMilestones WHERE [MilestoneId] = @Original_MilestoneID AND DealKey=@DealKey"

    When the delete is done i need to also perform a delete from a related table based on MilestoneID.

    I am struggling with implementing the second delete. Any help is appreciated.

    <%@ Page Title="Deal Milestones" Language="VB" MasterPageFile="~/Protected/TwoColFixed.master" 
    AutoEventWireup="false" EnableViewState="true"
    CodeFile="DealMilestones.aspx.vb" Inherits="Protected_DealMilestones" EnableTheming="true" Theme="PrimaryTheme" %>
    <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
        
           <asp:Panel ID="pnlMileStoneList" runat="server">
           
             <cc1:ReorderList ID="ReorderList1" runat="server"
                                   PostBackOnReorder="true"
                                   DataSourceID="SqlDataSource1"
                                   CallbackCssStyle="callbackStyle"
                                   DragHandleAlignment="Left"
                                   ItemInsertLocation="End" 
                                   DataKeyField="MilestoneID"
                                   SortOrderField="MilestoneOrder"  
                                    
                                   Width="400px"  >
                                   
            <InsertItemTemplate>
              <div style="padding-left:25px; border-bottom:thin solid transparent;">
                <asp:Panel ID="panelInsert" runat="server" DefaultButton="ImageButton1">
                    
                   
                     
                <asp:DropDownList id="ddlNewMilestone"  SelectedValue='<%# Bind("Title")%>' runat="server"
                ValidationGroup="2"> 
                <asp:ListItem>--Select Additional Milestone--</asp:ListItem>
                <asp:ListItem>Accepted Offer</asp:ListItem>
                <asp:ListItem>Appraisal</asp:ListItem>
                  
                </asp:DropDownList>
                    <asp:RequiredFieldValidator 
                        ID="RequiredFieldValidator1" Enabled="True"  EnableClientScript="true"
                        runat="server" ValidationGroup="2"  ControlToValidate="ddlNewMileStone" InitialValue="--Select Additional Milestone--" 
                        
                        ErrorMessage="Please Select A Milestone"/>
                      <asp:ImageButton ID="ImageButton1" CommandName="Insert" runat="server"
                     ImageUrl="~/Images/icons/milestoneicons/plus.png"/>
                 </asp:Panel>
              </div>
            </InsertItemTemplate>
            <ItemTemplate>
                 <asp:Panel ID="panel1" runat="server" >
                      <div class="itemarea">
                    
                      
                       
                      <asp:Label ID="Label1a" runat="server" Width="100px"
                                   Text='<%# HttpUtility.HtmlEncode(Convert.ToString(Eval("Title"))) %>' />
                       
    
                      <img src='<%# DataBinder.Eval(Container.DataItem, "FullImagePath") %>.png'   
                                     style="border:1px solid white;"
                                     alt="MilestoneImage" />
                                     
                      <asp:ImageButton ID="btnDelete" 
                            runat="server" 
                           Text="Delete" CommandName="Delete" CausesValidation="false"
                           ImageUrl="~/Images/icons/milestoneicons/thex.png"   />
                                      
                      </div>
                      
              </asp:Panel>
            </ItemTemplate>
            <EditItemTemplate>
              <div class="itemArea">
                <asp:TextBox ID="txtTitle_E" runat="server" Text='<%# Bind("Title") %>' />
                
                <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("MilestoneOrder") %>' />
              </div>
            </EditItemTemplate>
            <ReorderTemplate>
              <asp:Panel ID="Panel2" runat="server" CssClass="reorderCue" />
            </ReorderTemplate>
            <DragHandleTemplate>
              <div >
              <asp:ImageButton ID="btnDelete" 
                            runat="server" 
                           Text="Delete" CommandName="Delete" CausesValidation="false"
                           ImageUrl="~/Images/icons/milestoneicons/move.png"   />
              </div>
            </DragHandleTemplate>
          </cc1:ReorderList>
          </asp:Panel>
           <asp:Button ID="btnSetMileStoneScenarioDefinedFlag" runat="server" 
            Text="Set Deal Milestone Scenario" CausesValidation="false" />
             <asp:Button ID="btnCancelScenarioChange" runat="server" Text="Cancel" CausesValidation="False"/>
       </div>
       
      <asp:SqlDataSource ID="SqlDataSource1" runat="server"  
               ConnectionString="<%$ ConnectionStrings:DB_6056_securedeConnectionString %>" 
                DeleteCommand="DELETE FROM tblMilestones WHERE [MilestoneId] = @Original_MilestoneID AND DealKey=@DealKey" 
                InsertCommand="INSERT INTO tblMilestones ([Title],  [MilestoneOrder]) VALUES (@Title, @MilestoneOrder)"
                SelectCommand="SELECT [MilestoneId], [Title],[DateDue],  [MilestoneOrder] , FullImagePath = Directory + Title FROM tblMilestones WHERE DealKey=@DealKey ORDER BY MilestoneOrder ASC" 
                UpdateCommand="UPDATE tblMilestones SET [MilestoneOrder] = @MilestoneOrder WHERE [MilestoneId] = @MilestoneID AND DealKey=@DealKey" 
                OldValuesParameterFormatString = "Original_{0}">      
                
                
                
         <SelectParameters>
            <asp:SessionParameter Name="DealKey" SessionField="DealID" Type="Int32"  />
         </SelectParameters>
         <DeleteParameters>
            <asp:Parameter Name="MilestoneID" Type="Int32" />
            <asp:SessionParameter Name="DealKey" SessionField="DealID" Type="Int32" />
            
         </DeleteParameters>
         <UpdateParameters>
            <asp:Parameter Name="MilestoneOrder" Type="Int32" />
            <asp:Parameter Name="Original_MilestoneID" Type="Int32" />
            <asp:SessionParameter Name="DealKey" SessionField="DealID" Type="Int32"  />
         </UpdateParameters>
         <InsertParameters>
            <asp:Parameter Name="Title" Type="String" />
           
            <asp:Parameter Name="MilestoneOrder" Type="Int32" />
         </InsertParameters>
       </asp:SqlDataSource>
    
    </ContentTemplate>
    </asp:UpdatePanel>
    
       
    
    </asp:Content>
    



     

     

    Saturday, January 1, 2011 4:53 PM

Answers

  • User-1887867400 posted

    Hi, this isn't a problem that you shouldn't handle with asp.net controls. You need to modify your database and set the foreign key constraint between the two related tables.

    Then you need to specify a cascading delete in the Delete Rule between the two related table.

    In this way, whenver you'll delete a record from the master table, the corressponding records from the child table (related) will be deleted automatically without performing any aditional sql query.

    If you didn't understand this, then learn a bit about SQL Server tables, foreign keys and TSQL.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 2, 2011 1:36 AM

All replies

  • User-1887867400 posted

    Hi, this isn't a problem that you shouldn't handle with asp.net controls. You need to modify your database and set the foreign key constraint between the two related tables.

    Then you need to specify a cascading delete in the Delete Rule between the two related table.

    In this way, whenver you'll delete a record from the master table, the corressponding records from the child table (related) will be deleted automatically without performing any aditional sql query.

    If you didn't understand this, then learn a bit about SQL Server tables, foreign keys and TSQL.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 2, 2011 1:36 AM
  • User646364117 posted

     Yes,-I tried and that worked. Thank you.

    Sunday, January 2, 2011 9:37 AM