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    MyCompanyName@email.com 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.

Please choose a more descriptive title for your question. "I'm having trouble with Visual Basic" ... who isn't? — Jean-François Corbett, Apr 06 '12 at 18:46

score 5 · Accepted Answer · edited May 23 '17 at 10:34

5

Add

sql = sql & " SET NOCOUNT ON;"

as the first SQL statement.

SET NOCOUNT ON usage

edited May 23 '17 at 10:34

Community

1
1

answered Apr 06 '12 at 18:16

amit_g

30,880
8
61
118

That was it, thank you. Any idea why not having that breaks it? – James Wilson Apr 06 '12 at 18:18
Updated the comment with a link to detailed description about this. – amit_g Apr 06 '12 at 18:20
2

@amit_g: Yeah that link points to a lot of text. So for clarity the answer is simply this: At times ADODB gets itself confused and believes the single "count of rows" returned be SQL server is to be treated as a recordset. Most of the time we don't need this value so "SET NOCOUNT ON" prevents SQL Server from sending this phantom recordset and avoids ADODB become confused at times. – AnthonyWJones Apr 06 '12 at 19:27
That explained it perfectly, I'll be adding this a lot more from now on. – James Wilson Apr 06 '12 at 20:40

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

1 Answers1