VB.NET Database Insert Tutorial

Insert data into a database - VB version

Adding data into a database is called an INSERT operation.  The SQL code is called INSERT, but using Ado.net you can actually do this a number of different ways.  This page attempts to show how to insert using some best practices.

What does this sample show?

VistaDB VB.net sample demonstrating how to use SQL Parameters to insert data into a database table named MyTable.

This sample includes a database with a single table that contains 3 columns (ID, Data1, Data2).  The INSERT statement is built manually to demonstrate how to do it, and retrieves the IDENTITY value after the insert in a single statement.

VB.NET Sample SQL Code

All inserts using ADO.Net should really be done with Parameterized Queries.  Using a parameter object on a DBCommand is the safest way to insert data because you don't have to worry about SQL Injection Attacks.  All data is automatically quoted for you through our provider.

Another benefit of using parameterized queries is when you have data that is localization sensitive, such as datetime objects.  By using a parameterized query the datetime is automatically converted to a format that the engine will handle, you don't have to worry about the users locale.

     Public Sub RunDemoInsertSQL()
        Dim sw As New Stopwatch
        sw.Start()

        ' The Data Source is the name of the database. 
        The local file path is used meaning the database is in the same directory
            as the EXE.
        ' Mode is exclusive mode (not shared with other apps)
        ' Pooling is for connection pooling
        Using conn As VistaDBConnection = New 
           VistaDBConnection( "Data Source=NewDatabase.vdb4;Mode=ExclusiveReadWrite;Pooling=false")

            ' Open the database
            conn.Open()

            ' Insert a new row into the database using parameterized data

            Dim sqlInsertCommand As String = 
              "INSERT INTO [MyTable](Data1, Data2) VALUES (@Data1, @Data2); SELECT
                  @@IDENTITY"
            
            Using cmd As VistaDBCommand = New VistaDBCommand(sqlInsertCommand, conn)
                ' Add the new command parameters and their name
                cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@data1"
                cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "@data2"
                Dim i As Integer
                ' Do this in a loop only changing the command parameter values per
                    loop
                For i = 0 To 30000
                    cmd.Parameters.Item("@Data1").Value = ("SQL
                        Entry: " & i.ToString)
                    cmd.Parameters.Item("@Data2").Value = DateTime.Now.Millisecond
                    cmd.ExecuteReader()
                Next i
            End Using
        End Using

        sw.Stop()
        Console.WriteLine(("SQL Insert : " & sw.ElapsedMilliseconds & " ms "))

    End Sub
    

Sample VB.NET DDA Code

This portion of the code is the same sample, but written for Direct Data Access (DDA) instead.  

In DDA you must first tell the table you are adding a new record with an insert operation, then you modify the columns, and the Post the new record to the database. Column data does not have to be quoted or passed by a parameterized query because DDA always handles .Net datatypes natively.

Public Sub RunDemoInsertDDA()
        Dim sw As New Stopwatch
        sw.Start()

        ' This interface requires Importing the VistaDB.DDA namespace
        ' Nothing for the password means the database is not encrypted
        Using db As IVistaDBDatabase = 

         VistaDBEngine.Connections.OpenDDA.OpenDatabase(
            "NewDatabase.vdb4",         
            VistaDB.VistaDBDatabaseOpenMode.ExclusiveReadWrite, 
            Nothing)
            ' Open the table
            Using table As IVistaDBTable = db.OpenTable("MyTable", True, False)
                Dim i As Integer
                For i = 0 To 30000
                    ' Insert a new record
                    table.Insert()
                    ' Add the values to the columns
                    table.PutString("data1", ("Entry: " & i.ToString))
                    table.PutInt32("data2", DateTime.Now.Millisecond)
                    ' Post the new record to the table
                    table.Post()
                Next i
            End Using
        End Using

        sw.Stop()
        Console.WriteLine(("DDA Insert : " & sw.ElapsedMilliseconds & " ms "))
    End Sub

C# Insert Sample

You may also want to view the C# insert rows sample also