代码之家 › 专栏 › 技术社区 › Neha

如何使用单个对象一次性在qtp中运行多个查询

qtp vba sql-server

Neha · 技术社区 · 7 年前

每个单元格都要运行一组查询。

但是存在具有多个语句的单元格,例如在单元格(x,6)中存在以下查询: " Exec sp_DropObjectiveExists“可诱惑”; 从主表中选择col1到#tentable; 更新#可诱惑集colv=5 从#tentable中选择count(1)作为totalCount

以上只是一个示例,而不是确切的sql查询。整个集合位于单个excel工作表的单元格中。

Set objconnection = CreateObject("ADODB.Connection")
objconnection.open"provider=blah blah blah"
 Set objrecordset= CreateObject("ADODB.Recordset")
 ws.cells(x,6).select ''''the above sql queries set is in this cell
Sqlquery1= ws.cells(x,6).value
objrecordset.Open Sqlquery1. objconnection
Value1=objrecordset.Fields.Item(0)

对于上面的最后一行,我得到了一个错误,即 “在与requestef name或ordinal对应的集合中找不到项”

你能帮我一次性完成这件事吗。

1 回复 | 直到 7 年前

David Rushton 7 年前

在查询前加上前缀 SET NOCOUNT ON; temp tables 和SQL语句中的变量。

下面的代码说明了这一点。我用过 early binding 使代码更易于阅读( 工具 &燃气轮机&燃气轮机; 工具书类 ).

在这些线路之间切换以进行测试:

rs.Open QueryA, cn, adOpenForwardOnly, adLockReadOnly
rs.Open QueryB, cn, adOpenForwardOnly, adLockReadOnly

QueryA将失败。QueryB将返回 Jack

' Demo of using SET NOCOUNT ON;.
' This option enabled the use of SQL vars and temp tables.
Sub Test()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cn.Open "Driver={SQL Server};Server=YOUR-SEVER-NAME-HERE;Database=master;Trusted_Connection=Yes;"

    ' QueryA fails, while QueryB does not.
    ' Switch which line is commented out to test.
    rs.Open QueryA, cn, adOpenForwardOnly, adLockReadOnly
    'rs.Open QueryB, cn, adOpenForwardOnly, adLockReadOnly

    ' This line will raise an error with QueryA.
    ' This line will work with QueryB.
    MsgBox rs.Fields(1).Value

    rs.Close
    cn.Close
End Sub

' Returns a sample query without NOCOUNT.
Public Function QueryA() As String

    QueryA = "              CREATE TABLE #ExampleA              "
    QueryA = QueryA & "     (                                   "
    QueryA = QueryA & "         Id      INT PRIMARY KEY,        "
    QueryA = QueryA & "         Name    VARCHAR(50) NOT NULL    "
    QueryA = QueryA & "     );                                  "
    QueryA = QueryA & ""
    QueryA = QueryA & "     INSERT INTO #ExampleA (Id, Name)    "
    QueryA = QueryA & "     VALUES                              "
    QueryA = QueryA & "         (1, 'Jack'),                    "
    QueryA = QueryA & "         (2, 'Jill')                     "
    QueryA = QueryA & "     ;                                   "
    QueryA = QueryA & ""
    QueryA = QueryA & "     SELECT * FROM #ExampleA             "
End Function

' Returns a sample query with NOCOUNT.
Public Function QueryB() As String

    QueryB = "              SET NOCOUNT ON;                     "
    QueryB = QueryB & ""
    QueryB = QueryB & "     CREATE TABLE #ExampleA              "
    QueryB = QueryB & "     (                                   "
    QueryB = QueryB & "         Id      INT PRIMARY KEY,        "
    QueryB = QueryB & "         Name    VARCHAR(50) NOT NULL    "
    QueryB = QueryB & "     );                                  "
    QueryB = QueryB & ""
    QueryB = QueryB & "     INSERT INTO #ExampleA (Id, Name)    "
    QueryB = QueryB & "     VALUES                              "
    QueryB = QueryB & "         (1, 'Jack'),                    "
    QueryB = QueryB & "         (2, 'Jill')                     "
    QueryB = QueryB & "     ;                                   "
    QueryB = QueryB & ""
    QueryB = QueryB & "     SELECT * FROM #ExampleA             "
End Function

我已经在几个丑陋的函数中嵌入了我的查询的两个版本。它们很难阅读,但很容易分享。下面是工作查询的干净版本。拆下非工作变型的第一行。

SET NOCOUNT ON;

CREATE TABLE #ExampleA              
(                                   
    Id      INT PRIMARY KEY,        
    Name    VARCHAR(50) NOT NULL    
);                                  

INSERT INTO #ExampleA (Id, Name)    
VALUES                              
    (1, 'Jack'),                    
    (2, 'Jill')                     
;                                   

SELECT * FROM #ExampleA;