Visual Basic：无法从记录集中读取记录/数据。

Question

这里是我的代码：

Dim CompanyName, _ 
    CompanyDomain, _ 
    CompanyEmail, _ 
    CompanySupportPhone 

Call GetEmailList 

Sub GetEmailList 
    dim sql 
    dim companydata 

    sql = "" 
    sql = sql & " DECLARE @CompanyName VARCHAR(100);" 
    sql = sql & " DECLARE @CompanyDomain VARCHAR(100);" 
    sql = sql & " DECLARE @CompanyActivityEmail VARCHAR(100);" 
    sql = sql & " DECLARE @CompanySupportPhone VARCHAR(100);" 

    sql = sql & " SELECT" 
     sql = sql & " @CompanyName = CASE WHEN Setting = 'CompanyName'" 
     sql = sql & " THEN StringValue ELSE @CompanyName END," 
     sql = sql & " @CompanyDomain = CASE WHEN Setting = 'CompanyDomain'" 
     sql = sql & " THEN StringValue ELSE @CompanyDomain END," 
     sql = sql & " @CompanyActivityEmail = CASE WHEN Setting = 'CompanyActivityEmail'" 
     sql = sql & " THEN StringValue ELSE @CompanyActivityEmail END," 
     sql = sql & " @CompanySupportPhone = CASE WHEN Setting = 'CompanySupportPhone'" 
     sql = sql & " THEN StringValue ELSE @CompanySupportPhone END" 
    sql = sql & " FROM ClientSettings" 
    sql = sql & " WHERE Setting in ('CompanyDomain','CompanyActivityEmail','CompanySupportPhone','CompanyName')" 

    sql = sql & " SELECT ISNULL(@CompanyName, '') AS CompanyName, ISNULL(@CompanyDomain, '') AS CompanyDomain, ISNULL(@CompanyActivityEmail, '') AS CompanyEmail, ISNULL(@CompanySupportPhone, '') AS CompanySupportPhone" 
    set companydata = getRecordset(sql) 

    CompanyName = companydata("CompanyName") ' LINE 80 
    CompanyDomain = companydata("CompanyDomain") 
    CompanyEmail = companydata("CompanyEmail") 
     CompanySupportPhone = companydata("CompanySupportPhone") 

     companydata.Close 
     Set companydata = Nothing 
End Sub 

这将引发一个错误：

Line 80

Item cannot be found in the collection corresponding to the requested name or ordinal.

我上面标线80。我在SQL Server管理器中运行完全相同的SQL并返回结果：

CompanyName CompanyDomain CompanyEmail CompanySupportPhone 
MyCompanyName http://localhost [email protected] 801-555-1212 

任何想法我在做什么错在这里？

GetRecordSet正确加载和处理数据库调用，该函数可以在其他1,000个地方使用。我相信这个问题不存在。

Answer 1

添加

sql = sql & " SET NOCOUNT ON;" 

作为第一个SQL语句。

SET NOCOUNT ON usage