MS Access project with SQL Server - Stored procedure problem

Author

Stored procedure problem

AkAlan via AccessMonster.com

2006-03-15, 8:25 pm

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.

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200603/1

Sylvain Lafontaine

2006-03-15, 8:25 pm

Single quote around a variable??? It's « IF IS_MEMBER (@RoleName) = 1 »,
not « IF IS_MEMBER ('@RoleName') = 1 »

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

"AkAlan via webservertalk.com" <u18147@uwe> wrote in message
news:5d4e5fde5381a@u
we...
>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.
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Fo...server/200603/1

AkAlan via AccessMonster.com

2006-03-15, 8:25 pm

Once again you save me. I swear I will get this syntax stuff down soon.
Thanks Sylvain, you are the bomb!!

Sylvain Lafontaine wrote:[color=darkred
]
>Single quote around a variable??? It's « IF IS_MEMBER (@RoleName) = 1 »,
>not « IF IS_MEMBER ('@RoleName') = 1 »
>
>[quoted text clipped - 53 lines]

--
Message posted via http://www.webservertalk.com

Sylvain Lafontaine

2006-03-15, 8:25 pm

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 webservertalk.com" <u18147@uwe> wrote in message
news:5d4f20a474e99@u
we...
> Once again you save me. I swear I will get this syntax stuff down soon.
> Thanks Sylvain, you are the bomb!!
>
> Sylvain Lafontaine wrote:
>
> --
> Message posted via http://www.webservertalk.com

AkAlan via AccessMonster.com

2006-03-15, 8:25 pm

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 Lafontaine wrote:[color=darkred
]
>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.
>
>[quoted text clipped - 9 lines]

--
Message posted via http://www.webservertalk.com

Sponsored Links