How to make a simple CRUD (Create,Retrieve,Update,Delete) in VB.NET

1. Create database in MS SQL.

2. Add new table and create necessary columns. Then save.

3. Create a new project in visual studio and choose "windows form applications".

4. Add new module. We will put here the public connection string of our simple CRUDE program.

5. Place the code below.

'NAMESPACES FOR SQL

Imports System.Data.Sql

Imports System.Data.SqlClient

Imports System.Data

Module Module1

    'PUBLIC VARIABLES FOR SERVERNAME,DATABASENAME,CONNECTION STRING AND ETC.

    Public SERVER_NAME As String

    Public DATABASE_NAME As String

    Public SQL As String

    Public CON As New SqlConnection()

    Public SQLCMD As New SqlCommand()

    Public SQLADAPTER As New SqlDataAdapter

    Public DR As System.Data.SqlClient.SqlDataReader

    Public Sub MAIN()

        SERVER_NAME = "SAMSUNG-PC"  'INSTANCE NAME IN MSSQL

        DATABASE_NAME = "CRUDE"     'DATABASE NAME

        CON.ConnectionString = "DATA SOURCE=" + SERVER_NAME + ";" + "INITIAL CATALOG=" + DATABASE_NAME + ";" + "INTEGRATED SECURITY = TRUE;" 'CONNECTION STRING

        CON.Open()

    End Sub

End Module

6. Add four labels and four text box. (txtId, txtFname, txtMname, txtLname)

7. Add five buttons (btnAdd, btnEdit, btnDelete, btnSave, btnCancel) and one listview (lstvwInfo) with the corresponding column headers.

8. Add the codes below for each button.

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        Panel1.Enabled = True

        btnAdd.Enabled = False

        btnEdit.Enabled = False

        btnDel.Enabled = False

        btnSave.Enabled = True

        btnCancel.Enabled = True

        txtFname.Focus()

    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click

        Panel1.Enabled = True

        btnAdd.Enabled = False

        btnEdit.Enabled = False

        btnDel.Enabled = False

        btnSave.Enabled = True

        btnCancel.Enabled = True

        txtFname.Focus()

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click

        Panel1.Enabled = False

        btnAdd.Enabled = True

        btnEdit.Enabled = True

        btnDel.Enabled = True

        btnSave.Enabled = False

        btnCancel.Enabled = False

    End Sub

9. Add the three namespaces below at the top of the class.

Imports System.Data.Sql
Imports System.Data.SqlClient

Imports System.Data

10. Declare public variable AddOrEdit.

Dim AddOrEdit As String = ""

11. Add a function storevariables. (This will serve later as the code for passing the selected data from listview to textbox)


    Function storevariables(ByVal sqlquery As String)
        DR.Close()
        SQLCMD = New SqlCommand(sqlquery, CON)
        DR = SQLCMD.ExecuteReader()

        If DR.Read = False Then
            storevariables = ""
            Exit Function
        Else
            txtId.Text = DR("ID")
            txtLname.Text = DR("LASTNAME")
            txtFname.Text = DR("FIRSTNAME")
            txtMname.Text = DR("MIDDLENAME")
            storevariables = ""
        End If
    End Function

12. Add a sub procedure LoadLstItems. (This will load the data from database to listview.)



To be continued...