Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dephi ADO - Stored procedures

Posted on 2013-01-28
8
Medium Priority
?
489 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 38

Accepted Solution

by:
Geert Gruwez earned 666 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 334 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
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!

 
LVL 38

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 38

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 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 666 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

610 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