Getting records and output parameter from ASP

I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset.

I can get either but not both. (ie. if there is a select then there is no output parameter)

The stored procedure is:

ALTER PROCEDURE test
(
@.Msgs nvarchar(150) OUTPUT
)

As
declare @.selCount int
SELECT folderLocation, externalDocsID from externalDocs

set @.selCount = @.@.ROWCOUNT
IF @.@.ROWCOUNT = 0
BEGIN
SET @.Msgs = 'No folders meet this selection criteria'
RETURN
END
else
BEGIN
SET @.Msgs = @.selCount
END
return

-------------------------

the asp code is

Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object
Dim rstStoredProc ' Recordset object
Dim folderText
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open db
' get the correct records for a page
Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Create Command object we'll use to execute the SP
cmdStoredProc.ActiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.CommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Refresh

' ---- SET PARAMETERS --GET A PAGE WORTH OF RECORD---------------------
set prop=ADODB.Parameter
cmdStoredProc.Parameters("@.functionCode").Value = "L" 'functionCode
cmdStoredProc.Parameters("@.customerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.Parameters("@.searchcriteria").Value = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.Parameters("@.excludeDefined").Value = "Y" '
end if
set rstStoredProc = cmdStoredProc.Execute
'

response.write("msgs=" & cmdStoredProc("@.Msgs")) ' <<<<<<< THIS DOESN'T WORK------------------------------

I can enumerate through the recordset but the output parameter is blank.

Any Ideas

Thanks

Quote:

Originally Posted by Mike Lester

I have a need for a stored procedure to return a recordset AND an output parameter that contains the count of records in the recordset.

I can get either but not both. (ie. if there is a select then there is no output parameter)

The stored procedure is:

ALTER PROCEDURE test
(
@.Msgs nvarchar(150) OUTPUT
)

As
declare @.selCount int
SELECT folderLocation, externalDocsID from externalDocs

set @.selCount = @.@.ROWCOUNT
IF @.@.ROWCOUNT = 0
BEGIN
SET @.Msgs = 'No folders meet this selection criteria'
RETURN
END
else
BEGIN
SET @.Msgs = @.selCount
END
return

-------------------------

the asp code is

Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object
Dim rstStoredProc ' Recordset object
Dim folderText
Set cnnStoredProc = Server.CreateObject("ADODB.Connection")
cnnStoredProc.Open db
' get the correct records for a page
Set cmdStoredProc = Server.CreateObject("ADODB.Command") ' Create Command object we'll use to execute the SP
cmdStoredProc.ActiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.CommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.Parameters.Refresh

' ---- SET PARAMETERS --GET A PAGE WORTH OF RECORD---------------------
set prop=ADODB.Parameter
cmdStoredProc.Parameters("@.functionCode").Value = "L" 'functionCode
cmdStoredProc.Parameters("@.customerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.Parameters("@.searchcriteria").Value = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.Parameters("@.excludeDefined").Value = "Y" '
end if
set rstStoredProc = cmdStoredProc.Execute
'

response.write("msgs=" & cmdStoredProc("@.Msgs")) ' <<<<<<< THIS DOESN'T WORK------------------------------

I can enumerate through the recordset but the output parameter is blank.

Any Ideas

Thanks

i believe RecordSet have a property called RecordCount...

example:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))

set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM Customers"
rs.Open sql,conn

if rs.Supports(adApproxPosition)=true then
i=rs.RecordCount
response.write("The number of records is: " & i)
end if

rs.Close
conn.Close
%>