error while fetching recordset using stored proc 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

 

[Veep]

You didn't open your recordset so EOF is not available. An easier way to do it is:
<%
Set conn=server.createobject(&quot;ADODB.Connection&quot
Conn.open YourConnectionString
strSql=&quot;EXEC YourProcedureName @Param1='&quot; & Param1 & &quot;',@Param2='&quot; & Param2 & &quot;'&quot;
Set rs=conn.execute(strSql)
returnParam1=rs(0)
set rs=rs.nextRecordset
returnParam2=rs(0)
%>
That solves passing the input parameters. For get output parameters I turn NOCOUNT back on and do something like this at the end of my stored procedure:
Select myParameter = @AValueYouWantToPassBack
Select myParameter2 = @AnotherValueToPassBack

Another example is getting the Identity column
Select newID = @@IDENTITY

Works like a charm. If you need real world examples just shout.

 

[nadskram]

you dont need to open a recordset to get one
set rs = db_conn.execute never calls the rs.open method, but you get a recordset

i think his issue was he forgot to say SET when he told the command object to execute

but i could be wrong