asked on

Store Procedure Return More Than One Value

I have a SP that I am calling from my VB code, and passing in 5 parameters. The issue is that it is returning more that one user, it should only be the user that is matched to the "Username". I have stepped through the VB code, and verified that all of the values are there, however when I get to the values returned I have 4 rows that are returned. I am attaching a code snippet from the VB and the SP.

---VB.NET Code
 
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("Exec spLoginUser '" & ret & "','" & strWindowsUserName & "','" & strIPAddress & "','" & My.Settings.Branch & "','" & My.Settings.Department & "'", DB.connection)
Dim rdr As OleDb.OleDbDataReader
 
---SP Code
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLoginUser] 
@UserName as nvarchar(100),
@WindowsUserName as varchar(50),
@IPAddress as varchar(15),
@Branch as varchar(50),
@Department as varchar(50)
 
AS
BEGIN
SET NOCOUNT ON;
Select UserName,Password,AccountType,isnull(ActualName,'') ActualName,IsNull(Designation,'') Designation,isnull(AllowMultiLogin,0) AllowMultiLogin,isnull(AllowWebLogin,0) AllowWebLogin From tblUsers 
END  
Else
Select '_000_' UserName,'_000_' Password,'_000_' AccountType,'' ActualName,'' Designation,'' AllowMultiLogin,''AllowWebLogin, '_000_' URL  
END

Open in new window

appari

you are not using the parameters you passed to the SP anywhere in your SQL.
try this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLoginUser]
@UserName as nvarchar(100),
@WindowsUserName as varchar(50),
@IPAddress as varchar(15),
@Branch as varchar(50),
@Department as varchar(50)

AS
BEGIN
SET NOCOUNT ON;
if exists(Select 1 From tblUsers
      where UserName = @UserName and ActualName = @WindowsUserName and IPAddress = @IPAddress
            and Branch = @Branch and Department = @Department )
begin
      Select UserName,Password,AccountType,isnull(ActualName,'') ActualName,IsNull(Designation,'') Designation,isnull(AllowMultiLogin,0)             AllowMultiLogin,isnull(AllowWebLogin,0) AllowWebLogin From tblUsers
      where UserName = @UserName and ActualName = @WindowsUserName and IPAddress = @IPAddress
            and Branch = @Branch and Department = @Department
END
Else
begin
      Select '_000_' UserName,'_000_' Password,'_000_' AccountType,'' ActualName,'' Designation,'' AllowMultiLogin,''AllowWebLogin, '_000_' URL
end
END

aj85

ASKER

Hello thanks for the reply. When I execute the code you supplied I get this:

Msg 102, Level 15, State 1, Procedure spLoginUser, Line 56
Incorrect syntax near 'END'.

Please advise.

Thanks,
aj85

appari

did you post complete SP source or only part of it?
the source i posted dont have 56 lines. either post complete source here or check for missing or extra end statements.

aj85

ASKER

Hello ok I am posting the complete SP, most of it was in the original post:

----SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLoginUser]
@UserName as nvarchar(100),
@WindowsUserName as varchar(50),
@IPAddress as varchar(15),
@Branch as varchar(50),
@Department as varchar(50)

AS
BEGIN
SET NOCOUNT ON;
Declare @AccUser as nvarchar(100)
------------- Check IF User Has Access on the IP Address
SELECT @AccUser = U.UserName
FROM tblUsers AS U LEFT OUTER JOIN
tblPolicy AS P ON U.UserName = P.UserName
WHERE (U.IsActive = 1) AND U.UserName=@UserName AND P.Accessable=1 and P.IPAddresses=@IPAddress
------------- END - Check IF User Has Access on the IP Address

IF @AccUser is Not Null
BEGIN ------------------------------------
      Declare @MultiLogin as bit
      Declare @MaxLoginID as bigint

Select @MultiLogin=isnull(AllowMultiLogin,0) From tblUsers Where UserName = @UserName

