Lesson 36: Editing Database Data in VB2019

Learn to add, edit, and delete database records using ADO.NET

Key Takeaway

Learn to navigate, edit, add, and delete database records using ADO.NET objects.

In this lesson, we'll build upon database connections from Lesson 35 to learn how to manipulate data in VB2019. You'll learn how to navigate through records, edit existing data, add new records, and delete unwanted data from your database applications.

Record Navigation

Browse through database records efficiently

Editing Records

Modify existing database records

Adding Records

Insert new records into the database

Deleting Records

Remove unwanted records from the database

36.1 Browsing Records

To navigate through records, we need to create command buttons that allow users to browse records forward and backward, as well as jump to the first and last records.

Figure 36.1: Record navigation buttons in VB2019

Navigation Button Code

Here's the code for each navigation button:

Button	Description	Code
First Record (<<)	Moves to the first record	MyRowPosition = 0 Me.showRecords()
Previous Record (<)	Moves to the previous record	If MyRowPosition > 0 Then MyRowPosition = MyRowPosition - 1 Me.showRecords() End If
Next Record (>)	Moves to the next record	If MyRowPosition < (MyDataTbl.Rows.Count - 1) Then MyRowPosition = MyRowPosition + 1 Me.showRecords() End If
Last Record (>>)	Moves to the last record	If MyDataTbl.Rows.Count > 0 Then MyRowPosition = MyDataTbl.Rows.Count - 1 Me.showRecords() End If

36.2 Editing, Saving, Adding and Deleting Records

Beyond browsing records, you'll need to manipulate data by editing existing records, adding new ones, and deleting unwanted records.

Saving Edited Records

After editing data in text boxes, use the Update method of SqlDataAdapter to save changes:

Saving Edited Records

Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
    If MyDataTbl.Rows.Count <> 0 Then
        MyDataTbl.Rows(MyRowPosition)("ContactName") = TxtName.Text
        MyDataTbl.Rows(MyRowPosition)("state") = TxtState.Text
        MyDatAdp.Update(MyDataTbl)
    End If
End Sub

Adding New Records

To add a new record, create a new DataRow and add it to the DataTable:

Adding New Records

Private Sub BtnAdd_Click(sender As Object, e As EventArgs) Handles BtnAdd.Click
    Dim MyNewRow As DataRow = MyDataTbl.NewRow()
    MyDataTbl.Rows.Add(MyNewRow)
    MyRowPosition = MyDataTbl.Rows.Count - 1
    showRecords()
End Sub

Deleting Records

To delete a record, mark it for deletion and update the database:

Deleting Records

Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click
    If MyDataTbl.Rows.Count <> 0 Then
        MyDataTbl.Rows(MyRowPosition).Delete()
        MyRowPosition = 0
        MyDatAdp.Update(MyDataTbl)
        showRecords()
    End If
End Sub

36.3 Complete Implementation

Here's the complete code for a database editor application:

Complete Database Editor Implementation

