• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Insert parent-child information in sql database

I have two tables in my database. Parent table has primary key (autogen) and other information about requestor which I am able to insert through asp.net form.
Now, I have a child table which has foreign key and other information. Each parent table record can have more than one child record.

I am trying to create a loop to create a new child record and at the same time I want to pass the primary key of parent table. Any ideas - how can I achieve this? Here is my code.
I am able to capture the primary key of parent table. But I am not sure how to insert it into child table. Also, how could I create a loop to create more child records which are linked to same primary key of parent table?

Sub button_Click(sender As Object, e As EventArgs)
   
   
        Dim connectionString As String = "server='xxxxxx'; user id='xxxxx'; password='xxxx'; database='abc'"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
        Dim queryString As String = "INSERT requestor (whseNum, locName, rDate, rName, rphone, remail) VALUES ( @whseNum, @locName, @rDate, @rName, @rphone, @remail)" & _
        "SELECT @@IDENTITY AS 'Identity'"
        Dim cmdInsert As SqlCommand
        Dim iID AS Integer
        Dim childID As Integer
   
        cmdInsert= New SqlCommand(queryString, dbConnection)
        cmdInsert.Parameters.Add("@whseNum", dropwhseNum.SelectedItem.Value)
        cmdInsert.Parameters.Add("@locName", location.SelectedItem.Value)
        cmdInsert.Parameters.Add("@rDate", rdate.Text)
        cmdInsert.Parameters.Add("@rName", rname.Text)
        cmdInsert.Parameters.Add("@rphone", rphone.Text)
        cmdInsert.Parameters.Add("@remail", remail.Text)
   
   
        Try
        dbConnection.Open()
   
       iID = cmdInsert.ExecuteScalar()
   
   
       childID = iID.ToString()
   
       Dim query As String = "Insert inventory (invenID) Values ('1234')"& _
        "SELECT @@IDENTITY AS 'Identity'"
       Dim cInsert As SqlCommand
       cInsert = New SqlCommand(query, dbConnection)
       childID = cInsert.ExecuteScalar()
   
        Catch ex As Exception
        Response.Write(ex.Message)
        Response.End
        Finally
        dbConnection.Close()
        End Try
   
       Response.Write("The ID" & childID)
        Response.End
   
   
    End Sub

0
InfoTeam
Asked:
InfoTeam
  • 4
  • 3
1 Solution
 
arif_eqbalCommented:
Ok your first part seems to be right you are inserting values into the requestor table and you get the Autogenerated ID as well

Now what is the second query ???

Dim query As String = "Insert inventory (invenID) Values ('1234')"& _
        "SELECT @@IDENTITY AS 'Identity'"
       Dim cInsert As SqlCommand
       cInsert = New SqlCommand(query, dbConnection)
       childID = cInsert.ExecuteScalar()

Is it the child table ???
if yes then why are you again using "Select Identity..."
if it is the child it should be like

Insert into Inventory (FK_Field,Other_Field) values (" & iID & ",'Other values')
0
 
InfoTeamAuthor Commented:
Thanks arif,

I realized that - (after I posted my code) and I have changed that as below and I am able to insert foreign key to child table. However, I have more than one child raw for each primary key in parent raw.
I think I need to create a loop for child table which inserts the same foreign key. I have to capture this information from the form which allows users to add line item with same foreign key.
How can I do this?

________

Try
        dbConnection.Open()
   
       iID = cmdInsert.ExecuteScalar()
   
   
         childID = iID.ToString()
   
       Dim query As String = "Insert inventory (recordID, invenID) Values (@@IDENTITY, '1234')"
   
       Dim cInsert As SqlCommand
       cInsert = New SqlCommand(query, dbConnection)
       childID = cInsert.ExecuteScalar()
   
        Catch ex As Exception
        Response.Write(ex.Message)
        Response.End
        Finally
        dbConnection.Close()
        End Try
   
         
   
    End Sub
