Stored procedure problem

  • Thread starter Thread starter AkAlan via AccessMonster.com
  • Start date Start date
A

AkAlan via AccessMonster.com

I have a stored procedure that is not returning what I expect it should. I am
passing the name of a Role and it should be passing me back a 1 if the user
is a member and a 0 if not. It always returns a zero regardless of wether the
user is a member or not. I will post both the Stored procedure and the VBA
code I'm using.

VBA code:


Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "pr_IsRoleMember"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@RoleName", adBSTR, adParamInput, 6, "PUBLIC")
cmd.Parameters.Append prm

cmd.Execute

If cmd(0) = 1 Then
MsgBox "True"
Else
MsgBox "False"
End If

Set cmd = Nothing
Set prm = Nothing


Stored Procedure:


CREATE Procedure pr_IsRoleMember
(
@RoleName sysname
)

AS
BEGIN
DECLARE @RetVal INTEGER


IF IS_MEMBER ('@RoleName') = 1
SET @RetVal = 1
ELSE IF IS_MEMBER ('@RoleName') = 0
SET @RetVal = 0
ELSE IF IS_MEMBER ('@RoleName') IS NULL
SET @RetVal = NULL


RETURN @RetVal
END
GO


I think I'm really close just can't quite get there. Thanks for any help.
 
Single quote around a variable??? It's « IF IS_MEMBER (@RoleName) = 1 »,
not « IF IS_MEMBER ('@RoleName') = 1 »
 
Once again you save me. I swear I will get this syntax stuff down soon.
Thanks Sylvain, you are the bomb!!

Sylvain said:
Single quote around a variable??? It's « IF IS_MEMBER (@RoleName) = 1 »,
not « IF IS_MEMBER ('@RoleName') = 1 »
I have a stored procedure that is not returning what I expect it should. I
am
Click to expand...
[quoted text clipped - 53 lines]
I think I'm really close just can't quite get there. Thanks for any help.
Click to expand...
Click to expand...
 
Don't expect to become really well acquainted with ADP if you don't buy a
few good books about SQL-Server and T-SQL.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


AkAlan via AccessMonster.com said:
Once again you save me. I swear I will get this syntax stuff down soon.
Thanks Sylvain, you are the bomb!!

Sylvain said:
Single quote around a variable??? It's « IF IS_MEMBER (@RoleName) = 1 »,
not « IF IS_MEMBER ('@RoleName') = 1 »
I have a stored procedure that is not returning what I expect it should.
I
am
Click to expand...
[quoted text clipped - 53 lines]
I think I'm really close just can't quite get there. Thanks for any
help.
Click to expand...
Click to expand...
Click to expand...
 
I have Sams Microsoft Access Developers guide to SQL Server, Programming MS
Office Access 2003, SQL Server 2000 Bible, Access 2000 and VBA Developers
handbook. If you could recommend any others I will buy them. I got seriously
rushed into converting from mdb to adp by paygrades above me even after I
advised against it, but circumstances beyond our control put me in this
position. I really am digging this adp and SQL stuff I'm just under the gun
to finish fast and therefore am relying partly on these user groups and the
kindness of people like yourself to save me some time. Believe me I have
these books open and my eye's are bleeding at the end of the day. Thanks so
much for all your help, you are my hero.

Sylvain said:
Don't expect to become really well acquainted with ADP if you don't buy a
few good books about SQL-Server and T-SQL.
Once again you save me. I swear I will get this syntax stuff down soon.
Thanks Sylvain, you are the bomb!!
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 
Back
Top