ADO With VB Express: The Structured Query Language

	Introduction to SQL

The Structured Query Language

Introduction

The Structured Query Language, abbreviated SQL, is a universal language used to create and managed computer databases. It is used in all popular database environments, including Microsoft SQL Server, Oracle, Borland/Corel Paradox, Microsoft Access, etc.

SQL can be pronounced Sequel or S. Q. L. On this site, we will consider the Sequel pronunciation.

A SQL Statement

When using SQL, you write a relatively short sections of code and view its result. Code based on SQL is referred to as a SQL statement. When writing an expression, SQL is not case-sensitive. This means that Case, case, and CASE represent the same word. This applies to keywords of the SQL or words that you will add in your expressions.

The most fundamental operator used in the SQL is called SELECT. This operator is primarily used to display a value to the user. In this simple case, it uses the following formula:

SELECT Value;

The value on the right side of SELECT must be appropriate. The value to select can be a number. Here is an example:

SELECT 48;

The value can also be a string or else. In most cases, you will be selecting one or more columns of a table.

After creating the SELECT expression, you can pass it as the first argument to the RecordsetClass.Open() method.

Column Selection

Introduction

When creating a recordset, you can use the whole table, including all of its columns. An alternative is to select only one or more columns from a table. In this case, the formula used on the SELECT operator is:

SELECT What FROM WhatObject;

The What factor of our syntax is the name of the column(s) of a table. The WhatObject factor can be the name of a table. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

            ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.RecordsetClass = New ADODB.Recordset

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

              "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT LastName FROM Persons;", conADO)



        rstPeople.Close()

End Sub

To consider more than one column in a statement, you can list them in the What factor of our formula, separating them with a comma except for the last column. The syntax you would use is:

SELECT Column1, Column2, Column_n FROM WhatObject;

Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

            ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.RecordsetClass = New ADODB.Recordset

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

              "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT FirstName, LastName, Gender FROM Persons;", conADO)



        rstPeople.Close()

End Sub

In the previous lesson, we saw that, to select everything, that is, all columns, from a table, you could pass the name of the column as the first argument to the RecordsetClass.Open() method. To get the same effect, you can use the asterisk in place of the What factor of our formula. This would be done as follows:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

            ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.RecordsetClass = New ADODB.Recordset

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

              "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT * FROM Persons;", conADO)



        rstPeople.Close()

End Sub

SELECT This AS That

If you create a SELECT statement that specifies the name or names of columns, the name of each column is used to represent it. If you want, you can specify a different string, sometimes named a caption, that would represent the column. To do this, the formula to use is:

SELECT Value As Caption;

The words SELECT and AS are required. As mentioned already, SELECT would be used to specify a value and AS in this case allows you to specify a caption of your choice. The caption can be made of a word but the word cannot be one of the SQL's keywords. If the Caption is made of more than one word, you can include between an opening and a closing square brackets. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

            ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.RecordsetClass

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        

        rstPeople = New ADODB.Recordset

        Dim fldEach As ADODB.Field



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

              "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

        rstPeople.Open("SELECT LastName As [Last Name] FROM Persons;", _

                        conADO)



        For Each fldEach In rstPeople.Fields

            MsgBox(fldEach.Value)

        Next



        rstPeople.Close()

End Sub

In the same way, you can apply the AS keyword to as many columns as you want by separating them with commas. Here is an example:

Private Sub btnRecordset_Click(ByVal sender As System.Object, _

            ByVal e As System.EventArgs) Handles btnRecordset.Click

        Dim rstPeople As ADODB.RecordsetClass

        Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass

        

        rstPeople = New ADODB.Recordset

        Dim fldEach As ADODB.Field



        conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

              "Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)

   rstPeople.Open("SELECT LastName AS [Last Name], Gender AS Sex FROM Persons;", _

                        conADO)



        For Each fldEach In rstPeople.Fields

            MsgBox(fldEach.Value)

        Next



        rstPeople.Close()

End Sub

In the same way, you can mix number-based and string-based columns.


Home	Copyright © 2005-2016, FunctionX	Next