0
 
arif_eqbalCommented:
iID = cmdInsert.ExecuteScalar()
 childID = iID.ToString()

I assume after these lines you have the Primary Key value in the variable childID
Now you need that for each Child Data this key which will be the Foreign Key for the Child to be inserted.

Well as for a loop it is an easy thing you can have

For i=0 to YourNoOfChildRecords
     'It will be Child ID here and not @@Identity
     Dim query As String = "Insert inventory (recordID, invenID) Values (childID, '1234')"
   
       Dim cInsert As SqlCommand
       cInsert = New SqlCommand(query, dbConnection)
       childID = cInsert.ExecuteScalar()
   
        Catch ex As Exception
        Response.Write(ex.Message)
        Response.End
Next


However, how are the child records enterred, I mean do you have some sort of a Grid
if yes then loop through all the Items of the grid
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
InfoTeamAuthor Commented:
Thanks Arif,

ok - Now - I have made some progress - but still not there yet.

When I do the loop - it repeats the same records. That is not what I want. I need to allow users to enter the child records. - Here is my code.
_______

Sub button_Click(sender As Object, e As EventArgs)

            'Insert Parent record with primary key auto generated
             Dim connectionString As String = "server='xxxx'; user id='xxxx'; password='xxx'; database='xxx'"
             Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
             Dim queryString As String = "INSERT requestor (whseNum, locName, rDate, rName, rphone, remail) VALUES ( @whseNum, @locName, @rDate, @rName, @rphone, @remail)" & _
             "SELECT @@IDENTITY AS 'Identity'"
             Dim cmdInsert As SqlCommand
            Dim iID AS Integer
             Dim childID As Integer
             Dim numrows As Integer


             'capture requestor info and insert into parent table
             cmdInsert= New SqlCommand(queryString, dbConnection)
             cmdInsert.Parameters.Add("@whseNum", dropwhseNum.SelectedItem.Value)
             cmdInsert.Parameters.Add("@locName", location.SelectedItem.Value)
             cmdInsert.Parameters.Add("@rDate", rdate.Text)
             cmdInsert.Parameters.Add("@rName", rname.Text)
             cmdInsert.Parameters.Add("@rphone", rphone.Text)
             cmdInsert.Parameters.Add("@remail", remail.Text)


             Try
             dbConnection.Open()
             'capture primary key of parent table


            iID = cmdInsert.ExecuteScalar()
            Dim i As Integer
            For i = 0 to 2

             'create a variable and pass the primary key of parent table
             childID = iID.ToString()


            'insert information into child table

           ' Dim query As String = "Insert inventory (recordID, fsv, producedItem, contract, invenID, ard, description, routeNo, prefLineNo, plannedProdDate, palletConfiguration, comments) Values (@@IDENTITY, @fsv, @producedItem, @contract, @invenID, @ard, @description, @routeNo, @prefLineNo, @plannedProdDate, @palletConfiguration, @comments)"


            Dim query As String = "Insert inventory (recordID, fsv, producedItem, contract, invenID, ard, description, routeNo, prefLineNo, plannedProdDate, palletConfiguration, comments) Values (@recordID, @fsv, @producedItem, @contract, @invenID, @ard, @description, @routeNo, @prefLineNo, @plannedProdDate, @palletConfiguration, @comments)"

            Dim cInsert As SqlCommand
            cInsert = New SqlCommand(query, dbConnection)
            cInsert.Parameters.Add("@recordID", childID)
            cInsert.Parameters.Add("@fsv", fsvItem.Value)
            cInsert.Parameters.Add("@producedItem", producedItem.Value)
            cInsert.Parameters.Add("@contract", contract.Value)
            cInsert.Parameters.Add("@invenID", invenID.Text)
            cInsert.Parameters.Add("@ard", ard.SelectedItem.Value)
            cInsert.Parameters.Add("@description", description.Text)
            cInsert.Parameters.Add("@routeNo", routeNo.Text)
            cInsert.Parameters.Add("@prefLineNo", prefLineNo.Text)
            cInsert.Parameters.Add("@plannedProdDate", plannedProdDate.Text)
            cInsert.Parameters.Add("@palletConfiguration", palletConfiguration.Text)
            cInsert.Parameters.Add("@comments", comments.Text)
            childID = cInsert.ExecuteScalar()
              Next i
           ' Display the child records
            Dim query2 As String ="select * from inventory "

            Dim MyCommand As SqlDataAdapter = New SqlDataAdapter(query2 , dbConnection)

            Dim DS As DataSet = New DataSet()
            MyCommand.Fill(DS, "inventory")
            MyDataGrid.DataSource = DS.Tables("inventory")
            MyDataGrid.DataBind()

             Catch ex As Exception
             Response.Write(ex.Message)
             Response.End



           ' Response.Write("The ID" & childID)
            ' Response.End
    'Response.ReDirect("fullGoodsRequestForm1.aspx")

            Finally
             dbConnection.Close()
             End Try

         End Sub
