Visual Basic: передача переменных в SQL-запрос стандартом.

Начнем с простого.
Пусть у нас имеется база данных DataBase.mdb и пусть в ней присутствует таблица tblAdres, а в этой таблице присутствуют поля fieldCity, fieldStreet, fieldHouse, fieldSutname и fieldTelefon. Нам нужно выбрать адресатов, проживающих в г. Владивосток.

Dim strSQL As String
strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = Владивосток”
rst.Open strSQL ‘предполагается, что объекты db.ADODB и rst.ADODB уже объявлены.
Do Until rst.EOF ‘предполагается, что есть в наличии элемент управления ListBox1, куда мы будем передавать записи
ListBox1.AddItem rst.Fields(“fieldCity”) & “ “ & rst.Fields(“fieldStreet”) & “ “rst.Fields(“fieldHouse”) & “ “ & _
rst.Fields(“fieldSurname”) & “ “rst.Fields(“fieldTelefon”) & vbCrLf
rst.MoveNext
Loop

Обратите внимание, весь запрос помещен внутри двойных кавычек. Чтобы Jet «понял», что в запросе имеется переменная, ее необходимо вынести за пределы кавычек. В нашем случае это будет выглядеть так:

Dim strCity As String
strCity = “Владивосток”
strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & strCity
rst.Open strSQL

Данный запрос уже близок к рабочему, но при попытке его выполнить вы, скорее всего, получите сообщение об ошибке № 3061: Too few parameters. Expected 1. (Слишком мало параметров. Ожидалось 1). Дело в том, что спецификации SQL предписывают ограничивать одинарными кавычками передаваемый параметр. Джеффри П. Мак-Манус в своей книге [1] рекомендует использовать функцию, ограничивающую строковую переменную одинарными кавычками.

Private function Quote(strVariable As String) As String
Quote = “’” & strVariable & “’”
End function

Я тоже рекомендую использовать подобную функцию. Это особенно удобно, если вам необходимо передавать несколько строковый переменных в различные запросы. Итак, наш запрос примет вид:

strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & Quote(strCity)
rst.Open strSQL

Могу вас заверить, что эта комбинация будет работать, если вы, конечно, добавили в свой проект функцию Quote(). Но если вы не хотите этого делать, вам достаточно слегка «модернизировать» переменную strCity:

strCity = “Владивосток”
strCity = “’” & strCity & “’”
Dim strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & strCity
rst.Open strSQL

Теперь посмотрим, как передать в запрос несколько переменных. Предположим, нам надо выбрать записи по жителям города Владивосток, имеющих фамилию Иванов.

Dim strCity As String
Dim strSurname As String
strCity = “Владивосток”
strSurname = “Иванов”
strSQL = “SELECT * FROM [tblAdres] WHERE [fieldCity] = ” & Quote(strCity) & “ AND [fieldSurname] = “ & Quote(strSurname)

Строка запроса немного усложнилась, но структура осталась все та же. В подобных случаях целесообразно разбить строку на несколько частей, иначе целиком она не поместится на экране редактора, и работать с ней будет крайне неудобно. Я рекомендую разбить строку запроса так, чтобы каждая SQL-директива находилась на новой строке. Так будет легче редактировать строку и отслеживать в нем ошибки.

strSQL = “SELECT * FROM [tblAdres] “ & _
“ WHERE [fieldCity] = ” & Quote(strCity) & _
“ AND [fieldSurname] = “ & Quote(strSurname)
rst.Open strSQL

Обратите внимание на пробел между кавычками и директивой AND. При использовании переменных в запросе трудно заметить необходимость этого пробела, но без него вы можете получить сообщение об ошибке №3131: Syntax error in FROM clause (синтаксическая ошибка в пункте FROM). Действительно, если вы еще раз взглянете на строку запроса в предыдущем примере, то заметите, что без этого пробела директива AND «прилипнет» к переменной Quote(strCity), что и вызовет ошибку. Чтобы оградить себя от подобных недоразумений, я перед всеми SQL-директивами ставлю пробел.

Теперь посмотрим, как можно передать переменную в имя таблицы или имя поля. Ограничить их одинарными кавычками не получится — мы получим сообщение об ошибке №3450: Syntax error in query. Incomplete query clause (Синтаксическая ошибка в запросе. Неполная строка запроса). В этом случае, используя все туже конкатенацию необходимо «уложить» переменную в квадратные скобки. Пусть наша таблица называется [tbl Adres] и имеет поля [field Surname], [field City], [field Street] и [field House]. Допустим, нам необходимо выбрать адреса граждан, проживающих в городе, который укажет пользователь, и имеющих фамилию, которую укажет пользователь. Добавим в проект два текстовых поля: txtPeople и txtCity.

