Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Access Новый топик    Ответить
 VBA. ADO. Запрос с параметрами к MS SQL.  [new]
VBA_Kiddy
Member

Откуда: Высокогорные районы Полярного Урала
Сообщений: 7
Здравствуйте! Помогите с решением проблемы.

Хочу разобраться как написать параметризованный запрос к MS SQL 2008 из VBA(Excel).
Добавляется строка в таблицу [my_table]
Процедура выдаёт ошибку на строке "oCmd.Execute": "Необходимо объявить скалярную переменную @TT_Param"

Как сделать правильно?

Sub SQL_temp()
Dim Conn As ADODB.Connection
Dim strConn As String 
Dim oCmd As ADODB.Command
Dim oPara As ADODB.Parameter

Set Conn = New ADODB.Connection
Conn.ConnectionString = _
"Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=My_Database;Data Source=(local);"
Conn.Open

Set oCmd = CreateObject("adodb.command")
oCmd.ActiveConnection = Conn.ConnectionString
oCmd.CommandText = "insert into my_table (TT) values(@TT_Param)"
Set oPara = oCmd.CreateParameter("@TT_param", adInteger, adParamInput)
oCmd.Parameters.Append oPara
oCmd.Parameters(0) = "123"

oCmd.Execute

Conn.Close
Set Conn = Nothing
End Sub
13 май 11, 21:13    [10650542]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
Guest33
Member

Откуда:
Сообщений: 2070
Set oCmd = CreateObject("adodb.command")
oCmd.ActiveConnection = Conn.ConnectionString
oCmd.CommandText = "declare @TT_Param varchar(3) set @TT_Param='123' insert into my_table (TT) values(@TT_Param)"
'Set oPara = oCmd.CreateParameter("@TT_param", adInteger, adParamInput)
'oCmd.Parameters.Append oPara
'oCmd.Parameters(0) = "123"

oCmd.Execute
13 май 11, 21:57    [10650711]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
VBA_Kiddy
Member

Откуда: Высокогорные районы Полярного Урала
Сообщений: 7
Guest33,
Понятно, что можно так сделать, но я хотел подставлять параметры непосредственно из программы VBA.
13 май 11, 22:01    [10650724]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
Guest33
Member

Откуда:
Сообщений: 2070
Наверно, не получится. Надо использовать ХП
13 май 11, 22:25    [10650795]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
VBA_Kiddy
Member

Откуда: Высокогорные районы Полярного Урала
Сообщений: 7
Выяснил, что можно сделать так:
oCmd.CommandText = "insert into tt (TT,P) values(?,?)"
Set oPara1 = oCmd.CreateParameter("@TT_param", adInteger, adParamInput)
Set oPara2 = oCmd.CreateParameter("@TT_param2", adInteger, adParamInput)
oCmd.Parameters.Append oPara1
oCmd.Parameters.Append oPara2
oCmd.Parameters(0) = "123"
oCmd.Parameters(1) = "222"
oCmd.Execute

Тогда возникает вопрос: можно ли вписать названия параметров в тексте SQL запроса?
13 май 11, 22:25    [10650797]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2783
VBA_Kiddy
...
Тогда возникает вопрос: можно ли вписать названия параметров в тексте SQL запроса?


Что мешает Вам программно создать SQL текст и только потом присвоить в "CommandText"?
А для параметров либо будете спрашивать у пользователя через Inputbox (правда не очень красивое решение), либо создаёте специальную "UserForm", где должен будет пользователь ввести в нужные поля нужные параметры, либо в отдельном листе в отдельных ячейках пользователь должен будет ввести значения параметров заранее до запуска макроса.
Если пофантазировать вариантов куча, это ещё не предел. Можно придумывать и придумывать.
14 май 11, 09:03    [10651413]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
Guest33
Member

Откуда:
Сообщений: 2070
Вот как получилось:
Sub SQL_temp()
Dim Conn As ADODB.Connection
Dim strConn As String
Dim oCmd As ADODB.Command
Dim oPara As ADODB.Parameter

Set Conn = New ADODB.Connection
Conn.ConnectionString = _
GetConnectOLEDB
Conn.Open

