Lesson 27: SQL Queries in VB6

Learn to retrieve and filter database records efficiently using SQL queries in Visual Basic 6

Previous Lesson Next Lesson

Key Takeaway

SQL queries enable precise retrieval of database information, allowing you to filter and select specific data based on defined criteria using keywords like SELECT and FROM.

Welcome to Lesson 27 of our Visual Basic 6 Tutorial! In this lesson, you'll learn how to use SQL queries to efficiently retrieve and filter database records in VB6. SQL (Structured Query Language) allows you to select specific information from databases based on defined criteria.

27.1 Introduction to SQL Queries

SQL is the standard language for interacting with relational databases. In VB6, we use SQL with ADO controls to:

Filter Data

Retrieve specific records based on conditions

Select Columns

Choose which fields to display from a table

Sort Records

Organize results in ascending or descending order

27.1.1 Basic SQL Syntax

The most fundamental SQL keywords are SELECT and FROM:

SQLSyntax.frm
-- Select specific fields
SELECT field1, field2 FROM TableName

-- Select all fields
SELECT * FROM TableName

27.2 Building a Book Database Application

We'll create a book database application to demonstrate SQL capabilities. Create a Microsoft Access database named books.mdb with a table named book containing these fields: ID, Title, Author, Year, ISBN, Publisher, Price.

Book Database Application Design
Figure 27.1: Database Application Design

27.2.1 Designing the Interface

After adding the ADO control and DataGrid to your form:

Design Interface
Figure 27.2: Design Interface
Runtime Interface
Figure 27.3: Runtime Interface

27.3 Connecting to Database with SQL

To connect ADO to the database with SQL:

1Set ConnectionString

Right-click ADO control, open properties, and set ConnectionString

2Set RecordSource

Select Command Type: 1-adCmdText and enter SQL query: SELECT * FROM book

3Set DataGrid

Configure DataGrid properties and set DataSource to ADO control

ADO Properties
Figure 27.4: ADO Properties
SQL Query in Action
Figure 27.5: SQL Query in Action

27.4 Implementing SQL Queries

We'll add buttons to execute different SQL queries:

SQLQueries.frm
Private Sub cmdAuthor_Click()
    Adodc1.RecordSource = "SELECT Author FROM book"
    Adodc1.Refresh
    Adodc1.Caption = Adodc1.RecordSource
End Sub

Private Sub cmdTitle_Click()
    Adodc1.RecordSource = "SELECT Title FROM book"
    Adodc1.Refresh
    Adodc1.Caption = Adodc1.RecordSource
End Sub

Private Sub cmdAll_Click()
    Adodc1.RecordSource = "SELECT * FROM book"
    Adodc1.Refresh
    Adodc1.Caption = Adodc1.RecordSource
End Sub

27.4.1 Query Results

When you run the program and click on different buttons:

Author Query Result
Figure 27.6: Author Query Result
Title Query Result
Figure 27.7: Title Query Result
All Fields Query Result
Figure 27.8: All Fields Query Result

Lesson Summary

In this lesson, you've learned how to use SQL queries in VB6 to retrieve specific database information:

SQL Fundamentals

Using SELECT and FROM to retrieve specific data

Database Connection

Configuring ADO control with SQL queries

Dynamic Queries

Changing RecordSource property at runtime

Data Filtering

Displaying specific fields from database tables

Important Note

SQL queries provide precise control over which data is retrieved from your database, making your applications more efficient and user-friendly.

Practice Exercises

Enhance your SQL skills with these exercises:

Exercise 1: Price Filter

Add a button to display books with prices less than $20

Exercise 2: Author Search

Create a search feature to find books by specific authors

Exercise 3: Year Range

Implement a query to show books published between 2000 and 2010

Exercise 4: Combined Fields

Display Title and Author in a single column as "Title by Author"

Exercise 5: Sorting

Add sorting options (ascending/descending) by Title and Year

Next Lesson

Continue your VB6 journey with Lesson 28: Advanced SQL Queries.

Related Resources

DataGrid Control

Review DataGrid usage from Lesson 26

Previous Lesson

Advanced SQL

Learn advanced SQL queries in VB6

Next Lesson

ADO Control

Review ADO database connection from Lesson 25

View Lesson

SQL Sample Code

Practical VB6 SQL code samples

View Examples
Visual Basic 6 Made Easy

Visual Basic 6 Made Easy

by Dr. Liew Voon Kiong

The ultimate beginner-friendly guide for mastering Windows-based application development using Visual Basic 6. Used as a textbook by universities worldwide.

What You'll Learn:

  • Comprehensive coverage of VB6 coding techniques
  • Database application development
  • Practical examples and projects
  • Debugging and error handling
  • Advanced database integration
  • Professional UI development
Get on Amazon
Visual Basic 2022 Made Easy

Visual Basic 2022 Made Easy

by Dr. Liew Voon Kiong

The ultimate guide to VB.NET programming in Visual Studio 2022. Master modern VB development with this comprehensive resource.

What You'll Learn:

  • Modern VB.NET coding techniques
  • Visual Studio 2022 features
  • Database programming with ADO.NET
  • Advanced UI development
  • Entity Framework integration
  • Deployment strategies
Get on Amazon