Getting records and output parameter from ASP

[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.CreateOb ject("ADODB.Con nection")
cnnStoredProc.O pen db
' get the correct records for a page
Set cmdStoredProc = Server.CreateOb ject("ADODB.Com mand") ' Create Command object we'll use to execute the SP
cmdStoredProc.A ctiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.C ommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.C ommandType = adCmdStoredProc
cmdStoredProc.P arameters.Refre sh

' ---------- SET PARAMETERS ----GET A PAGE WORTH OF RECORD--------------------------------------------------------------------
set prop=ADODB.Para meter
cmdStoredProc.P arameters("@fun ctionCode").Val ue = "L" 'functionCode
cmdStoredProc.P arameters("@cus tomerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.P arameters("@sea rchcriteria").V alue = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.P arameters("@exc ludeDefined").V alue = "Y" '
end if
set rstStoredProc = cmdStoredProc.E xecute
'

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

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

Any Ideas

Thanks

[ck9663]

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.CreateOb ject("ADODB.Con nection")
cnnStoredProc.O pen db
' get the correct records for a page
Set cmdStoredProc = Server.CreateOb ject("ADODB.Com mand") ' Create Command object we'll use to execute the SP
cmdStoredProc.A ctiveConnection = cnnStoredProc ' Set our Command to use our existing connection
cmdStoredProc.C ommandText = "test" ' Set the SP's name and tell the Command object
cmdStoredProc.C ommandType = adCmdStoredProc
cmdStoredProc.P arameters.Refre sh

' ---------- SET PARAMETERS ----GET A PAGE WORTH OF RECORD--------------------------------------------------------------------
set prop=ADODB.Para meter
cmdStoredProc.P arameters("@fun ctionCode").Val ue = "L" 'functionCode
cmdStoredProc.P arameters("@cus tomerID").Value = customerId 'CustomerID
if searchCriteria <> "" then
cmdStoredProc.P arameters("@sea rchcriteria").V alue = searchCriteria 'search string
end if
if showAssigned = "Y" then
cmdStoredProc.P arameters("@exc ludeDefined").V alue = "Y" '
end if
set rstStoredProc = cmdStoredProc.E xecute
'

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.Cre ateObject("ADOD B.Connection")
conn.Provider=" Microsoft.Jet.O LEDB.4.0"
conn.Open(Serve r.Mappath("nort hwind.mdb"))

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

if rs.Supports(adA pproxPosition)= true then
i=rs.RecordCoun t
response.write( "The number of records is: " & i)
end if

rs.Close
conn.Close
%>