Stef Merlijn
asked on
TFDStoredProc param not found
Hi,
In my Delphi application I have a TFDStoredProc (name: aspCopyMyRecord) to access a MS SQL Server database.
Sometimes when the procedure below is executed, I get an error:
aspCopyMyRecord: Parameter '@ID' not found
The parameters in SQL Server Stored Proc are named like:
Can anybode see what might be wrong with my code?
Thanks.
In my Delphi application I have a TFDStoredProc (name: aspCopyMyRecord) to access a MS SQL Server database.
Sometimes when the procedure below is executed, I get an error:
aspCopyMyRecord: Parameter '@ID' not found
procedure TDM3.CopyMyRecord(vID, vID2 : Integer; CONST vCode : String);
begin
try
with aspCopyMyRecord do
begin
ResourceOptions.UnifyParam := False;
Close;
StoredProcName := 'dba_CopyMyRecord';
FetchOptions.Items := aspCopyMyRecord.FetchOptions.Items - [fiMeta];
Command.FillParams(aspCopyMyRecord.Params);
Prepare;
Params.ParamByName('@ID').Value := vID;
Params.ParamByName('@ID2').Value := vID2;
Params.ParamByName('@Code').Value := vCode;
ExecProc;
end;
finally
CheckReturn(aspCopyMyRecord);
end;
end;
The parameters in SQL Server Stored Proc are named like:
CREATE PROCEDURE dba_CopyMyRecord(
@ID INT
, @ID2 INT
, @Code nVarChar(50)
, @ReturnValue INT OUTPUT)
Can anybode see what might be wrong with my code?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How did you create the stored procedure in your program?
If I create the stored procedure in code (with MySQL) I do the following:
If I create the stored procedure in code (with MySQL) I do the following:
FScCreateUserProc:= TFDStoredProc.Create(Self);
with FScCreateUserProc do
begin
Connection:= FConnection;
StoredProcName:= 'ScCreateUser';
Params.Clear;
Params.Add('AUserName', ftString,45,ptInput);
Params.Add('AFirstName', ftString,45,ptInput);
Params.Add('ALastName', ftString,45,ptInput);
Params.Add('ARoleName', ftString,45,ptInput);
Params.Add('AId', ftString,4,ptOutput);
end;
After that you can set the values and execute the procedure:with FScCreateUserProc do
begin
Params.ParamByName('AUserName').AsString:= LoginName;
Params.ParamByName('AFirstName').AsString:= FirstName;
Params.ParamByName('ALastName').AsString:= LastName;
Params.ParamByName('ARoleName').AsString:= RoleName;
ExecProc;
Id:= Params.ParamByName('AId').AsInteger;
ASKER
Thanks for the help.
Both solution should probably work fine, but I've decided to use TADOStroredProc in stead of TFDStoredProc, as I kept getting errors from customers.
Both solution should probably work fine, but I've decided to use TADOStroredProc in stead of TFDStoredProc, as I kept getting errors from customers.
ASKER
Open in new window
But for some reason this doesn't always work.Depending on the DBMS, the parameter name may be prefixed with '@' or ResourceOptions.UnifyParam
Info on: ResourceOptions.UnifyParam
In my development enviroment it works fine as it is setup now, but some customers still generate the error now and than.