0
 
arif_eqbalCommented:
right it is inserting dupluicate records
because when you run your loop two times you send the same parameters.
your problem is here

For i = 0 to 2
             'create a variable and pass the primary key of parent table
             childID = iID.ToString()
            'insert information into child table
            Dim query As String = "Insert inventory (recordID, fsv, producedItem, contract, invenID, ard, description, routeNo, prefLineNo, plannedProdDate, palletConfiguration, comments) Values (@recordID, @fsv, @producedItem, @contract, @invenID, @ard, @description, @routeNo, @prefLineNo, @plannedProdDate, @palletConfiguration, @comments)"

            Dim cInsert As SqlCommand
            cInsert = New SqlCommand(query, dbConnection)
            cInsert.Parameters.Add("@recordID", childID)
            cInsert.Parameters.Add("@fsv", fsvItem.Value)
            cInsert.Parameters.Add("@producedItem", producedItem.Value)
            cInsert.Parameters.Add("@contract", contract.Value)
            cInsert.Parameters.Add("@invenID", invenID.Text)
            cInsert.Parameters.Add("@ard", ard.SelectedItem.Value)
            cInsert.Parameters.Add("@description", description.Text)
            cInsert.Parameters.Add("@routeNo", routeNo.Text)
            cInsert.Parameters.Add("@prefLineNo", prefLineNo.Text)
            cInsert.Parameters.Add("@plannedProdDate", plannedProdDate.Text)
            cInsert.Parameters.Add("@palletConfiguration", palletConfiguration.Text)
            cInsert.Parameters.Add("@comments", comments.Text)
'---> YOU SEE EACH TIME THE LOOP EXECUTES THE SAME VALUE OF comments.Text WILL BE ADDED
'----> ALL THESE PARAMETERS NEEDS TO BE CHANGED EVERY TIME THE LOOP EXECUTES
            childID = cInsert.ExecuteScalar()
     Next i


So you see the textbox values are being inserted again and again, Now you say you have more than one record for the child, how are they entered in the front end, youare inserting values from the TextBoxes (invenID.Text, routeNo.Text etc.) now textboxes can only contain one value so we can't have multiple records,
In case there are more than one record for the child we sort of need a DataGrid or something. Then we loop through the Datagrid for each row in it and save in the database.

OK, I'll create a sample Master-Detail form using an Employee table so that you can get a feel of how things ought to be...
0
 
InfoTeamAuthor Commented:
Thanks Arif, I understand now why it is duplicating.
Here is my html portion - may be that will help to resolve this.
First table captures parents table information. Second table displays child records ( I want the users to see what their line items)
Third table is where I am capturing child table information. I need to allow at least 20 child records. But users may or may not have all 20 line items. So, if I have a grid - then in that case it will insert null values ????
May be when I see your example, I will understand.

