Insert Multiple Records into Access DB

Question

What is the most efficient way to insert multiple records into an Access DB with VB.net?

I have a list of objects with multiple properties which are the values for an INSERT query and I want to know can I insert them all together instead of looping through the list of objects, building the query string and executing the queries one by one which is very slow.

Rough example of what I have:

    For Each Val In ValueList

        ValueString = Val.X.ToString & ", "
        ValueString += Val.Y.ToString & ", "
        ValueString += Val.Z.ToString

        SQLValueList.Add(ValueString)

    Next

    Dim cmd As OleDb.OleDbCommand
    Dim strConnection As String
    Dim strSql As String = Nothing

    strConnection = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\db.accdb;" & _
            "User ID=Admin;Password=;"

    For Each ValueString As String In SQLValueList

        strSql = "INSERT INTO Results (FldX, FldY, FldZ)" &
                    "VALUES ( " & ValueString & ");"

        cmd = New OleDb.OleDbCommand(strSql)
        cmd.Connection = New OleDb.OleDbConnection(strConnection)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()

    Next

I'm assuming there is a much better and more efficient way of doing this but I haven't been able to find it!

score 3 · Accepted Answer · edited May 23 '17 at 11:52

3

Yes a parameterized query

Imports System.Data.OleDb
.......

Dim strConnection As String
Dim strSql As String = Nothing

strConnection = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\db.accdb;" & _
        "User ID=Admin;Password=;"

strSql = "INSERT INTO Results (FldX, FldY, FldZ) VALUES ( ?, ?, ?)"
using cn = new OleDbConnection(strConnection)
using cmd = new OleDbCommand(strSql, cn)
    cn.Open()
    ' HERE all the parameters are added with a string dummy value. '
    ' This should be changed if one of the underlying field is of different type '
    ' For example, if FldX is of type integer your need to write '
    '  cmd.Parameters.AddWithValue("@p1", 0) and then in the loop code '
    '  '
    '  cmd.Parameters(0).Value = val.X  or  '
    '  cmd.Parameters(0).Value = Convert.ToInt32(val.X) if val.X is not an integer but  convertible to... '


    cmd.Parameters.AddWithValue("@p1", "")
    cmd.Parameters.AddWithValue("@p2", "")
    cmd.Parameters.AddWithValue("@p3", "")
    For Each val In ValueList
        cmd.Parameters(0).Value = val.X.ToString()
        cmd.Parameters(1).Value = val.Y.ToString()
        cmd.Parameters(2).Value = val.Z.ToString()
        cmd.ExecuteNonQuery()
   Next
End Using
End Using

This is just an example because it is not clear what kind of data is stored in your ValueList (strings, integers, doubles dates?), but I hope that the idea is clear. Create a command object with 3 parameters (one for each field to insert), add every parameter to the command collection with dummy values (in the example, every parameter contains a string value but you need to add the correct datatype for the underlying field type). At this point just loop one time on your values and execute the query.

Please, stay away to string concatenation to build an sql command, expecially when the string values to concatenate are typed by your user. You risk an Sql Injection attack

edited May 23 '17 at 11:52

Community

1
1

answered Oct 04 '13 at 07:15

Steve

213,761
22
232
286

Thanks for the quick response! First off, the data is a mix of strings integers and doubles. I tried your code but I'm getting an error: "Property access must assign to the property or use its value." on the lines `cmd.parameters[n].value = val` what am I doing wrong? – doovers Oct 04 '13 at 07:38
As I have said, the example above assumes that all of your fields (FldX, fldY,FldZ) are of type text in the database. Thus every parameter is of type string in code. If this is not true then you need to use the correct datatype. I will add an example to the answer – Steve Oct 04 '13 at 07:42
Hmm I'm still getting the same error... for example `cmd.Parameters[0].Value = "test"` if I understand you correctly this is the right syntax or am I wrong? – doovers Oct 04 '13 at 07:52
I should mention that I had to add `OleDb.` before `OleDbConnection` and `OleDbCommand` on the `Using` lines and remove the `Dim cmd` line as they were giving errors. Other than that the code is as you wrote it... – doovers Oct 04 '13 at 07:58
Could you tell me the exact error message received and on which line you get it? For the OleDb you could use `Imports System.Data.OleDb` at the beginning of your source file to remove the need of OleDb in front of every class of this namespace. – Steve Oct 04 '13 at 08:06
I have made an error confusing the array syntax of C# with VB.NET. Need to use the round braces not the square brackets. Fixing the answer now – Steve Oct 04 '13 at 08:10
Cool thanks Steve just got it working now! Dramatic improvement speed wise!! – doovers Oct 04 '13 at 08:25

Insert Multiple Records into Access DB

1 Answers1