TADOStoredProc and Parameters

[cpmasesa]

I have a query in MSAccess2K which expects 3 parameters.

In my delphi app i connect to the query using a TADOStoredProc.

when passing parameters, the following DOES NOT work:
with Orders do begin //this is the TADOStoredProc
Close;
Parameters.ParamByName('DateFrom').Value :=dpFrom.Date;
Parameters.ParamByName('DateTo').Value := dpTo.Date;
Parameters.ParamByName('aDateType').Value := cbDateTypes.Text;
Open;
end;

WHY? Using a normal TADOQuery i can pass parametersin the same fashion and it works properly. For the TADOStoredProc it works only with the 1st values passed, when called again it still shows records 4 the 1st values passed. Its as if it doesnot referesh the parameterlist

So i end up having to do it as follows:
with dbgList.DataSource.DataSet AS TADOStoredProc do begin
Close;
Parameters.Clear;
Parameters.CreateParameter('DateFrom', ftDateTime, pdInput, 0, dpFrom.Date);
Parameters.CreateParameter('DateTo', ftDateTime, pdInput, 0, dpTo.Date);
Parameters.CreateParameter('aDateType', ftString, pdInput, 13, cbDateTypes.Text);
Open;
end;

What am i missing? or is the way to do it with TADOStoredProc? (somehow i donot want to believe that thats the case!!)

TIA,

cpmasesa

 

[Zathras]

Not sure what the problem is. Are you spelling the parm names consistently to match the sp definition? Did you set the correct data types in the TParameters collection?

I don't do it that way. Instead of dropping an ADOStoredProc on the form, I do it all in code. Here is a demo:
[green]

{Use spSecGetUserGroups to populate FGroupList and FGroupDescriptions.}

[/color][blue]

procedure TdlgLogin.GetUserGroups(ALoginID: string);
var
  sp:TADOStoredProc;
  n: integer;
begin
  FGroupList.Clear;
  FGroupDescriptions.Clear;
  hSecMain.Connected := True;
  n := 0;
  sp := TADOStoredProc.Create( self );
  with sp do
    try
      Connection := hSecMain;
      ProcedureName := 'spSecGetUserGroups';
      with parameters do begin
        CreateParameter('@RETURN_VALUE', ftInteger, pdReturnValue, 0, n);
        CreateParameter('@AppLogin', ftString, pdInput, 20, ALoginID);
      end;
      Open;
      while (not EOF) do
        begin
          FGroupList.Add( FieldByName('GroupCode').AsString );
          FGroupDescriptions.Add( FieldByName('Description').AsString );
          Next;
        end;
      Close;
    finally
      sp.Free;
    end;
end;

[/color]

The sp looks like this (SQL Server):
[green]

/*
Name:    dbo.spSecGetUserGroups
Purpose: Returns list of groups that a user is a member of.
Revision History:
   Date         Name             Comments
---------- --------------- --------------------------
03/17/2003 Sheridan        Created.
*/

[/color][blue]

CREATE PROCEDURE dbo.spSecGetUserGroups 
  @AppLogin varchar(20)
AS
BEGIN
  SET NOCOUNT ON
  SELECT G.GroupID, G.GroupCode, G.Description
   FROM dbo.SecGroupUser GU
      INNER JOIN dbo.SecGroup G ON G.GroupID = GU.GroupID
      INNER JOIN dbo.SecUser U ON U.UserID = GU.UserID
   WHERE U.Login = LTRIM(RTRIM(@AppLogin))
   ORDER BY G.GroupCode
  RETURN(0)
END

[/color]

 

[cpmasesa]

Zathras,

I notice that you free th sp.

Essentially whenever you run your code you create a new instance of the TADOStoredProc.

In my case, i use the same instance of TADOStoredProc and just need to refresh the parameters. (it returns list of orders given start and end dates and a datetype. Whenever user changes dates or order types the param should be refreshed and the sp run)

Yes i am spelling the param names the same as they are declared in MS Access2k.

Try out my scenario and see whether you get same effect.

 

[Zathras]

Ok, I set it up your way and it works just fine (after setting up the parameters in the properties):
[red]

{ Populate a list box with user's permissions. }

[/color][blue]

procedure TForm1.GetUserPermissions(ApplicationID,UserID:integer);
begin
  ListBox1.Clear;
  with ADOStoredProc1 do
    try
      with parameters do begin
        ParamByName('@InApplicationID').Value := ApplicationID;
        ParamByName('@InUserID').Value := UserID;
      end;
      Open;
      while (not EOF) do
        begin
          ListBox1.Items.Add( FieldByName('PermissionCode').AsString );
          Next;
        end;
      Close;
    finally
    end;
end;

[/color][red]

{Test procedure GetuserPermissions}

[/color][blue]

procedure TForm1.Button1Click(Sender: TObject);
begin
  GetUserPermissions(StrToInt(Edit1.Text),2048);
end;

[/color]

 

[cpmasesa]

mmmmh! I will try it again and see what i am doing wrong and will let you know.

Ahaaaa! in Access the parameter is '[DateFrom]' while in the TADOStoredProc i am calling the parameter 'DateFrom'

I will try the change and see if it works, will let you know.

Thanks

 

[cpmasesa]

Zathras,

I've spent a whole hour trying to get it to work with no success!!

I matched the parameter names (even including the '[]' as put by access) with no success.

.....
Close;
Parameters.ParamByName('DateFrom').Value := dpFrom.Date;
Parameters.ParamByName('DateTo').Value := dpTo.Date;
Parameters.ParamByName('aDateType').Value := cbDateTypes.Text;
Open;
.....

just doesnot work for me!!

(dpFrom and dpTo are TDateEdit from RxLib and cbDateTypes is a TComboBox)

i noticed how ever that you cannot do:
Parameters.ParamByName('DateTo').AsDateTime := dpTo.Date

Maybe since 'Value' is variant (??) while dpTo.Date is DateTime its messing up somehow??

I am really stumped and wil have to use the work around i have till i get a solution somehow.

TFTH,

Clemens