IF @MultiLogin = 0
      BEGIN
      Update tblLoginDetails Set LogoffTime=Getdate() Where LogoffTime is Null and LoginID=(
            SELECT Max(LoginID) FROM tblLoginDetails Where UserName = @UserName)
            Insert into tblLoginDetails (UserName, LoginTime, WindowsUserName, IPAddress,Branch, Department)
                                          Values      (@UserName,Getdate(),@WindowsUserName,@IPAddress,@Branch,@Department)

            END
      ELSE
            BEGIN
            Insert into tblLoginDetails (UserName, LoginTime, WindowsUserName, IPAddress, Branch, Department)
                                          Values      (@UserName,Getdate(),@WindowsUserName,@IPAddress,@Branch,@Department)
      END

Select UserName,Password,AccountType,isnull(ActualName,'') ActualName,IsNull(Designation,'') Designation,isnull(AllowMultiLogin,0) AllowMultiLogin,isnull(AllowWebLogin,0) AllowWebLogin,isnull(URL,'') URL From tblUsers
      END
Else
      Select '_000_' UserName,'_000_' Password,'_000_' AccountType,'' ActualName,'' Designation,AllowMultiLogin,'' AllowWebLogin, '_000_' URL
END

appari

in your original post the sp is not complete,
try this
----SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[spLoginUser]
@UserName as nvarchar(100),
@WindowsUserName as varchar(50),
@IPAddress as varchar(15),
@Branch as varchar(50),
@Department as varchar(50)

AS
BEGIN
SET NOCOUNT ON;
Declare @AccUser as nvarchar(100)
------------- Check IF User Has Access on the IP Address
SELECT @AccUser = U.UserName
FROM tblUsers AS U LEFT OUTER JOIN
tblPolicy AS P ON U.UserName = P.UserName
WHERE (U.IsActive = 1) AND U.UserName=@UserName AND P.Accessable=1 and P.IPAddresses=@IPAddress
------------- END - Check IF User Has Access on the IP Address

IF @AccUser is Not Null
BEGIN ------------------------------------
      Declare @MultiLogin as bit
      Declare @MaxLoginID as bigint

      Select @MultiLogin=isnull(AllowMultiLogin,0) From tblUsers Where UserName = @UserName

      IF @MultiLogin = 0
            BEGIN
                  Update tblLoginDetails Set LogoffTime=Getdate() Where LogoffTime is Null and LoginID=(
                        SELECT Max(LoginID) FROM tblLoginDetails Where UserName = @UserName)

                  Insert into tblLoginDetails (UserName, LoginTime, WindowsUserName, IPAddress,Branch, Department)
Values (@UserName,Getdate(),@WindowsUserName,@IPAddress,@Branch,@Department)

       END
      ELSE
            BEGIN
                  Insert into tblLoginDetails (UserName, LoginTime, WindowsUserName, IPAddress, Branch, Department)
Values (@UserName,Getdate(),@WindowsUserName,@IPAddress,@Branch,@Department)
      END

      Select UserName,Password,AccountType,isnull(ActualName,'') ActualName,IsNull(Designation,'') Designation,
                  isnull(AllowMultiLogin,0) AllowMultiLogin,isnull(AllowWebLogin,0) AllowWebLogin,isnull(URL,'') URL
      From tblUsers
      where UserName = @UserName and WindowsUserName = @WindowsUserName and IPAddress = @IPAddress
and Branch = @Branch and Department = @Department

END
Else
Select '_000_' UserName,'_000_' Password,'_000_' AccountType,'' ActualName,'' Designation,'' AllowMultiLogin,'' AllowWebLogin, '_000_' URL
END

aj85

ASKER

Hello,

This executes, but I still have the same problem, all four users are returned, not the single one that should be. Is there something else that needs to be added?

Thanks,
aj85

ASKER CERTIFIED SOLUTION

appari

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

aj85

ASKER

Hello,

When I execute this from the VB code I do not get any return values with the "Where" clause added, and the application does not work. If I take out the clause it works but as it did before with all of the rows being retruned. Please advise if possible.

Thanks,
aj85

aj85

ASKER

Hello,
Your solution worked so I am awarding you the points. I had to make another adjustment in the VB code, but your SP code solved the overall issue.

Thanks again!
aj85