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 |