Simple ASP function question

Question

Good Morning,

I have got the following function:

 FUNCTION queryDatabaseCount(sqlStr)
        SET queryDatabaseCountRecordSet = databaseConnection.Execute(sqlStr) 
        If queryDatabaseCountRecordSet.EOF Then
            queryDatabaseCountRecordSet.Close
            queryDatabaseCount = 0
        Else
            QueryArray = queryDatabaseCountRecordSet.GetRows
            queryDatabaseCountRecordSet.Close
            queryDatabaseCount = UBound(QueryArray,2) + 1
        End If
    END FUNCTION

And the following dbConnect:

SET databaseConnection = Server.CreateObject("ADODB.Connection")

    databaseConnection.Open "Provider=SQLOLEDB; Data Source ="&dataSource&"; Initial Catalog ="&initialCatalog&"; User Id ="&userID&"; Password="&password&""

But for some reason I get the following error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/UBS/DBMS/includes/blocks/block_databaseoverview.asp, line 30

Does anyone have any suggestions?

Many Thanks, Joel

Answer 1

Long time since I touched asp/ado but I have some vague memory about EOF not always being a reliable indicator:

If (rst.BOF And rst.EOF) Then
--
Else
--
End If

As an aside, you shouldn't really be determining a record count that way. Preferably execute a statement or proc that returns only the count, rather than returning the rows then counting them.

Answer 2

As already been pointed out: use a count in stead of your code (your code will load all data in memory!)

function queryDatabaseCount(sTable, sSomeCondition) 
   dim sSql
   dim Result
   Result = 0
   sSql = "select Count(*) as Cnt from " & sTable
   if sSomeCondition <> "" then
      sSql = sSql & " where " &  sSomeCondition
   end if
   objRec.Source = sSql
   objRec.Open
   Result = Fld("Cnt")
   objRec.Close
   queryDatabaseCount = Result

end function

calling it as

dim i
SET databaseConnection = Server.CreateObject("ADODB.Connection") 
SET objRec = Server.CreateObject("ADODB.Recordset")
objRec.ActiveConnection = databaseConnection 

i = queryDatabaseCount("MyTable", "SomeField = 1")

databaseConnection.Close
SET databaseConnection = nothing

Haven't tested this code, but it should do the trick (or come very close to the solution).

Just as a side note: I tend to append the suffix _DBO to these function (meaning Data Base Open), letting me know that this function expects a open database connection. But that's just personal preference of course.