|
 |
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
|
|
|
View Cart
