VBNET How to insert multiple values into different rows with two table

Question

How can i link two table together and insert multiple values into different rows when coding ? Like when the employee purchased 3 items so it will appear on another table, and each items with taken each rows instead of one column.

I've no idea how to work on it, it will be much appreciated if you could help it out.

Items:
[X] Bread
[] Milk
[X] Tools
[] Books

For Example:

**Table 1: Employee**
EmpID (Primary key)
Name
TotalPurchased

**Table 2: EmployeeDetails**
ED_ID (Primary key)
EmpID (Foreign key)
Items

It looks to me like you need another table for the items, with columns `EmpId`, `Product` (or [`SKU`](https://en.wikipedia.org/wiki/Stock_keeping_unit)), and perhaps `PurchaseDate`. — Andrew Morton, Apr 05 '20 at 17:30
Yes, above it just an example. I've no idea how to code it out. Can you give me some example? — Kayden, Apr 05 '20 at 17:32
If you look at [this answer](https://stackoverflow.com/a/60803625/1115360) starting from the line which starts with `sql = "INSERT INTO`, hopefully you will have enough to work with. If not, please [edit] your question to show the code you have tried. N.B. You forgot to tell us which database it is using. — Andrew Morton, Apr 05 '20 at 17:40

score 0 · Accepted Answer · answered Apr 06 '20 at 03:28

The important thing to note in the first Sub is the Select Scope_Identity() which returns the last inserted identity on the connection.

The second Sub adds the @ID parameter and set its value outside the loop. (it is the same for each item) The @Item parameter is added to the parameters collection outside the loop and its value is set inside the loop. The connection is opened once, outside the loop. The command is executed on each iteration of the loop.

Private Sub InsertEmployee(Name As String, Purchase As Decimal, Items As List(Of String))
    'This code assumes that EmpID is an identity field (auto-increment)
    'and therefore not included in the Insert
    Dim strSql = "Insert Into Employee (Name, TotalPurchased) Values (@Name, @Purchase); Select Scope_Identity();"
    Dim EmployeeID As Integer
    Using cn As New SqlConnection("Your connection String"),
            cmd As New SqlCommand(strSql, cn)
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = Name
        cmd.Parameters.Add("@Puchase", SqlDbType.Decimal).Value = Purchase
        cn.Open()
        EmployeeID = CInt(cmd.ExecuteScalar)
    End Using
    InsertItems(EmployeeID, Items)
End Sub

Private Sub InsertItems(Id As Integer, PurchaseItems As List(Of String))
    'This code assumes that ED_ID is an identity field (auto-increment)
    'and therefore not included in the Insert
    Dim strSql = "Insert Into EmployeeDetails (EmpID, Items) Values (@ID, @Item);"
    Using cn As New SqlConnection("Your connection String"),
            cmd As New SqlCommand(strSql, cn)
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Id
        cmd.Parameters.Add("@Item", SqlDbType.NVarChar, 100)
        cn.Open()
        For Each i In PurchaseItems
            cmd.Parameters("@Item").Value = i
            cmd.ExecuteNonQuery()
        Next
    End Using
End Sub

An example of how the code could be used.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim CheckBoxes = {CheckBox1, CheckBox2, CheckBox3, CheckBox4}
    Dim lst As New List(Of String)
    For Each c In CheckBoxes
        If c.Checked Then
            lst.Add(c.Text)
        End If
    Next
    Dim PurchaseAmount As Decimal
    If Decimal.TryParse(TextBox2.Text, PurchaseAmount) Then
        InsertEmployee(TextBox1.Text, PurchaseAmount, lst)
    Else
        MessageBox.Show("Please enter a valid amount for Purchase")
    End If
End Sub

VBNET How to insert multiple values into different rows with two table

1 Answers1