Return Results from procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return Results from procedure

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-19 : 17:25:12

Simplest of procedures - a login

CREATE PROCEDURE dbo.LoginUserEmail
@email varchar(100) -- This procedures logs

AS
DECLARE @UserID as INT, @FirstLogin as datetime

SELECT @UserID=U.UserID, @FirstLogin=U.FirstLogin
FROM Users U WHERE U.Email = @Email

IF @UserID IS NOT NULL
BEGIN
IF @FirstLogin IS NULL -- Set this date for first login only
SET @FirstLogin = GETDATE() -- Record first login

UPDATE Users -- Now write the Login dates back to the row
SET FirstLogin = @FirstLogin, LastLogin = GETDATE()
WHERE UserID = @UserID

SELECT * FROM Users WHERE Email = @Email
END
GO

In ASP, the check for rs.eof is used to determine if the record was found.

If the record isn't found however, rs.eof always generates the following message:

"Operation not allowed when object is closed."

To resolve this problem, I had to add

SELECT * from Users where Email=@Email

to the end of the routine.

Is the first SELECT not setting EOF and somehow closing the connection?

SamC

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-09-19 : 18:04:11

Before your first SELECT put SET NOCOUNT ON
and after your last SELECT put SET NOCOUNT OFF

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-09-19 : 18:05:39

Add

SET NOCOUNT ON

at the beginning of the procedure, otherwise the update statement generates an empty and closed recordset that is returned to the ASP page.

(In your case the result of the select statement is in a separate recordset which can be get by using the nextrecordset method.)

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2002-09-19 : 19:08:13

In ASP if you want to handle return values you should use connection obj. event it returns return value or rset. Here is an example which I use to handle return values in asp.

CREATE PROCEDURE up_SaveRecord
(
@adi varchar(40),
@soyadi varchar(40),
@kullanici varchar(15),
@sifre varchar(15),
@email varchar(50),
@cinsiyet char(1),
@dtarih varchar(10),
@tanim varchar(50),
@sid varchar(15),
@new_id int OUTPUT

)
AS

DECLARE @rc int

select @rc = 0

IF Exists(Select adi FROM u_kullanicilar WHERE kullanici = @kullanici)
begin
select @new_id=0
Goto OnExit
end

insert into u_kullanicilar (adi, soyadi, kullanici, sifre, email, cinsiyet, dtarih, is_active, tanim, sid) values
(@adi, @soyadi, @kullanici, @sifre, @email, @cinsiyet, @dtarih, 0, @tanim, @sid)
select @new_id = @@identity

OnExit:
RETURN @rc
GO
----------- in my asp page --------
Set moADOCon = Server.CreateObject("ADODB.Connection")
Set moADOCom = Server.CreateObject("ADODB.Command")

moADOCon.CursorLocation = adUseClient
moADOCon.open dsn

with moADOCom
.activeconnection = moADOCon
.commandtext = "up_SaveRecord"
.commandtype = adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@adi", adVarWChar, adParamInput, 40, ad)
.Parameters.Append .CreateParameter("@soyadi", adVarWChar, adParamInput, 40, sad)
.Parameters.Append .CreateParameter("@kullanici", adVarWChar, adParamInput, 15, kadi)
.Parameters.Append .CreateParameter("@sifre", adVarWChar, adParamInput, 15, sifre1)
.Parameters.Append .CreateParameter("@email", adVarWChar, adParamInput, 50, email)
.Parameters.Append .CreateParameter("@cinsiyet", adChar, adParamInput, 1, cinsiyet)
.Parameters.Append .CreateParameter("@dtarih", advarChar, adParamInput, 10, dtarih)
.Parameters.Append .CreateParameter("@tanim", adVarWChar, adParamInput, 4000, tanim)
.Parameters.Append .CreateParameter("@sid", adVarWChar, adParamInput, 15, iid)
.Parameters.Append .CreateParameter("@new_id", adInteger, adParamOutput, 0)

end with

moADOCom.Execute , , adExecuteNoRecords
msNewID = trim(moADOCom("@new_id"))

On Error Resume Next

If moADOCon.STATE = adStateOpen Then moADOCon.Close

Set moADOCom = nothing
Set moADOCon = Nothing

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-19 : 20:56:28

After doing a little more work and using SET NOCOUNT ON, I found that the rs.eof check in ASP was still failing in error.

Turns out the problem is the first select in the procedure (see first post) - which SELECTS the record only defines local variables to the procedure and doesn not return a recordset or set EOF.

For now, I have solved the problem and set EOF by executing a second SELECT

SELECT * FROM Users WHERE 1=0

If there's a better way to set this condition before returning, please let me know.

SamC

Edited by - SamC on 09/21/2002 13:55:50

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources