Solved

Store Procedure Return More Than One Value

Posted on 2008-06-15
9
795 Views
Last Modified: 2013-12-25
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

0
Comment
Question by:aj85
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:appari
ID: 21790337
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
 
0
 

Author Comment

by:aj85
ID: 21790396
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
0
 
LVL 39

Expert Comment

by:appari
ID: 21790405
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.
0
 

Author Comment

by:aj85
ID: 21790425
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 39

Expert Comment

by:appari
ID: 21790442
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
0
 

Author Comment

by:aj85
ID: 21790522
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
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 21790550
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 U.UserName,U.Password,U.AccountType,isnull(U.ActualName,'') ActualName,IsNull(U.Designation,'') Designation,
                  isnull(U.AllowMultiLogin,0) AllowMultiLogin,isnull(U.AllowWebLogin,0) AllowWebLogin,isnull(U.URL,'') URL  
      From tblUsers  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
                  and U.UserName = @UserName and U.WindowsUserName = @WindowsUserName and U.IPAddress = @IPAddress
            and U.Branch = @Branch and U.Department = @Department
      
END  
Else
      Select '_000_' UserName,'_000_' Password,'_000_' AccountType,'' ActualName,'' Designation,'' AllowMultiLogin,'' AllowWebLogin, '_000_' URL  
END
0
 

Author Comment

by:aj85
ID: 21792615
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
0
 

Author Closing Comment

by:aj85
ID: 31467441
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
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now