p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: Stored Procedure + Recordset = No Sense


Message #1 by "Stephen B Looney" <slooney@l...> on Thu, 27 Jul 2000 21:51:53
Hello:

   I have a Stored Procedure that works and returns what I want when I 

execute it alone in InterDev.  When I try to access it from my ASP code the 

recordset seems not to be open.  Now the strange part, if I comment out all 

of the logic in my Stored Pocedure a recordset is created and contains the 

expected records...Here is the code for calling the Stored Procedure:



Set objComm = Server.CreateObject("ADODB.Command")

Set objRS = Server.CreateObject("ADODB.Recordset")

		

objComm.ActiveConnection = strConnect

objComm.CommandText = "sp_permissions"

objComm.CommandType = adCmdStoredProc

		

'Input Parameters

Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_

         adParamInput, 40)

objComm.Parameters.Append (objParam)

		

		

objComm.Parameters ("@SessionID") = SessionID

	

	

Set objRS = objComm.Execute

	

'Testing recordset

IF NOT objRS IS NOTHING THEN

	If objRS.BOF AND objRS.EOF Then



          ...DO Something...



	End If

	

	

		

	objRS.Close

END IF

Set objRS = Nothing

Set objParam = Nothing

Set objComm = Nothing



*****************

Now the Code for the Stored Procedure



Create Procedure sp_permissions



	(

		@SessionID		varchar(40)

	)



AS

DECLARE @Permission		INT

DECLARE @MachineNo		INT

DECLARE @MachineType	VARCHAR(60)

DECLARE @Count			TINYINT

	

DELETE FROM RSTEMP

			

SELECT @Permission=USERS.Permission FROM USERLOG INNER JOIN USERS_

     ON USERLOG.UserID = USERS.UserID WHERE (USERLOG.SessionID 

=@SessionID)



SET @Count = 0

	

WHILE @Count < 29

   BEGIN

       IF (SELECT Status FROM MACHINES WHERE MachineNo 

=POWER(2,@Count))<>0

	  BEGIN

	      IF ((@Permission & POWER(2,@Count)) = POWER(2,@Count))

		 BEGIN

		     SELECT @MachineNo=MachineNo, @MachineType=MachineType_

                           FROM MACHINES WHERE MachineNo = POWER(2,@Count)

		     INSERT INTO RSTEMP (MachineNo, MachineType) VALUES_

                           (@MachineNo, @MachineType)

		  END

	   END

	SET @Count = @Count + 1

   END

	

SELECT * FROM RSTEMP

RETURN 



*******

If anyone could help me with this it would be greatly appreciated...All the 

documentation I have come across uses simple Stored Procedures which this 

seems not to be...And it isn't obvious I am kinda new to ASP's



Thanks



Stephen B Looney

Message #2 by Jagdeep Dua <Jag.Dua@n...> on Fri, 28 Jul 2000 13:34:12 +0100
I have experienced the same problem when using OLEDB to make the connection

instead of using an ODBC driver. 



The problem was caused by the fact that NOCOUNT was left to OFF in the

stored procedure. This count (that you will see in SQL enterprise appears to

come back as a recordset when executed through ADO/ASP. 



Try using SET NOCOUNT ON at the top of your Stored Procedure (which is

actually good practice anyway, or so I am told)







-----Original Message-----

From: Stephen B Looney 

Sent: 27 July 2000 22:52

To: ASP Databases

Subject: [asp_databases] Stored Procedure + Recordset = No Sense





Hello:

   I have a Stored Procedure that works and returns what I want when I 

execute it alone in InterDev.  When I try to access it from my ASP code the 

recordset seems not to be open.  Now the strange part, if I comment out all 

of the logic in my Stored Pocedure a recordset is created and contains the 

expected records...Here is the code for calling the Stored Procedure:



Set objComm = Server.CreateObject("ADODB.Command")

Set objRS = Server.CreateObject("ADODB.Recordset")

		

objComm.ActiveConnection = strConnect

objComm.CommandText = "sp_permissions"

objComm.CommandType = adCmdStoredProc

		

'Input Parameters

Set objParam = objComm.CreateParameter("@SessionID", adVarChar,_

         adParamInput, 40)

objComm.Parameters.Append (objParam)

		

		

objComm.Parameters ("@SessionID") = SessionID

	

	

Set objRS = objComm.Execute

	

'Testing recordset

IF NOT objRS IS NOTHING THEN

	If objRS.BOF AND objRS.EOF Then



          ...DO Something...



	End If

	

	

		

	objRS.Close

END IF

Set objRS = Nothing

Set objParam = Nothing

Set objComm = Nothing



*****************

Now the Code for the Stored Procedure



Create Procedure sp_permissions



	(

		@SessionID		varchar(40)

	)



AS

DECLARE @Permission		INT

DECLARE @MachineNo		INT

DECLARE @MachineType	VARCHAR(60)

DECLARE @Count			TINYINT

	

DELETE FROM RSTEMP

			

SELECT @Permission=USERS.Permission FROM USERLOG INNER JOIN USERS_

     ON USERLOG.UserID = USERS.UserID WHERE (USERLOG.SessionID 

=@SessionID)



SET @Count = 0

	

WHILE @Count < 29

   BEGIN

       IF (SELECT Status FROM MACHINES WHERE MachineNo 

=POWER(2,@Count))<>0

	  BEGIN

	      IF ((@Permission & POWER(2,@Count)) = POWER(2,@Count))

		 BEGIN

		     SELECT @MachineNo=MachineNo, @MachineType=MachineType_

                           FROM MACHINES WHERE MachineNo = POWER(2,@Count)

		     INSERT INTO RSTEMP (MachineNo, MachineType) VALUES_

                           (@MachineNo, @MachineType)

		  END

	   END

	SET @Count = @Count + 1

   END

	

SELECT * FROM RSTEMP

RETURN 



*******

If anyone could help me with this it would be greatly appreciated...All the 

documentation I have come across uses simple Stored Procedures which this 

seems not to be...And it isn't obvious I am kinda new to ASP's



Thanks



Stephen B Looney




  Return to Index