<html>
<head>
    <title>Untitled Document</title>
    </head>
<body>
        <form name="requestorInfo" method="post" runat="server">
        <table width="100%" border="1">
            <tbody>
                <tr>
                    <td width="12%">
                        Whse# in NIS</td>
                    <td width="88%">
                        <asp:DropDownList id="dropwhseNum" runat="server" AutoPostBack="True" OnSelectedIndexChanged="Selection_Change" BackColor="#E0E0E0"></asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td>
                        Location Name:</td>
                    <td>
                        <asp:DropDownList id="location" runat="server"></asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td>
                        Request Date:</td>
                    <td>
                        <asp:TextBox id="rdate" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Requestor's Name:</td>
                    <td>
                        <asp:TextBox id="rname" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Requestor's Phone Number:</td>
                    <td>
                        <asp:TextBox id="rphone" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Requestor's EMail Address:</td>
                    <td>
                        <asp:TextBox id="remail" runat="server"></asp:TextBox>
                    </td>
                </tr>
            </tbody>
        </table>
        <table width="100%" border="1">
            <tbody>
                <tr>
                    <td>
                        <asp:DataGrid id="MyDataGrid" runat="server" EnableViewState="false"></asp:DataGrid>
                    </td>
                </tr>
            </tbody>
        </table>
        <table width="100%" border="1">
            <tbody>
                <tr>
                    <td width="12%">
                        FSV Item</td>
                    <td>
                        Produced Item</td>
                    <td>
                        Contract Operations</td>
                    <td>
                        Inven ID</td>
                    <td>
                        ADD, Revise or Delete?</td>
                    <td>
                        Complete Package Description:</td>
                    <td>
                        Plants Only: Route # - Bottle/Can/BIB Line Description</td>
                    <td>
                        Plants Only:Pref. Line #</td>
                    <td>
                        Plants Only: Date of First planned production(estimate)</td>
                    <td>
                        Pallet Configuration:Not set up or maintained in NIS by The NIS Team</td>
                    <td>
                        Comments and Contract Operations Information<br />
                        Contract Operations requires additional maintenance, therefore indicate Contract Operations
                        only if ordered using MRP Console in NIS</td>
                </tr>
                <tr>
                    <td width="88%">
                        <select id="fsvItem" runat="server">
                            <option value="0" selected="selected">No</option>
                            <option value="1">Yes</option>
                        </select>
                    </td>
                    <td>
                        <select id="producedItem" runat="server">
                            <option value="0" selected="selected">No</option>
                            <option value="1">Yes</option>
                        </select>
                    </td>
                    <td>
                        <select id="contract" runat="server">
                            <option value="0" selected="selected">No</option>
                            <option value="1">Yes</option>
                        </select>
                    </td>
                    <td>
                        <asp:TextBox id="invenID" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:DropDownList id="ard" runat="server">
                            <asp:ListItem>Add</asp:ListItem>
                            <asp:ListItem>Revise</asp:ListItem>
                            <asp:ListItem>Delete</asp:ListItem>
                        </asp:DropDownList>
                    </td>
                    <td>
                        <asp:TextBox id="description" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:TextBox id="routeNo" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:TextBox id="prefLineNo" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:TextBox id="plannedProdDate" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:TextBox id="palletConfiguration" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:TextBox id="comments" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td align="middle" colspan="11">
                        <asp:Button id="Button2" onclick="button_Click" runat="server" Text="Submit"></asp:Button>
                    </td>
                </tr>
            </tbody>
        </table>
    </form>
</body>
</html>
0
 
arif_eqbalCommented:
hi InfoTeam
I made a sample but it is Windows form not Web based.
I am a bit tight in web based stuff.

Anyway, I think you already are using a Grid, as your code shows.
I think there's no problem about Null values, while saving the data in your Loop you can check if the Row is Null we would not insert it.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now