Add, edit, and delete are fundamental database operations which can be implemented in VB.Net in multitude of ways. They are usually collectively referred to as CRUD (Create, Read, Update and Delete). In this article, we are going to look at the different ways of accomplishing CRUD in VB.Net (Visual Basic).
One of the straightforward way of adding, editing, and deleting records in a database table is thru the direct execution of SQL commands. That is, to add a record to an existing table, the SQL command INSERT is used. Likewise, to edit a record, the SQL command UPDATE is executed. For record deletion, the SQL command is DELETE. Finally for searching and reading of records, the SQL command SELECT is used.
SQL Commands for Reading, Adding, Editing, and Deleting Records
1. SQL SELECT Statement
Reading or retrieval of a table record or set of records
1 2 3 | SELECT column1, column2, column3, ..., columnN FROM table_name WHERE condition; |
VB.Net SqlCommand
1 2 | cmd.CommandText = "SELECT * FROM table_name WHERE condition" cmd.ExecuteReader() |
2. SQL INSERT Statement
Addition, creation, or insertion of a new table record
1 2 | INSERT INTO table_name (column1, column2, column3, ..., columnN) VALUES (value1, value2, value3, ..., valueN); |
VB.Net SqlCommand
1 2 3 | cmd.CommandText = "INSERT INTO table_name (column1, column2, column3, ..., columnN) VALUES (value1, value2, value3, ..., valueN) WHERE condition" cmd.ExecuteNonQuery() |
3. SQL UPDATE Statement
Editing or updating a table record
1 2 3 | UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3, ..., columnN = valueN WHERE condition; |
VB.Net SqlCommand
1 2 3 | cmd.CommandText = "UPDATE table_name SET column1 = value1, column2 = value2, column3 = value3, ..., columnN = valueN" cmd.ExecuteNonQuery |
4. SQL DELETE Statement
Delete a table record or set of records
1 | DELETE FROM table_name WHERE condition; |
VB.Net SqlCommand
1 2 | cmd.CommandText = "DELETE FROM table_name WHERE condition" cmd.ExecuteNonQuery |
We can execute SQL commands in VB.Net by using an SqlCommand object. However, an SqlCommand object needs an SqlConnection object. Also, we need to provide the SqlConnection object with a connection string. The connection string specifies how to connect to the database.
Sample Add, Edit, Delete Program in VB.Net
To illustrate the use of the different SQL commands, let us first create a simple SQL database. For this tutorial, I am using a Visual Studio 2019 Community Edition. In order to work with SQL databases in VS2019, you must install the Data Storage and Processing toolset. If you did not install it when you installed your VS2019, re-run the installer. Put a checkmark on Data Storage and Processing under the Other Toolsets list.
You may install an SQL server if you want. There are two free editions of Microsoft SQL Server that you can use. These are the Developer and Express editions. They can be downloaded here.
Notes on Visual Studio Project Types
Windows Forms App versus Windows Forms App (.Net Framework)
If you are new to Visual Studio, choosing a project type can be confusing. Take a look at the screenshot below. Both Windows Forms App and Windows Forms App (.Net Framework) are used for creating WinForms. Both types also mention “.Net”. So, what’s the difference?
Windows Forms App (.Net Framework) is for creating apps targeting .Net Framework. Whereas the plain Windows Forms App is for creating apps that use the .Net Core. These will become clear after you select the project type.
.Net Framework is the old and mature .Net that runs on the Windows platform. In contrast, .Net Core is the new .Net that is cross-platform.
Read
1 2 3 4 5 6 7 8 9 | Dim connStr As String = "Data Source=localhost\SQLEXPRESS; Initial Catalog=myDatabase; Integrated Security=True; Pooling=False" Dim conn As New SqlConnection(connStr) Dim cmd As SqlCommand cmd = conn.CreateCommand cmd.CommandText = "Select * from Employees" |
After prepping the SqlCommand object as shown above, the actual execution of the SqlCommand depends on the type of the command. In this case, because it is a SELECT command, we expect the command to return a set of values. Therefore, we need an object to receive the results.
1 2 3 | conn.Open() Dim reader As SqlDataReader reader = cmd.ExecuteReader |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | Imports System.Data.SqlClient Public Class Form1 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim connStr As String = "Data Source=localhost\SQLEXPRESS; Initial Catalog=myDatabase; Integrated Security=True; Pooling=False" Dim conn As New SqlConnection(connStr) Dim cmd As SqlCommand cmd = conn.CreateCommand cmd.CommandText = "Select * from Employees" conn.Open() Dim dt As New DataTable dt.Load(cmd.ExecuteReader()) conn.Close() DataGridView1.DataSource = dt End Sub End Class |
ADD
CREATE – or INSERT a record into a table. The SQL command starts with the keywords INSERT INTO. It is followed with the name of the table. Then, the column names are specified inside a pair of parentheses. Finally, the corresponding values for the columns are enumerated, again, inside a pair of parentheses.
SQL INSERT COMMAND
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | Imports System.Data.SqlClient Public Class frmAdd Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click Dim connStr As String = "Data Source=localhost\SQLEXPRESS; Initial Catalog=myDatabase; Integrated Security=True; Pooling=False" Dim conn As New SqlConnection(connStr) Dim cmd As SqlCommand 'DATA VALIDATION 'Check if all required fields are filled If Trim(txtEmployeeID.Text) = "" Or Trim(txtEmployeeName.Text) = "" Or Trim(txtAge.Text) = "" Then MsgBox("Please fill all fields", vbExclamation, "Add Record Error") Exit Sub End If 'Check if EmployeeID is numeric If Not IsNumeric(txtEmployeeID.Text) Then MsgBox("Please enter a numeric value for Employee ID", vbExclamation, "Add Record Error") Exit Sub End If 'Check if Age is numeric If Not IsNumeric(txtAge.Text) Then MsgBox("Please enter a numeric value for Age", vbExclamation, "Add Record Error") Exit Sub End If 'Check if EmployeeID is already in use cmd = conn.CreateCommand cmd.CommandText = "SELECT * FROM Employees WHERE EmployeeID = '" & txtEmployeeID.Text & "'" conn.Open() Dim reader As SqlDataReader reader = cmd.ExecuteReader() If reader.HasRows Then MsgBox("Employee ID already exist", vbExclamation, "Add Record Error") reader.Close() conn.Close() Exit Sub End If reader.Close() 'SAVE RECORD cmd.CommandText = "INSERT INTO Employees (EmployeeID, EmployeeName, Age) VALUES (" & txtEmployeeID.Text & "," & txtEmployeeName.Text & "," & txtAge.Text & ")" Dim result As Integer result = cmd.ExecuteNonQuery() If result > 0 Then MsgBox("Record successfully saved", vbInformation, "Add Record Info") Else MsgBox("Record save failed", vbCritical, "Add Record Error") End If reader.Close() conn.Close() End Sub End Class |
READ – or retrieve a record or a set of records. The keyword for the SQL command is SELECT. The column names are then enumerated. Then, the keyword FROM followed by the table name. As an option, a condition clause can be added to the command.
VB.Net Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | Imports System.Data.SqlClient Public Class frmRead Private Sub frmRead_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim connStr As String = "Data Source=localhost\SQLEXPRESS; Initial Catalog=myDatabase; Integrated Security=True; Pooling=False" Dim conn As New SqlConnection(connStr) Dim cmd As SqlCommand Dim dt As New DataTable cmd = conn.CreateCommand cmd.CommandText = "Select * from Employees" conn.Open() dt.Load(cmd.ExecuteReader) conn.Close() Dim drow As DataRow = dt.Rows(0) txtEmployeeID.Text = drow(0) txtEmployeeName.Text = drow(1) txtAge.Text = drow(2) DataGridView1.DataSource = dt End Sub End Class |
UPDATE
DELETE
1 2 3 4 5 | ' Private Sub dgv_RowsAdded(sender As Object, e As DataGridViewRowsAddedEventArgs) Handles dgv.RowsAdded rowIndex = e.RowIndex rowCount = e.RowCount End Sub |
Creating a Database
Project–>Add New Item–>Service-based Database–>Click Add button
In the Solution Explorer, a file name Database1.mdf will appear. On the Server Explorer, the same file will appear under Data Connections
Rename the file into “StudentDatabase.mdf” on the Solution Explorer
Creating a Table
On the Server Explorer, expand the StudentDatabase.mdf, and right-click the Tables program to create a new Table
Opening a database.
PROBLEM WITH SQL QUERY WITH DATES, INPUTTED DATE FORMAT IS MODIFIED DURING SQL QUERY
FOR EXAMPLE INPUT 06/30/2021 BECOMES 30/06/2021, THEREBY GENERATING ERRORS
SOLUTION : CHANGE LANGUAGE SETTINGS TO US
EXECUTESCALAR CAUSING ERROR IF RESULT IS DBNULL
SOLUTION: MAKE THE RECEIVING VARIABLE AN OBJECT
dim result as long
result=cmd.executescalar ’causes error when result is dbnull or nothing
solution:
dim result as object
Related Articles on Add Edit Delete in VB.Net Programming
How to Install Esptool on Windows 10