Skip to content

Add Edit Delete in VB.Net Programming

  • by

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

SELECT column1, column2, column3, ..., columnN
FROM table_name
WHERE condition;

VB.Net SqlCommand

cmd.CommandText = "SELECT * FROM table_name WHERE condition"
cmd.ExecuteReader()

2. SQL INSERT Statement

Addition, creation, or insertion of a new table record

INSERT INTO table_name (column1, column2, column3, ..., columnN)
VALUES (value1, value2, value3, ..., valueN);

VB.Net SqlCommand

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

UPDATE table_name
SET column1 = value1, column2 = value2, column3 = value3, ..., columnN = valueN 
WHERE condition;

VB.Net SqlCommand

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

DELETE FROM table_name WHERE condition;

VB.Net SqlCommand

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

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.

conn.Open()
Dim reader As SqlDataReader
reader = cmd.ExecuteReader
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

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

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



'
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

References on Add Edit Delete in VB.Net Programming

CRUD on Wikipedia

Leave a Reply

Your email address will not be published. Required fields are marked *