Dim strTable As String
Dim strFieldPeople As String
Dim strFieldCity As String
Dim strFieldStreet As String
Dim strFieldHouse As String
Dim strPeople As String
Dim strCity As String

strTable = “[tbl Adres]” ‘наименование таблицы
strTable = “[tbl Surnma]” ‘наименование поля таблицы ‘Фамилия’
strTable = “[tbl City]” ‘наименование поля таблицы ‘Город’
strTable = “[tbl Street]” ‘наименование поля таблицы ‘Улица’
strTable = “[tbl House]” ‘наименование поля таблицы ‘Дом’

strPeople = Trim(txtPeople.Text) ‘даем пользователю возможность ввести критерий выборки,
strCity = Trim(txtCity.text) ‘и сразу ‘обрезаем’ лишние пробелы

>strSQL = “SELECT “ & strFieldStreet & “, “ & strFieldHouse & _
“ FROM “ & strTable & _
“ WHERE “ & strFieldPeople & “ = “ & Quote(strPeople) & _
“ AND “ & strFieldCity & “ = “ & Quote(strCity)
rst.Open strSQL

Как вы, наверное, заметили, данный SQL-запрос полностью состоит из переменных (кроме директив самого запроса). К тому же в данном примере использовались названия таблиц, состоящие из нескольких слов. При такай структуре запроса количество слов в названии таблицы становится не существенным.

Рассмотрим еще несколько примеров. Допустим, у нас есть база данных, которая содержит таблицу tblOperations, которая в свою очередь имеет поля [fieldNumber], [fieldData], [fieldNameCompany] и [fieldSumm]. Первое поле содержит номер заказа, второе дату, третье имя компании а четвертое сумму операции. Допустим, вам необходимо дать пользователям возможность задавать критерии выборки по имени компании. Причем, если поле не заполнено, тогда recordset должен содержать полный набор записей, а если заполнено, тогда с критерием выборки (такой прием часто применяют при организации поиска в базе данных). В этом случае (и в более сложных так же) можно обойтись вполне одним SQL-запросом. Посмотрим, как это реализовать на практике:
«Нарисуем» в форме текстовое поле txtCompanyName.

Dim strCompanyName As String
If txtCompanyName <> Empty Then
strCompanyName = “= “ & Quote(txtCompanyName.Text)
Else
strCompanyName = “<> Null” ‘если вы конечно уверены, что в вашей базе нет пустых записей

End if
strSQL = “SELECT * & _
“ FRM tblOperations “ & _
“ WHERE [fieldNameCompany] “ & strCompanyName
rst.Open strSQL

Приведенный фрагмент кода выберет все записи таблицы, если поле txtCompanyName не заполнено. Но если его заполнить, тогда recordset будет выбирать записи по критерию совпадения с введенным в поле txtCompanyName значением. О том, как сделать выборку по дате мы поговорим чуть позже, а пока вернемся к приведенному примеру.
Обратите внимание, в запросе между директивой WHERE [fieldNameCompany] т переменной нет математического знака — мы вынесли его за пределы запроса и сделали частью переменной. Хочу также обратить ваше внимание на то, что процессор баз данных интерпритирует пустое поле таблицы как Null, а не как Empty. Поэтому использовать значение Empty при построении SQL-запроса нельзя.

В одном из примеров мы уже присваивали строковой переменной весь SQL-запрос, тоже самое можно сделать с любое его частью, даже той, которая включает директивы SQL. Вот как может выглядеть предыдущий пример:

Dim strCompanyName As String
If txtCompanyName <> Empty Then
strCompanyName = “ WHERE [fieldNameCompany] = “ & Quote(txtCompanyName.Text)
Else
strCompanyName = ““ WHERE [fieldNameCompany] <> Null “

>End if
strSQL = “SELECT * & _
“ FRM tblOperations “ & _
strCompanyName
rst.Open strSQL

Если вы обратили внимание, я постоянно упоминаю о строковых переменных. Дело в том, что кроме них в SQL-запрос можно передать только целочисленные переменные (byte, integer и long), а все остальные придется переводить в строковый тип. Отдельного внимания заслуживают переменные типа Data, но о них чуть позже. Давайте посмотрим, как проще всего перевести в строковый тип такие типы данные, как single, double и currency.
Для этого проще всего использовать встроенную в VB функцию преобразования с сроковый тип str(variable).

