CODEWITHSUNDEEP
vb.netms-access-2007
LaserCircus
LaserCircus

Reputation: 25

How to insert multiple row/records/items from listview to ms access using vb.net

Good Day! I am making a program which saves multiple rows to MS access 2007. My problem is, it only saves the first row when the listview has 2 or more rows/items then an error will pop up but if the listview has one item only it saves smoothly and errorless, i like it to save all the rows/items in the listview in just one click smoothly and errorless. Thanks guys! Cheers!

note: field names are (with their respective data types): ItemID (Number) Item (text) Brand (text) Model (text) Price (Number)

here is the code of the button: 

    Dim con As New OleDbConnection

    con.ConnectionString = "Provider=Microsoft.ace.oledb.12.0;data source= ..\dborder.accdb"

    If con.State = ConnectionState.Closed Then
        con.Open()

        For x = 0 To ListView1.Items.Count - 1

            Dim sqlQuery As String = "INSERT INTO tbl_sample Values ('" & ListView1.Items(x).SubItems(0).Text & "', '" & ListView1.Items(x).SubItems(1).Text & "','" & ListView1.Items(x).SubItems(2).Text & "','" & ListView1.Items(x).SubItems(3).Text & "','" & ListView1.Items(x).SubItems(4).Text & "')"

            Dim cmd As New OleDbCommand

            With cmd
                .CommandText = sqlQuery
                .Connection = con
                .ExecuteNonQuery()


            End With
            MsgBox("Transaction Saved")
            ListView1.Items.Clear()


        Next

    End If
    con.Close()

Upvotes: 1

Views: 2029

Answers (1)

LarsTech
LarsTech

Reputation: 81675

Don't clear out the items until after you finish the loop:

Move the Clear line:

Next
ListView1.Items.Clear() 
MsgBox("Transaction Saved")

Your queries would benefit from using parameters to avoid sql injection and data conversion issues.

Upvotes: 1

Related Questions