Solved

Dephi ADO - Stored procedures

Posted on 2013-01-28
8
475 Views
Last Modified: 2013-03-24
Hello,

I am using Delphi 2010 and SQL Server 2012.

I have an SP as follows:  It checks to see if a given user/password combination exists and returns the UserId if one is found.  It also sets the return value to 1 to indicate a user has been found.

 ALTER PROCEDURE [dbo].[DoesStandardUserExist]

	-- Add the parameters for the stored procedure here
	@Username nvarchar(50),
	@Password nvarchar(50),
	@UserId integer = null output

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  select @UserId = Users.Id from Users 
  where Users.UserId = @Username and Users.Password = @Password and Users.Status = 'A'
 -- If the login was found then return 1.  
  if not( @UserId is null) return 1 
  else return 0 ;
 
END

Open in new window

I have dropped a TADOStoreProc component on my DataModule and use it as follows:

  DoesStandardUserExist.Parameters.ParamByName('@UserName').Value := edtUserName.Text ;
  DoesStandardUserExist.Parameters.ParamByName('@Password').Value := edtPassword.Text ;
  DoesStandardUserExist.ExecProc ;

  if not VarIsNull( DoesStandardUserExist.Parameters.ParamByName('@UserId').Value ) then
  begin

Open in new window

BUT it appears that only the first time i execute the SP i get the correct return values.  After the first successful user/password match the values of @UserID and @Return_Value are always set to the last successful match.

They never get set to null (@UserId), or 0 (@Return_value)

The SP works as expected when called directly from Studio Manager.  Am i using ADOStoredProc correctly?
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 333 total points
ID: 38826084
just to be sure you get the correct values:

use this for an intermediate test:
  if not( @UserId is null) return 100
  else return 999;

Open in new window


also reset the output parameter before executing second time:
DoesStandardUserExist.Parameters.ParamByName('@UserId').Value := Null;

Open in new window


you may be getting the number of rows affected in result
0
 
LVL 19

Assisted Solution

by:Thommy
Thommy earned 167 total points
ID: 38826092
Try a parameter refresh before setting the parameter values:

DoesStandardUserExist.Parameters.Refresh;
0
 

Author Comment

by:soozh
ID: 38826253
Hello,

Thanks for the replies.

Am i quite certain the sp returns the correct result but i will make the changes you suggest and test again.

I have tried setting the @userid parameter to null before calling but it still came back with the previous result.

This is obviously a coding error on my side but i just can see where.  Am i correct to assume that an ExecProc call should execute the SP each time it is made, and that the sp parameters should be updated correctly after the call?

I can have a look at refreshing the parameters but it does not seem to be logical ...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38826273
why are you using return with a procedure ????

you use return with a function
0
 

Author Comment

by:soozh
ID: 38826279
my understanding that the value of the tsql "return" statement is returned in the parameter "@Return_Value"

Maybe that is wrong?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38826460
you program delphi ?
same logic applies for function/procedure

return is used for indicating succes or failure inside a proc
http://msdn.microsoft.com/en-us/library/ms187926.aspx

if you create the proc as below:
ALTER PROCEDURE [dbo].[DoesStandardUserExist]

	-- Add the parameters for the stored procedure here
	@Username nvarchar(50),
	@Password nvarchar(50),
	@UserId integer = null output

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  select @UserId = Users.Id from Users 
  where Users.UserId = @Username and Users.Password = @Password and Users.Status = 'A'
END

Open in new window


does it work then ?
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 333 total points
ID: 38826462
it's actually worse:
return in a proc actually generates an error

http://msdn.microsoft.com/en-us/library/ms174998.aspx
0
 

Author Comment

by:soozh
ID: 38826581
Hello,

I am pretty certain my SP works.  I am more interested in if i am calling it correctly from Delphi.

/richard
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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