Public Class Form1
    Private MyDatAdp As New SqlDataAdapter
    Private MyCmdBld As New SqlCommandBuilder
    Private MyDataTbl As New DataTable
    Private MyCn As New SqlConnection
    Private MyRowPosition As Integer = 0
    
    Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        MyCn.Close()
        MyCn.Dispose()
    End Sub
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        MyCn.ConnectionString = "Data Source=TOSHIBA-PC\SQL2012; " & _
            "AttachDbFilename=C:\Program Files\Microsoft SQL Server\" & _
            "MSSQL11.SQL2012\MSSQL\DATA\Test.mdf; " & _
            "User Instance=True;Integrated Security=SSPI"
        MyCn.Open()
        
        MyDatAdp = New SqlDataAdapter("Select * from Contacts", MyCn)
        MyCmdBld = New SqlCommandBuilder(MyDatAdp)
        MyDatAdp.Fill(MyDataTbl)
        
        ' Display first record
        Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
        TxtName.Text = MyDataRow("ContactName").ToString()
        TxtState.Text = MyDataRow("State").ToString()
        
        showRecords()
    End Sub
    
    Private Sub showRecords()
        If MyDataTbl.Rows.Count = 0 Then
            TxtName.Text = ""
            TxtState.Text = ""
            Exit Sub
        End If
        
        TxtName.Text = MyDataTbl.Rows(MyRowPosition)("ContactName").ToString()
        TxtState.Text = MyDataTbl.Rows(MyRowPosition)("State").ToString()
    End Sub
    
    Private Sub BtnMoveFirst_Click(sender As Object, e As EventArgs) Handles BtnMoveFirst.Click
        MyRowPosition = 0
        showRecords()
    End Sub
    
    Private Sub BtnMovePrev_Click(sender As Object, e As EventArgs) Handles BtnMovePrev.Click
        If MyRowPosition > 0 Then
            MyRowPosition = MyRowPosition - 1
            showRecords()
        End If
    End Sub
    
    Private Sub BtnMoveNext_Click(sender As Object, e As EventArgs) Handles BtnMoveNext.Click
        If MyRowPosition < (MyDataTbl.Rows.Count - 1) Then
            MyRowPosition = MyRowPosition + 1
            showRecords()
        End If
    End Sub
    
    Private Sub BtnMoveLast_Click(sender As Object, e As EventArgs) Handles BtnMoveLast.Click
        If MyDataTbl.Rows.Count > 0 Then
            MyRowPosition = MyDataTbl.Rows.Count - 1
            showRecords()
        End If
    End Sub
    
    Private Sub BtnAdd_Click(sender As Object, e As EventArgs) Handles BtnAdd.Click
        Dim MyNewRow As DataRow = MyDataTbl.NewRow()
        MyDataTbl.Rows.Add(MyNewRow)
        MyRowPosition = MyDataTbl.Rows.Count - 1
        showRecords()
    End Sub
    
    Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click
        If MyDataTbl.Rows.Count <> 0 Then
            MyDataTbl.Rows(MyRowPosition).Delete()
            MyRowPosition = 0
            MyDatAdp.Update(MyDataTbl)
            showRecords()
        End If
    End Sub
    
    Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
        If MyDataTbl.Rows.Count <> 0 Then
            MyDataTbl.Rows(MyRowPosition)("ContactName") = TxtName.Text
            MyDataTbl.Rows(MyRowPosition)("state") = TxtState.Text
            MyDatAdp.Update(MyDataTbl)
        End If
    End Sub
End Class

Figure 36.2: Complete database editor interface in VB2019

Lesson Summary

In this lesson, you've learned how to create a complete database editor application:

Record Navigation

Implemented navigation buttons to browse database records

Editing Records

Modified existing records and saved changes to the database

Adding Records

Created functionality to add new records to the database

Deleting Records

Implemented safe deletion of unwanted database records

You now have a complete database application that can perform CRUD (Create, Read, Update, Delete) operations.

Next Lesson

Learn to create console applications in Lesson 37: Console Applications Part 1.

Related Resources

VB2019 Database Guide

Comprehensive guide to database programming in VB2019

Explore Guide

Database Sample Code

Practical VB2019 code samples for database applications

View Examples

ADO.NET Documentation

Official Microsoft ADO.NET documentation

View Documentation

SQL Server Reference

Complete reference for SQL Server management

Learn More

Visual Basic 2019 Made Easy

by Dr. Liew Voon Kiong

Master Visual Basic 2019 with this comprehensive guide that includes detailed coverage of database programming techniques. Learn to create professional database applications.

Key Database Topics:

Database fundamentals and concepts
SQL Server setup and configuration
ADO.NET programming techniques
Data binding and manipulation
Practical database projects with source code

Get on Amazon

Database Programming with VB.NET

by Carsten Thomsen

This comprehensive guide focuses specifically on database programming techniques in VB.NET, covering everything from basic concepts to advanced techniques.

Database Coverage:

In-depth ADO.NET coverage
SQL Server integration
Entity Framework
Real-world database application examples

Get on Amazon