插入到ASP.net中的一对多关系数据库中

问题描述

您好，专家，

我对asp.net编程非常陌生.我正在asp.net上进行实验项目.

我有两个表(Sales_Head和Sales_Details)，而Sales_Head的主键是Sls_Serial_No，这是Sales_Details表的FK.
我有一个文本框和几个下拉列表和webdatagrid的Web表单.
输入到文本和下拉列表中的数据应插入Sales_Header表中，而输入到webdatagrid中的详细信息应保存在Sales_Details表中.由于webdatagrid是使用insert和Update方法绑定到sqlDatasource的，所以我的问题是它给我一个错误，指出我要插入到Sales_Details表中的FK值在Sales_Head父表中不存在.我想先将数据保存到Sales_Head父表中，然后在用户单击按钮时保存到Sales_Details子表中.任何人都可以通过示例代码来帮助我解决此问题.


首先，非常感谢您对Ali Al Omairi的回答，并表示赞赏.

您的意思是拥有一个存储过程，您不是Ali吗?我期望的是在我的项目中不使用任何SP，但是如果没有其他选项，那么我可能不得不使用您提到的方法，然后再次单击它时，它仅将记录插入Sales_Head表中. WebDataGrid1的最后一个Col会触发sqlDatasource的insert方法，因为它已与sqlDataSource绑定，因此在触发Sales_Head表时不包含试图保存到Sales_Details表中的PK.您能否看一下我粘贴在下面的代码，并帮助我清除此代码.

Hi experts,

I am very new to asp.net programming. I am working on an experimental project on asp.net.

I have two tables (Sales_Head and Sales_Details) and the Primary key of the Sales_Head is Sls_Serial_No and which is the FK of the Sales_Details table.
I have a web form consits of text boxes and couple of dropdown lists and webdatagrid.

Data that enter into text and dropdown list should insert into Sales_Header table and the details which is entered into webdatagrid should save in Sales_Details table. Since webdatagrid is bound to the sqlDatasource with insert and Update methods, my problem is that it gives me an error saying that FK value that I’m trying to insert into Sales_Details table does not exist in the Sales_Head parent table. I want to first save the data into Sales_Head parent table and then to Sales_Details child table when user clicks on a button. Can anyone plz help me to solve this problem with a sample code.


First of all thank you very much for your answer Ali Al Omairi and appreciate it.

U mean to have a stored procedure didn''t u Ali? What I was expecting is not to use any SP in my project, but if there is no other options then I may have to use the method u mentioned, then again it only insert the records to Sales_Head table, when I hit on enter button at the last Col of the WebDataGrid1 it fires the sqlDatasource''s insert method since it is been bound with sqlDataSource, at the time of it fires Sales_Head table doesn''t contain the PK that is trying to save in to the Sales_Details table. Can u plz look at my code I pasted below and help me to clear this out.

<asp:WebDataGrid ID="WebDataGrid1" runat="server" Height="350px" Width="960px"

    AutoGenerateColumns="False" Font-Names="Arial Narrow" Font-Size="Smaller"

    BackColor="LightGray" DataSourceID="SqlDataSource1">
    <Columns>
        <ig:BoundDataField DataFieldName="Main_Cat" Key="Main_Cat">
            <Header Text="Main_Cat" />
        </ig:BoundDataField>
        <ig:BoundDataField DataFieldName="Mnu_Item_Code" Key="Mnu_Item_Code">
            <Header Text="Mnu_Item_Code" />
        </ig:BoundDataField>
        <ig:BoundDataField DataFieldName="Mnu_Item_Name" Key="Mnu_Item_Name">
            <Header Text="Mnu_Item_Name" />
        </ig:BoundDataField>
        <ig:BoundDataField DataFieldName="Trns_Out_Qty" Key="Trns_Out_Qty">
            <Header Text="Trns_Out_Qty" />
        </ig:BoundDataField>
        <ig:BoundDataField DataFieldName="Mnu_Item_Rate" Key="Mnu_Item_Rate">
            <Header Text="Mnu_Item_Rate" />
        </ig:BoundDataField>
        <ig:BoundDataField DataFieldName="Remarks" Key="Remarks">
            <Header Text="Remarks" />
        </ig:BoundDataField>
    </Columns>
    <Behaviors>
        <ig:EditingCore>
            <Behaviors>
                <ig:CellEditing>
                </ig:CellEditing>
                <ig:RowAdding >
                </ig:RowAdding>
                <ig:RowDeleting />
            </Behaviors>
        </ig:EditingCore>
        <ig:Selection CellClickAction="Row" RowSelectType="Single">
        </ig:Selection>
        <ig:RowSelectors>
        </ig:RowSelectors>
    </Behaviors>
</ig:WebDataGrid>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"

    ConnectionString="<%$ ConnectionStrings:FoodLogConnStr %>"

    InsertCommand="INSERT INTO Trns_Detail(Trns_OUT_Serial_No, Mnu_Item_Code, Trns_Out_Qty, Mnu_Item_Rate, Remarks) VALUES (@Trns_OUT_Serial_No, @Mnu_Item_Code, @Trns_Out_Qty, @Mnu_Item_Rate, @Remarks)"

    SelectCommand="SELECT Item_Master.Main_Cat, Trns_Detail.Mnu_Item_Code, Item_Master.Mnu_Item_Name, Trns_Detail.Trns_Out_Qty, Trns_Detail.Mnu_Item_Rate, Trns_Detail.Remarks FROM Trns_Detail INNER JOIN Item_Master ON Trns_Detail.Mnu_Item_Code = Item_Master.Mnu_Item_Code INNER JOIN Trns_Head ON Trns_Detail.Trns_OUT_Serial_No = Trns_Head.Trns_OUT_Serial_No WHERE (Trns_Head.Trns_Date = @Trns_Date) ORDER BY Item_Master.Main_Cat, Trns_Detail.Mnu_Item_Code" >
    <InsertParameters>
        <asp:Parameter Name="Trns_OUT_Serial_No" />
        <asp:Parameter Name="Mnu_Item_Code" />
        <asp:Parameter Name="Trns_Out_Qty" />
        <asp:Parameter Name="Mnu_Item_Rate" />
        <asp:Parameter Name="Remarks" />
    </InsertParameters>
    <SelectParameters>
        <asp:ControlParameter ControlID="txtFoodLogDate" Name="Trns_Date"

            PropertyName="Text" />
    </SelectParameters>
</asp:SqlDataSource>

推荐答案

SET @Sls_Serial = SCOPE_IDINTITY();

DECLARE @insptr Int;
EXEC sp_xml_preparedocument @insptr OUTPUT, @XML;

INSERT INTO Sales_Head
    SELECT
      @Sls_Serial_No
      -- ,<other columns>
     FROM OPENXML(@insptr,'<element path>',1)
WITH
(<column> <type> '@<attribute name>');

EXEC sp_xml_removedocument @insptr

这篇关于插入到ASP.net中的一对多关系数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！