如何将参数与 ADO 中的 Command 对象与 VBScript 相关联? [英] How do I associate Parameters to Command objects in ADO with VBScript?

问题描述

我一直在使用 ADO VBScript，它需要接受参数并将这些参数合并到通过数据库传递的查询字符串中.当记录集对象尝试打开时，我不断收到错误消息.如果我传递一个不带参数的查询，记录集就会打开，我可以处理数据.当我通过调试器运行脚本时，命令对象不显示参数对象的值.在我看来，我缺少将 Command 对象和 Parameter 对象关联起来的东西，但我不知道是什么.这是一些 VBScript 代码:

I have been working an ADO VBScript that needs to accept parameters and incorporate those parameters in the Query string that gets passed the the database. I keep getting errors when the Record Set Object attempts to open. If I pass a query without parameters, the recordset opens and I can work with the data. When I run the script through a debugger, the command object does not show a value for the parameter object. It seems to me that I am missing something that associates the Command object and Parameter object, but I do not know what. Here is a bit of the VBScript Code:

...
'Open Text file to collect SQL query string'
Set fso = CreateObject("Scripting.FileSystemObject")
fileName = "C:SQLFUNLimits_ADO.sql"
Set tso = fso.OpenTextFile(fileName, FORREADING)

SQL = tso.ReadAll

'Create ADO instance'
 connString = "DRIVER={SQL Server};SERVER=myserver;UID=MyName;PWD=notapassword;   Database=favoriteDB"
 Set connection = CreateObject("ADODB.Connection")
 Set cmd = CreateObject("ADODB.Command")

  connection.Open connString
  cmd.ActiveConnection = connection
  cmd.CommandText = SQL
  cmd.CommandType = adCmdText

  Set paramTotals = cmd.CreateParameter
  With paramTotals
       .value = "tot%"
       .Name = "Param1"
  End With

  'The error occurs on the next line'
  Set recordset = cmd.Execute

  If recordset.EOF then
      WScript.Echo "No Data Returned"
  Else
      Do Until recordset.EOF
            WScript.Echo recordset.Fields.Item(0) ' & vbTab & recordset.Fields.Item(1)
            recordset.MoveNext
      Loop
  End If

我使用的 SQL 字符串相当标准，只是我想向它传递一个参数.它是这样的:

The SQL string that I use is fairly standard except I want to pass a parameter to it. It is something like this:

SELECT column1
FROM table1
WHERE column1 IS LIKE ?

我知道 ADO 应该替换?"使用我在脚本中分配的参数值.我看到的问题是 Parameter 对象显示了正确的值，但根据我的调试器，命令对象的参数字段为空.

I understand that ADO should replace the "?" with the parameter value I assign in the script. The problem I am seeing is that the Parameter object shows the correct value, but the command object's parameter field is null according to my debugger.

推荐答案

我知道这已经过时了，但对于任何仍在寻找这个的人(就像我通过谷歌所做的那样):

I know this is old, but for anyone still fiding this (like I did via google):

如果您使用存储过程:

set cmd = Server.CreateObject("ADODB.Command")
with cmd
    .ActiveConnection = db_connection
    .CommandText = "stored_procedure_name"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@Parameter1",adInteger,adParamInput,,1)
        .Parameters.Append .CreateParameter("@Parameter2",adVarChar,adParamInput,100,"Up to 100 chars")
        .Parameters.Append .CreateParameter("@Parameter3",adBoolean,adParamInput,,true)
        .Parameters.Append .CreateParameter("@Parameter4",adDBTimeStamp,adParamInput,,now())
end with
set rs = cmd.execute
    'do stuff with returned results from select or leave blank if insert/delete/etc stored procedure
set rs = nothing
set cmd = nothing

如果不是，我相信您将 .CommandText 更改为带有问号的 SQL 语句，并且您的参数必须遵循相同的顺序.

If not, I beleive you change the .CommandText to your SQL statement with questions marks in place and your Parameters must follow the same order.

参见 http://www.devguru.com/technologies/ado/quickref/command_createparameter.html有关您使用 CreateParameter 传递的值的细目分类，以及类型列表及其描述.

See http://www.devguru.com/technologies/ado/quickref/command_createparameter.html For a breakdown of what values you're passing with CreateParameter, as well as a list of types and their descriptions.

这篇关于如何将参数与 ADO 中的 Command 对象与 VBScript 相关联?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！