Access stored procedures with parameters - Newbee question

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
terjelarsen
Posts: 3
Joined: Thu 25 Aug 2005 12:39
Location: Svelvik, Norway

Access stored procedures with parameters - Newbee question

Post by terjelarsen » Thu 25 Aug 2005 12:47

Hi!

I'm new to this forum and the components. I've used the all components but the storedproc component.

The question is quite simpel I've a stored procedure I'd like to pase parameters to and execute. The stored procedure:
------------------------------------------------------------------------------------
-- =============================================
-- Create procedure
-- =============================================
-- creating the store procedure

CREATE PROCEDURE SettInnSamtale
@KUNDENR varchar(10),
@DATO varchar(8),
@TID varchar(8),
@ANR varchar(20),
@BNR varchar(30),
@BRUKER varchar(10),
@DESTINASJON varchar(40),
@VARIGHET varchar(8),
@FAKTURERT varchar(1),
@INNPRIS float,
@UTPRIS float,
@DB float,
@Songfaktura varchar(20)
AS
BEGIN
INSERT INTO Samtaler(KUNDENR,DATO,TID,ANR,BNR,BRUKER,DESTINASJON,VARIGHET,FAKTURERT,INNPRIS,UTPRIS,DB,Songfaktura)
VALUES (@KUNDENR,@DATO,@TID,@ANR,@BNR,@BRUKER,@DESTINASJON,@VARIGHET,@FAKTURERT,@INNPRIS,@UTPRIS,@DB,@Songfaktura)

END


GO
-------------------------------------------------------------------------------

How do I access this from Delphi with the components?

Thanks in advance

Terje

terjelarsen
Posts: 3
Joined: Thu 25 Aug 2005 12:39
Location: Svelvik, Norway

Post by terjelarsen » Thu 25 Aug 2005 13:55

I found the soulution.

Thanks anyway.

Terje

eferres
Posts: 3
Joined: Sat 03 Sep 2005 23:13
Location: Coimbra-Portugal

Post by eferres » Sat 03 Sep 2005 23:16

Wouldn't you like to share your findings? I have the same problem that you had but I'm less lucky... :oops: :oops:

terjelarsen
Posts: 3
Joined: Thu 25 Aug 2005 12:39
Location: Svelvik, Norway

Post by terjelarsen » Sun 04 Sep 2005 09:30

Hi!

Sure I'll share my findings.

Here's the procedure I use:

procedure TForm1.PostTilDataBase;
begin
MSStoredProc1.Params.ParamValues['KUNDENR'] := CDRRec.KundeNr;
MSStoredProc1.Params.ParamValues['DATO'] := CdrRec.Dato;
MSStoredProc1.Params.ParamValues['TID'] := CdrRec.StartTid;
MSStoredProc1.Params.ParamValues['ANR'] := CdrRec.Anr;
MSStoredProc1.Params.ParamValues['BNR'] := CdrRec.BNr;
MSStoredProc1.Params.ParamValues['BRUKER'] := CdrRec.Bruker;
MSStoredProc1.Params.ParamValues['DESTINASJON'] := CdrRec.Destinasjon;
MSStoredProc1.Params.ParamValues['VARIGHET'] := CdrRec.Varighet;
MSStoredProc1.Params.ParamValues['FAKTURERT'] := CdrRec.Fakturtert;
MSStoredProc1.Params.ParamValues['INNPRIS'] := CdrRec.InnPris;
MSStoredProc1.Params.ParamValues['UTPRIS'] := CdrRec.UtPris;
MSStoredProc1.Params.ParamValues['DB'] := CdrRec.DB;
MSStoredProc1.Params.ParamValues['Songfaktura'] := CdrRec.SongFaktura;
MSStoredProc1.Execute;
end;

I hope this make it clearer.

Kind regards
Terje

eferres
Posts: 3
Joined: Sat 03 Sep 2005 23:13
Location: Coimbra-Portugal

Post by eferres » Sun 04 Sep 2005 11:24

It worked! Thanks indeed! My problem was that I was naming my StoredProcs 'SP_*' and the component didn't recognize them correctly. Renaming them with other names, made them work correctly: with your code and with other codes (as ParamByName, for example). Well, I share this one for other beginners as myself...

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Mon 05 Sep 2005 11:41

> I was naming my StoredProcs 'SP_*' and the component didn't recognize them correctly.

Please specify your exact SDAC version

eferres
Posts: 3
Joined: Sat 03 Sep 2005 23:13
Location: Coimbra-Portugal

Post by eferres » Mon 05 Sep 2005 20:36

In the "What's new" file it's quoted "21-Mar-02 New features in SDAC 1.30" as the newer date...

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Tue 06 Sep 2005 07:51

So, you use very old SDAC version. We advice to upgrade it.

Guest

Post by Guest » Tue 06 Sep 2005 14:31

It's rather my Delphi 4 I have to upgrade! I see no later SDAC for it...
By the way, does anybody have a code example to execute a StoredProc at run time, from the assignment of a new value for the StoredProcName until the final execution? If I prepare the SP component at design time, assigning values for each parameter, values that I may change at run time, the component works fine. But if I want to use the same component to execute different StoredProcs in the server, I recieve an error message stating something like "too many steps in the OLE DB instruction..." (original in portuguese...)

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Wed 07 Sep 2005 14:52

Please specify exact SDAC version from menu SDAC -> About

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Wed 07 Sep 2005 15:04

> But if I want to use the same component to execute different StoredProcs
> in the server
> I receive an error message

Please send us (sdac*crlab*com) a complete small sample that demonstrates the problem.

Post Reply