Creating a Database Application using ADO Control

Introduction to ADO Database Applications

In Visual Basic 6, we can build powerful database applications using ADO (ActiveX Data Objects) control. Unlike the standard data control, ADO offers greater flexibility and can work with various data sources beyond traditional databases.

Flexible Connectivity

Connect to various data sources including databases, email, and web data

Cross-Platform

Works across different platforms and programming languages

High Performance

Efficient data access and manipulation capabilities

To use ADO data control in VB6, you need to insert it into the toolbox by pressing Ctrl+T and selecting "Microsoft ActiveX Data Control 6".

The Application Interface

Our database application allows users to manage book titles with the following capabilities:

Browse book records
Add new books
Update existing records
Delete books
Navigate between records

ADO Database Application Interface

Interactive Demo

Try out the functionality of our database application-An Electronic Library:

Title:

Year Published:

ISBN:

Publisher ID:

Subject:

VB6 Implementation with ADO Control

In VB6, we use the ADO Data Control to connect to our database. Here's how to set it up:

Connecting to the Database

Add the ADO Data Control to your form
Set the ConnectionString property to connect to your database
Set the RecordSource property to specify the table or query
Bind textboxes to the ADO Data Control

Save Button Code

Private Sub cmdSave_Click()
    ' Update record with form values
    adoBooks.Recordset.Fields("Title") = txtTitle.Text
    adoBooks.Recordset.Fields("Year Published") = txtPub.Text
    adoBooks.Recordset.Fields("ISBN") = txtISBN.Text
    adoBooks.Recordset.Fields("PubID") = txtPubID.Text
    adoBooks.Recordset.Fields("Subject") = txtSubject.Text
    
    ' Save changes to database
    adoBooks.Recordset.Update
    
    ' Show confirmation message
    MsgBox "Record saved successfully!", vbInformation
End Sub

Add Button Code

Private Sub cmdAdd_Click()
    ' Add a new blank record
    adoBooks.Recordset.AddNew
    
    ' Clear the form for new data entry
    txtTitle.Text = ""
    txtPub.Text = ""
    txtISBN.Text = ""
    txtPubID.Text = ""
    txtSubject.Text = ""
    
    ' Set focus to first field
    txtTitle.SetFocus
End Sub

Delete Button Code

Private Sub cmdDelete_Click()
    ' Confirm deletion with user
    Dim Confirm As Integer
    Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Confirm Deletion")
    
    If Confirm = vbYes Then
        ' Delete current record
        adoBooks.Recordset.Delete
        
        ' Move to next record if available
        If Not adoBooks.Recordset.EOF Then
            adoBooks.Recordset.MoveNext
        ElseIf Not adoBooks.Recordset.BOF Then
            adoBooks.Recordset.MovePrevious
        End If
        
        ' Show confirmation
        MsgBox "Record deleted!", vbInformation
    Else
        MsgBox "Deletion canceled", vbExclamation
    End If
End Sub

Navigation Buttons

' Previous Record Button
Private Sub cmdPrev_Click()
    If Not adoBooks.Recordset.BOF Then
        adoBooks.Recordset.MovePrevious
        If adoBooks.Recordset.BOF Then
            adoBooks.Recordset.MoveFirst
        End If
    End If
End Sub

' Next Record Button
Private Sub cmdNext_Click()
    If Not adoBooks.Recordset.EOF Then
        adoBooks.Recordset.MoveNext
        If adoBooks.Recordset.EOF Then
            adoBooks.Recordset.MoveLast
        End If
    End If
End Sub

VB.NET Implementation with ADO.NET

In VB.NET, we use ADO.NET which provides a more robust and flexible approach to database access compared to VB6's ADO control.

Key Differences

Feature	VB6 ADO	VB.NET ADO.NET
Architecture	Connected model	Disconnected model
Primary Components	Connection, Command, Recordset	Connection, Command, DataAdapter, DataSet
Data Binding	Simple binding to controls	Complex binding to DataGridView, etc.
Performance	Good for small datasets	Better for large datasets

VB.NET Code Example

Imports System.Data.SqlClient

Public Class BookForm
    Private connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=BIBLIO;Integrated Security=True"
    Private currentPosition As Integer = 0
    Private booksTable As New DataTable()
    
    ' Form Load - Load data
    Private Sub BookForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadBooksData()
        DisplayCurrentRecord()
    End Sub
    
    ' Load data from database
    Private Sub LoadBooksData()
        Using connection As New SqlConnection(connectionString)
            Dim query As String = "SELECT * FROM Titles"
            Using adapter As New SqlDataAdapter(query, connection)
                adapter.Fill(booksTable)
            End Using
        End Using
    End Sub
    
    ' Display current record
    Private Sub DisplayCurrentRecord()
        If booksTable.Rows.Count > 0 Then
            Dim row As DataRow = booksTable.Rows(currentPosition)
            txtTitle.Text = row("Title").ToString()
            txtYear.Text = row("Year_Published").ToString()
            txtISBN.Text = row("ISBN").ToString()
            txtPubID.Text = row("PubID").ToString()
            txtSubject.Text = row("Subject").ToString()
        End If
    End Sub
    
    ' Save button
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If booksTable.Rows.Count > 0 Then
            Dim row As DataRow = booksTable.Rows(currentPosition)
            row.BeginEdit()
            row("Title") = txtTitle.Text
            row("Year_Published") = txtYear.Text
            row("ISBN") = txtISBN.Text
            row("PubID") = txtPubID.Text
            row("Subject") = txtSubject.Text
            row.EndEdit()
            
            ' Update database
            Using connection As New SqlConnection(connectionString)
                Dim query As String = "SELECT * FROM Titles"
                Using adapter As New SqlDataAdapter(query, connection)
                    Dim builder As New SqlCommandBuilder(adapter)
                    adapter.Update(booksTable)
                End Using
            End Using
            
            MessageBox.Show("Record saved successfully!")
        End If
    End Sub
    
    ' Navigation buttons (similar to VB6 but using DataTable)
    Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click
        If currentPosition > 0 Then
            currentPosition -= 1
            DisplayCurrentRecord()
        End If
    End Sub
    
    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If currentPosition < booksTable.Rows.Count - 1 Then
            currentPosition += 1
            DisplayCurrentRecord()
        End If
    End Sub
    
    ' Other methods similar to VB6 implementation
End Class

Best Practices for Database Applications

Security

Use parameterized queries to prevent SQL injection attacks

Performance

Retrieve only necessary data and close connections promptly

Error Handling

Implement robust error handling for database operations

Concurrency

Handle concurrent data access with appropriate locking strategies

Exercise: Enhance the Application

Try adding these features to the application:

Add search functionality to find books by title or ISBN
Implement data validation to ensure all required fields are entered
Add a status bar showing current record position and total records
Create a report generation feature to export data to PDF or Excel

Creating an Electronic Library using ADO Control