BorlandTalk.com Forum Index BorlandTalk.com
Borland discussion newsgroups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Stored Procedure Output parameter

 
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> Delphi Databases (ADO)
View previous topic :: View next topic  
Author Message
Alan
Guest
PostPosted: Sun Feb 13, 2005 5:37 am    Post subject: Stored Procedure Output parameter Reply with quote


In SQL Server 2000, I have a stored procedure which has one output parameter
and several input parameters to insert a new record into a table, and
returns the newly auto-increment ID:

CREATE PROC dbo.proc_AddSomething (
@ID INTEGER OUTPUT,
@Number VARCHAR(20),
@FirstName VARCHAR(20),
@LastName VARCHAR(20),
@TitleID VARCHAR(15),
@DOB SMALLDATETIME,
@GenderID CHAR(1),
@Address1 VARCHAR(20),
@Address2 VARCHAR(30),
@SuburbID INTEGER,
@Phone VARCHAR(10),
@Email VARCHAR(30),
@Notes TEXT,
@IdentificationID INTEGER,
@IdentificationNumber VARCHAR(20),
@DateJoined SMALLDATETIME)
AS
INSERT INTO Table1(Number, TitleID, LastName, FirstName, DOB, GenderID,
Address1, Address2,
SuburbID, Phone, Email, Notes, IdentificationID,
IdentificationNumber, DateJoined)
VALUES (@Number, @TitleID, @LastName, @FirstName, @DOB, @GenderID,
@Address1, @Address2,
@SuburbID, @Phone, @Email, @Notes, @IdentificationID,
@IdentificationNumber, @DateJoined)
SET @ID = SCOPE_IDENTITY()
GO

In Delphi 7,

function TPatronDataModule.Add(aObject: TMyObject): Boolean;
begin
Result := FALSE;
with AddStoredProc, aObject do
try
Close;
Parameters.ParamValues['Number'] := aObject.Number;
Parameters.ParamValues['TitleID'] :=
aObject.TitleID;
Parameters.ParamValues['FirstName'] :=
aObject.FirstName;
Parameters.ParamValues['LastName'] :=
aObject.LastName;
Parameters.ParamValues['DOB'] := aObject.DOB;
Parameters.ParamValues['GenderID'] :=
aObject.GenderID;
Parameters.ParamValues['Address1'] :=
aObject.Address1;
Parameters.ParamValues['Address2'] :=
aObject.Address2;
Parameters.ParamValues['SuburbID'] :=
aObject.SuburbID;
Parameters.ParamValues['Phone'] :=
aObject.Phone;
Parameters.ParamValues['Email'] :=
aObject.Email;
Parameters.ParamValues['Notes'] :=
aObject.Notes;
Parameters.ParamValues['IdentificationID'] :=
aObject.IdentificationID;
Parameters.ParamValues['IdentificationNumber'] :=
aObject.IdentificationNumber;
Parameters.ParamValues['DateJoined'] :=
aObject.DateJoined;
Open;
aPatronObject.ID := Parameters.ParamValues['ID'];
Result := TRUE;
finally
Close;
end;
end;

I got an exception message:
AddStoredProc: CommandText does not return a result set'

I have set the parameter ID as pdOutput and other parameters as pdInput.
It happened right after the Open statement.
Any idea ?




Back to top
Kevin Frevert
Guest
PostPosted: Sun Feb 13, 2005 9:08 pm    Post subject: Re: Stored Procedure Output parameter Reply with quote


"Alan" <nospam_alanpltse (AT) yahoo (DOT) com.au> wrote

Quote:
Open;
aPatronObject.ID := Parameters.ParamValues['ID'];
Result := TRUE;
finally
Close;
end;
end;

I got an exception message:
AddStoredProc: CommandText does not return a result set'

Use the Execute method instead of Open.

Good luck,
krf



Back to top
Alan
Guest
PostPosted: Mon Feb 14, 2005 10:03 am    Post subject: Re: Stored Procedure Output parameter Reply with quote


I can only find the ExecuteAction method of TADOStoredProc, however, it has
parameter of type TBasicAction, what should I pass as parameter ?

"Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote

Quote:

I got an exception message:
AddStoredProc: CommandText does not return a result set'

I have set the parameter ID as pdOutput and other parameters as pdInput.
It happened right after the Open statement.
Resultset referred to is a recordset not parameters.
Open method is used when a recordset is returned.
Execute is used to run the SQL when no recordset is returned.
--
Brian Bushay (TeamB)
[email]Bbushay (AT) NMPLS (DOT) com[/email]



Back to top
Kevin Frevert
Guest
PostPosted: Mon Feb 14, 2005 1:10 pm    Post subject: Re: Stored Procedure Output parameter Reply with quote
"Alan" <nospam_alanpltse (AT) yahoo (DOT) com.au> wrote

Quote:
I can only find the ExecuteAction method of TADOStoredProc, however, it
has
parameter of type TBasicAction, what should I pass as parameter ?


Sorry, I didn't see that you were using TADOStoredProc.

Try TADOStoredProc.ExecProc method.

Good luck,
krf



Back to top
Alan
Guest
PostPosted: Mon Feb 14, 2005 10:42 pm    Post subject: Re: Stored Procedure Output parameter Reply with quote
That's strange, why the F1 help does not show up there is ExecPro method of
TADPStroedProc ?

Quote:
Try TADOStoredProc.ExecProc method.



Back to top
Alan
Guest
PostPosted: Tue Feb 15, 2005 11:44 am    Post subject: Re: Stored Procedure Output parameter Reply with quote
The Help I found about TADOStoredProc was I select the TADOStoredProc
component on palette or on the datamodule, then press F1.

Quote:
I can only find the ExecuteAction method of TADOStoredProc, however, it
has
parameter of type TBasicAction, what should I pass as parameter ?

I should have written AdoStoredProc.execProc. Or you could use
TadoCommand.execute
I don't know where you are finding ExecuteAction for a TadoStoredproc

TadoStoredProc uses the standard Tparameters



Back to top
Display posts from previous:   
Post new topic   Reply to topic    BorlandTalk.com Forum Index -> Delphi Databases (ADO) All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
SEO toolkit © 2004-2006 webmedic.