Dim curPrice As Currency
Dim strPrice As String
curPrice = 44.56
strPrice = str(strPrice) ‘ не забудьте «одеть» ее в одинарные кавычки

У вас также может появиться необходимость использовать в запросе переменную типа Boolean. Например, если необходимо сделать выборку данных по полю, содержащих логическое значение. Для этой цели можно воспользоваться функцией IIf.

Dim varBoolean As Boolean
varBoolean = True
strSQL = “SELECT * “ & _
“ FROM [tblAdres] “ & _
“ WHERE [fieldGood] = “ & IIf(varBoolean, “True”, “False”)
Если переменная содержит значение True, то функция вернет строковое значение “True”, и наоборот.

Передача переменной типа Date имеет свои нюансы. В конечном итоге ее тоже придется передавать как строковую переменную, только с учетом ее собственного формата. В SQL-запрос дата передается в следующем виде: #dd/mm/yy#, поэтому также нужно собрать и строковую переменную. Тут есть некоторое нюансы, которые вы должны учитывать. Во-первых, даже если в вашей операционной системе установлен формат даты с разделителем в виде точки (и соответственно, так же она отображается в полях вашей базы данных), формат передаваемой в SQL-запрос переменной все равно в качестве разделителя должен иметь слеш. Во-вторых, вы можете наткнуться на ситуацию, когда запрос с переменной в формате #dd/mm/yy# будет выводить неверные результаты. В этом случае вам необходимо воспользоваться американским форматом: #mm/dd/yy# (переставить местами месяц и день). Это приносим много неудобства, особенно, когда вы используете функции Date, Now и пр. для получения текущей даты, чтобы в последствии вставить эту переменную в SQL-запрос. Возможно, вам даже понадобится написать отдельную функцию, переводящую один формат в другой.

Dim strDate As String
strDate = “#08/27/2002#”
strSQL = “ SELECT * & _
“ FROM [tblOperations] “ & _
“ WHERE [fieldDate] = “ & strDate

Теперь поговорим о переменных byte, integer и long. Эти переменные необязательно обрамлять одинарными кавычками.
Допустим у нас имеется база данных с несколькими таблицами, имена которых «1», «2», «3», «4», «5». Нам нужно выбрать со всех таблиц данные и разнести по разным элементам отображения информации. Создадим пять экземпляров элемента управления ListBox с одинаковым названием и индексами от 0 до 4. Вот как будет выглядеть наш код:

Dim intTable As Integer
For intTable = 1 To 5
strSQL = “SELECT * & _
“ FROM “ & intTable
rst.Open strSQL

Do Until rst.EOF
ListBox(intTable – 1).AddItem rst.Fields(“Field1”) & “ “ & vbCrLf
rst.MoveNext
Loop
rst.Close
Next intTable

Этот код пять раз выберет набор записей и разнесет его по разным элементам управления. Тоже самое можно сделать и с именами таблицы, если в их названиях присутствуют последовательные целые числа. Если имена таблиц или полей таблицы более сложны, можно «собрать» их имя в строковую переменную, используя все ту же конкатенацию. Пусть в таблице [tbl1] имеются поля [field 1], [field 2] и [field 3]. Выборку из нее можно сделать, используя следующий код:

Dim intField As Integer
Dim strField As String

For intField = 1 To 3
strField = “[field” & intField & “]”
strSQL = “SELECT “ & Quote(strField) & _
“ FROM [tbl1]”

rst.Open strSQL

Do Until rst.EOF

ListBox(intTable – 1).AddItem rst.Fields(“Field1”) & “ “ & vbCrLf
rst.MoveNext
Loop
rst.Close
Next intField

Использование переменных существенно повышает гибкость программы. Вам достаточно написать одну процедуру с правильно спланированным запросом, и в последующем просто вызывать ее, передавая, как параметры переменные. Тем самым вы уменьшите свой код и повысите его «КПД» — продуктивность использования.
Данный обзор далеко не исчерпывающий (как и любая тема в программировании), но я надеюсь, он достаточен для эффективного решения возникающих проблем.

27.08.2002 г.

[1] Джеффри П. Мак-Манус «Обработка баз данных на Visual Basic 6» стр. 153

Вернуться к разделу статей

на отвлеченную тему