How to count rows in recordset

[djblois]

My company is using Legacy ASP with VB script.

The code I am testing to try and count the rows in a stored procedure is as follows:

If Not isNull(dblOrderMasterID) then
    Set cmdAttachCount = Server.CreateObject("ADODB.Command")
    cmdAttachCount.ActiveConnection = cnn
    'cmdAttachCount.CommandText = "SP_OrderGetAttchID"
    'cmdAttachCount.CommandType = adCmdStoredProc
    'prm.Append cmdAttachCount.CreateParameter("@u_order_master_id", adNumeric, adParamInput,,dblOrderMasterID)
    'Set rstAttchCount = Server.CreateObject("ADODB.Recordset")
    'Set rstAttchCount = cmdAttachCount.Execute
    intAttachCount = cmdAttachCount.Execute("SELECT COUNT(*) FROM SP_OrderGetAttchID @u_order_master_id;").Fields(0)
End IfInline Code Example Here

Unforunately it keeps crashing the webpage.

[glennferrie]

If you're executing a stored procedure, you'll need to load all of the data into memory and get the count from there. Otherwise, you can create another procedure that only returns the count.

[BMXDad]

If your record count is fairly small you can use the .RecordCount attribute on the record set. Otherwise a seperate SQL statment returning an int of records counted.

[djblois]

Glennferrie, all I need is the count. Can you point me in the right direction? Sorry I am new to VBscript - prior I did VBA (which I am pretty much an expert).

ggamble, I tried .RecordCount first but that crashed also. I will find that code again and post to see what I was doing wrong.

[djblois]

This is the .RecordCount that I tried and it also crashed:

If Not isNull(dblOrderMasterID) then
        Set cmdAttachCount = Server.CreateObject("ADODB.Command")
        cmdAttachCount.ActiveConnection = cnn
        cmdAttachCount.CommandText = "SP_OrderGetAttchID"
        cmdAttachCount.CommandType = adCmdStoredProc
        prm.Append cmdAttachCount.CreateParameter("@u_order_master_id", adNumeric, adParamInput,,dblOrderMasterID)
        Set rstAttchCount = Server.CreateObject("ADODB.Recordset")
        Set rstAttchCount = cmdAttachCount.Execute
        'intAttachCount = cmdAttachCount.Execute("SELECT COUNT(*) FROM SP_OrderGetAttchID @u_order_master_id;").Fields(0)
        intAttachCount = cmdAttachCount.RecordCount
    End If

[BMXDad]

What do you mean crashed? What did the error say?

[djblois]

The webpage cannot load. I currently can not step through the code because we have not set up a testing environment, so the only way to test any code is to push it live and then back it out if it doesn't work.

[djblois]

Exact Error, "The Website cannot display the page"

[djblois]

Does anyone have any idea? I have tried the two methods as above and neither worked.

[djblois]

Please can anyone help?

[SteveDotNet]

Execute the stored proc and get the resultant Recordset. Then call .RecordCount
Set rstAttchCount = cmdAttachCount.Execute
rstAttchCount.RecordCount
You may have to set the CursorLocation to adUseClient. The default is adUseServer

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.
The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.

The error message "The Website cannot display the page" is displayed because an exception was thrown.

Try prototyping in a console app. Use try/catch block to examine the exception.