how to insert multiple records in Access database using VB.NET

Question

I am trying learn how to use Access within VB.NET so i tried to make a simple application using an Access Database that can be used as a dictionary where someone can add some words int the database and then he can search for them.

My db contains two tables one with Word | Description and another one with Word | Synonym

The issue is that one word may have more than one Synonyms so i was thinking i could type all the synonyms in a textbox and using Regex.Split(" ") to split them and insert them in a loop. Can this be done with OleDbParameters? This is what i have done so far but it only inserts the last record:

 str = "insert into Synonyms ([Word],[Synonym]) values (@word,@synonym)"
    cmd = New OleDbCommand(str, myConnection)
    cmd.Parameters.Add(New OleDbParameter("Word", CType(txtWord.Text, 
String)))
    cmd.Parameters.Add("@synonym", OleDbType.VarChar)
    Dim syn As String() = Regex.Split(txtSynonyms.Text, " ")

    Dim i As Integer = 0
    While i < syn.Length()
        cmd.Parameters("@synonym").Value = syn(i)
        i = i + 1
    End While
    Try
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        MsgBox("Synonyms for word """ & txtWord.Text & """ added")
        txtWord.Clear()
        txtSynonyms.Clear()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

Answer 1

Maybe you will find this helpful.

Imports System.Data.OleDb

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim con As New OleDb.OleDbConnection
        Dim dbprovider As String
        Dim dbsource As String
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim inc As Integer
        dbprovider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        dbsource = "Data Source = C:\your_path_here\Nwind_Sample.accdb"
        con.ConnectionString = dbprovider & dbsource
        con.Open()
        sql = "SELECT * FROM [OrderDetails]"

        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "OrderDetails")

        Dim builder As New OleDbCommandBuilder(da)
        Dim dsnewrow As DataRow

        dsnewrow = ds.Tables("OrderDetails").NewRow()

        dsnewrow.Item(0) = OrderID.Text
        dsnewrow.Item(1) = ProductID.Text
        dsnewrow.Item(2) = UnitPrice.Text
        dsnewrow.Item(3) = Quantity.Text
        dsnewrow.Item(4) = Discount.Text
        'dsnewrow.Item(6) = True


        ds.Tables("OrderDetails").Rows.Add(dsnewrow)
        da.Update(ds, "OrderDetails")

    End Sub
End Class

Answer 2

myConnection.Open()
While i < syn.Length()
    cmd.Parameters("@synonym").Value = syn(i)
    cmd.ExecuteNonQuery()
    i = i + 1
End While
myConnection.Close()