Visual Basic and SQL Server – Inserting Data

In order to insert data into an SQL Server database, the SQL ‘Insert’ statement needs to be used. The following example inserts a record into the same ‘person’ table that was used in the example for selecting data.

Inserting data works in a similar fashion as selecting data using parameters. Firstly, a connection to the database is established, then the query parameters are defined as variables, which are bound in to the following SQL statement. The SQL statement is then executed and a confirmation message is displayed. The declaration and execution of the SQL statement is wrapped in a ‘Try-Catch-Finally’ block to catch any errors that may arise and close the database connection at the end.

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
    "User Id=DemoUN; Password=DemoPW")

Try

    ' Connect to database.
    connect.Open()

Catch ex As Exception

    ' Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.")
    End

End Try

Try

    ' Query parameters.
    Dim firstname As String = "Fiona"
    Dim lastname As String = "Jones"
    Dim title As String = "Miss"
    Dim dob As Date = "1985-05-19"

    ' Query text.
    Dim sqlText As String =
        "INSERT INTO person " &
        "       (firstname, lastname, title, dob) " &
        "VALUES (@firstname, @lastname, @title, @dob)"

    ' Query text incorporated into SQL command.
    Dim sqlInsert As New SqlCommand(sqlText, connect)

    ' Bind the parameters to the query.
    sqlInsert.Parameters.AddWithValue("@firstname", firstname)
    sqlInsert.Parameters.AddWithValue("@lastname", lastname)
    sqlInsert.Parameters.AddWithValue("@title", title)
    sqlInsert.Parameters.AddWithValue("@dob", dob)

    ' Execute SQL.
    sqlInsert.ExecuteNonQuery()

    ' Confirm successful addition of person information.
    Console.WriteLine("Person information added successfully.")

Catch ex As Exception

    ' Confirm error adding person information and exit.
    Console.WriteLine("Error adding person information.")
    End

Finally

    ' Close the database connection.
    connect.Close()

End Try

The contents of the ‘person’ table now looks as follows.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20
5 Fiona Jones Miss 1985-05-19