problem fetching a recSet thru Stored procedure (in ASP)

[rajkum]

Hi,
I am trying to fetch a RecordSet in ASP using Stored Procedure.
I have been using Stored procedure for returning single fields and Insert / Update and it works fine..but here I am stuck!!
Please help.

The error message I get is as below:
---------------------------------------

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'EOF'

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


ASP:

errMsgASP = ""
errASP = False
adSmallInt = 2
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = &H0002
adInteger = 3
adVarChar = 200
adChar = 129
adDate = 7




Set cmdStr = Server.CreateObject("ADODB.Command&quot
SET rs = Server.CreateObject("ADODB.RecordSet&quot
Set cmdStr.ActiveConnection = Conn

cmdStr.CommandText = "storedProcName"
cmdStr.CommandType = adCmdSPStoredProc

cmdStr.Parameters.Append cmdStr.CreateParameter("RETURN", adInteger, adParamReturnValue)
cmdStr.Parameters.Append cmdStr.CreateParameter("param1", adInteger, adParaminput,1)
cmdStr.Parameters.Append cmdStr.CreateParameter("param2", adChar, adParaminput,3)
cmdStr.Parameters.Append cmdStr.CreateParameter("param3", adChar, adParaminput,4)
cmdStr.Parameters.Append cmdStr.CreateParameter("param4", adInteger, adParaminput,1)
cmdStr.Parameters.Append cmdStr.CreateParameter("outParam1", adChar, adParamOutput,1)
cmdStr.Parameters.Append cmdStr.CreateParameter("outParam2", adVarChar, adParamOutput,500)

cmdStr("param1&quot = paramValfromWeb1
cmdStr("param2&quot = paramValfromWeb2
cmdStr("param3&quot = paramValfromWeb3
cmdStr("param4&quot = paramValfromWeb4

rs = cmdStr.Execute

WHILE NOT rs.EOF
Response.Write(rs(1))
rs.MoveNext
WEND

errCd = cmdStr.Parameters("outParam1&quot
errMsg = cmdStr.Parameters("outParam2&quot
errStr = cmdStr.Parameters("RETURN&quot

Set rs = Nothing


stored Proc:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC dbo.storedProcName
@param1 char(3),
@param2 char(4),
@param3 int = NULL,
@param4 int =NULL,
@outParam1 char(1) OUTPUT,
@outParam2 varchar(400) OUTPUT
AS
SET NOCOUNT ON
BEGIN

SET @outParam2 = ''
IF (@param1 is NULL OR @param1 = '')
BEGIN
RAISERROR('param1 cannot be Empty',1,10)
SET @outParam1 = 'Y'
SET @outParam2 = @outParam2 + 'param1 cannot be Empty'
return(1)
END


SELECT * from table WHERE fld1 = @param2 AND fld2 = @param3
return

END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks,
RK

 

[SQLSister]

This is an ASP error not a SQL Server error. You will probably get a better answer onthe ASP forum.