SQL returning value

Results 1 to 2 of 2

Thread: SQL returning value

  1. #1
    Join Date
    Dec 1969

    Default SQL returning value

    I have a small problem about the returning value from a stored procedure.<BR>i want to delete a user (if they exist in the database) and then return a custom message if the deleting of the user was succesfull<BR>through ASP<BR><BR>heres my SQL statment<BR>CREATE PROCEDURE [dbo].[del_user_newsletter] <BR><BR>/* get user */<BR><BR>@userEmail varchar(50)<BR><BR>AS<BR> If exists (<BR> Select *<BR> From user <BR> Where userEmail = @userEmail<BR> )<BR><BR> Begin<BR> Delete <BR> From user <BR> Where userEmail = @userEmail<BR> <BR> select @errmsg = 0<BR> END <BR><BR> Else<BR><BR> Begin<BR> select @errmsg = 1<BR> END<BR>GO<BR><BR>--------------------------------------------------------------<BR><BR>ASP code<BR><BR> <BR> Call OpenConn<BR> <BR> SET rs = conn.Execute("del_user_newsletter &#039;" & userEmail & "&#039;")<BR><BR> IF rs("errmsg") = 0 Then<BR> msg = "change_delete"<BR> Else<BR> msg = "change_false" & "&userFirstName=" & Server.URLEncode(userFirstName) & "&userEmail=" & Server.URLEncode(userEmail)<BR> End IF<BR> Call CloseConn <BR> set rs = nothing<BR><BR>------------------------------------------------------------------<BR><BR>Error message<BR><BR>ADODB.Recordset error &#039;800a0cc1&#039; <BR><BR>Item cannot be found in the collection corresponding to the requested name or ordinal. <BR><BR><BR>--------------------------------------------------------------------<BR><BR>when executing the sql statment from the queryanalyser i get errmsg = 0 or 1 so it returns a value.<BR><BR>TIA <BR><BR>/Magnus<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: SQL returning value

    To solve your problem, simply do:<BR><BR>IF rs(0) = 0<BR><BR>instead of <BR><BR>IF rs("errmsg") = 0<BR><BR><BR>but you could simplify the proc to this<BR><BR><BR>CREATE PROCEDURE [dbo].[del_user_newsletter] <BR><BR>@userEmail varchar(50) <BR><BR>AS <BR><BR>DELETE FROM [User] WHERE userEmail = @userEmail<BR>SELECT @@ROWCOUNT <BR><BR><BR>(also use rs(0))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts