Insert into One to Many Relational database in ASP.net

Question

1.00/5 (2 votes)

See more:

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.

[EDIT]
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.

XML

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

Posted 31-Dec-10 7:29am

nbiyanka

Updated 2-Jan-11 4:36am

Estys

v4

Add a Solution

Comments

[no name] 31-Dec-10 13:45pm

Show what you have tried so far.

Estys 2-Jan-11 10:38am

"removed" answer of the OP and pasted it into the original question.

Ali Al Omairi(Abu AlHassan) 1-Feb-11 18:29pm

Sir, give me the code you write in the .aspx.vb/.aspx.cs handling the onclick event.

2 solutions

Add a Solution

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

Ali Al Omairi(Abu AlHassan) · Answer 1 · 2011-01-01T05:00:00

you may need to save your detais in an xml using a clint script then save it in a hidden input. and when you post post your page back use the xml string as a in input to the insert sp:

SQL

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

nbiyanka · Answer 2 · 2011-01-02T04:22:00

Solution 2

[EDIT]
Moved text to Question

Posted 2-Jan-11 4:22am

nbiyanka

Updated 2-Jan-11 4:36am

v2

Comments

Estys 2-Jan-11 10:37am

Reply with a comment to Ali Al Omari.

Insert into One to Many Relational database in ASP.net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Insert into One to Many Relational database in ASP.net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us