Set oCmd = CreateObject("adodb.command")
oCmd.ActiveConnection = Conn.ConnectionString
oCmd.CommandText = "execute sp_executesql " & _
          "N'insert into edin_izm (signific) values (@Term)', " & _
          "N'@Term varchar(10)', " & _
          "@Term = ?"

Set oPara = oCmd.CreateParameter("@Term", adVarChar, adParamInput, 10)
oCmd.Parameters.Append oPara
oCmd.Parameters(0) = "###"

oCmd.Execute

Conn.Close
Set Conn = Nothing
MsgBox "OK!"
End Sub
14 май 11, 14:17    [10651992]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
basicv
Member

Откуда:
Сообщений: 252
studieren
Что мешает Вам программно создать SQL текст и только потом присвоить в "CommandText"?

Если имел в виду динамически создавать SQL запрос посредством обединения строк, то это плохая практика.
Параметризованные запросы выигрывают:
1. Повторные запросы будут выполнятся быстрее.
2. Повышается безопасность - так как затрудняется SQL Injection.
14 май 11, 16:27    [10652361]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
VBA_Kiddy
Member

Откуда: Высокогорные районы Полярного Урала
Сообщений: 7
Спасибо всем за обсуждение!
Вопрос был о правильном синтаксисе :)

Для моей задачи было удобнее сформировать строку запроса один раз, а потом подставлять параметры.
К тому же, через параметры, как мне кажется, проще работать с датами и числами - не приходится их преобразовывать в строку в коде и думать о форматах. Ну и, конечно же, аргументы, приведённые basicv имеют место быть.
18 май 11, 13:43    [10671199]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
studieren
Member

Откуда: Tashkent, Uzbekistan
Сообщений: 2783
VBA_Kiddy,

А как Вы будете передавать значение "Null" для определённого параметра? Или у Вас параметры никогда не могут быть с нулевым значением?
18 май 11, 14:43    [10671676]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
VBA_Kiddy
Member

Откуда: Высокогорные районы Полярного Урала
Сообщений: 7
studieren,

А в чём может быть проблема? У меня параметры подставляются как значения ячеек Excel. Пустые ячейки автоматически заносятся в MS SQL как NULL.
18 май 11, 15:09    [10671907]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
Guest33
Member

Откуда:
Сообщений: 2070
Sub sp_ExecuteSQL()
Dim Conn As ADODB.Connection
Dim strConn As String
Dim oCmd As ADODB.Command
Dim oPara As ADODB.Parameter

Set Conn = New ADODB.Connection
Conn.ConnectionString = _
GetConnectOLEDB
Conn.Open

Set oCmd = CreateObject("adodb.command")
oCmd.ActiveConnection = Conn.ConnectionString
oCmd.CommandText = "execute sp_executesql " & _
          "N'insert into _kontrct (signific, name_kontr, organiz_kt) " & _
          "values (@Sig, @Ktr,@org)', " & _
          "N'@Sig varchar(10),@Ktr varchar(40),@Org varchar(50)', " & _
          "@Sig = ?,@Ktr=?,@Org=?"

Set oPara = oCmd.CreateParameter("@Sig", adVarChar, adParamInput, 10)
oCmd.Parameters.Append oPara

Set oPara = oCmd.CreateParameter("@Ktr", adVarChar, adParamInput, 40)
oCmd.Parameters.Append oPara

Set oPara = oCmd.CreateParameter("@Org", adVarChar, adParamInput, 50)
oCmd.Parameters.Append oPara

oCmd.Parameters(0) = "##5"
oCmd.Parameters(1) = "klop"
oCmd.Parameters(2) = Null
oCmd.Execute

oCmd.Parameters(0) = "##6"
oCmd.Parameters(1) = Null
oCmd.Parameters(2) = "klop"
oCmd.Execute

Conn.Close
Set Conn = Nothing
MsgBox "OK!"
End Sub
18 май 11, 15:24    [10672051]     Ответить | Цитировать Сообщить модератору
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
П-Л
Guest
Оно может со стороны сервера, через объектную модель экселя может проще/удобнее будет ?
18 май 11, 15:56    [10672457]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: VBA. ADO. Запрос с параметрами к MS SQL.  [new]
JohnBMW
Member

Откуда:
Сообщений: 6
Guest33, Большое спасибо! Всё заработало ...
4 окт 17, 15:32    [20842555]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Access Ответить