Visual Basic: Can't read record/data from recordset.

Question

Here is my Code:

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

This throws an error:

Line 80

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

I marked line 80 above. I run this exact same SQL in SQL Server Manager and it returns results:

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

Any idea what I am doing wrong here?

GetRecordSet correctly loads and processes the database call, this function works in 1,000 other places. I'm sure the problem isn't there.

Answer 1

Add

sql = sql & " SET NOCOUNT ON;"

as the first SQL statement.

SET NOCOUNT ON usage