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

You're over-writing the parameter in a loop and then executing the query only once. If you want to execute the query multiple times, do it in the loop. — David
– David, Commented Aug 12, 2017 at 17:30
You just need to move the cmd.ExecuteNonQuery inside the while loop that traverse the synonyms array. At each loop insert the data. — Steve
– Steve, Commented Aug 12, 2017 at 17:42
Also not sure what is the advantage of using Regex.Split against a simple string.Split — Steve
– Steve, Commented Aug 12, 2017 at 17:43
@Steve i don't know if by moving the cmd.ExecuteNonQuery you meant moving the whole Try Catch, i tried that but i'm getting a message "ExecuteNonQuerry: Connection property has not been initialized" — Sakis
– Sakis, Commented Aug 12, 2017 at 18:09
The line, just the line. And about the connection. You need to open it before executing anything. From your code is not clear if you open or not the connection before the ExecuteNonQuery — Steve
– Steve, Commented Aug 12, 2017 at 19:27

dovid · Accepted Answer · 2017-08-12 18:19:37Z

1

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

answered Aug 12, 2017 at 18:19

dovid

6,5114 gold badges35 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ASH · Accepted Answer · 2017-08-20 19:51:21Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related