Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Activation of parameters in an ADO-Query

Posted on 2005-03-15
8
Medium Priority
?
291 Views
Last Modified: 2010-04-05
Hi,

what is necessary to activate a new set of parameters in an ado-query ?

In detail:

I prepare the sql-statements in an ado-query only at the beginning
  sql.clear;
  sql.add('select ...  from ...   where  field1 = :p1  and  field2 = :p2' )

from then on, only the parameters are set and sql.open is called.
  parameters.parambyname('p1').value:= ...;
  parameters.parambyname('p2').value:= ...;
  sql.open;

But it looks as if these parameters never get active, always the same record seems to be selected.
(sometimes you are on direct way to get mad)

a)  Is this impression true ?
b) what is necessary to activate the new parameter set ?
    (To start with sql.clear every time and build it up completely new takes a lot of time)

K.-P.BEcker

 
0
Comment
Question by:KPBecker
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 17

Accepted Solution

by:
mokule earned 750 total points
ID: 13549582
This should work i think

with ADOQuery1 do
  begin
  close;
  parameters.parambyname('p1').value:= ...;
  parameters.parambyname('p2').value:= ...;
  open;
  end;

mokule

0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13549711
What about to call (Prepared := True) before (Active := True):

  with ADOQuery1 do
  begin
    Active := False;
    SQL.Text := 'select ...  from ...   where  field1 = :p1  and  field2 = :p2';
    Parameters.Parambyname('p1').Value:= 'zombi';
    Parameters.Parambyname('p2').Value:= 'vampire';
    Prepared := True;
    Active := True;
  end;
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13549734
Excuse me about 'zombi' and 'vampire' Values, I have a big headache last 5 - 6 days, and I am almost mad ....
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Expert Comment

by:mokule
ID: 13549792
Well with Prepared You should be cautious.
I've just not long ago fall in AV with the code like Your above.
I must have done it this way
 with ADOQuery1 do
  begin
    Active := False;
    str := 'select ...  from ...   where  field1 = :p1  and  field2 = :p2';
    if SQL.Text <> str then
      begin
      SQL.Text := str
      Prepared := True;
      end;
    Parameters.Parambyname('p1').Value:= 'zombi';
    Parameters.Parambyname('p2').Value:= 'vampire';
    Active := True;
  end;

0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 13553734
Maybe you need to REfresh the Parameter-List :


with ADOQuery1 do
begin
      Close;
      Parameters.Refresh
      Parameters.ParamByName('p1').value:= ...;
      Parameters.ParamByName('p2').value:= ...;
      Open;
end;


Best regards,

The Mayor.
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 13555246
I just had the same porblem as KPBecker when working with Stored Procs.

I'm usinf Delphi 5  and SQL Server 2000.

I 'm pretty sure I found what was causing this strange behavior.
It is - and I'm not understand why - situated in the Stored Procedure Code itself (although in my case it is).

Here is the code of my Original Stored Proc :
CREATE PROCEDURE SP_GET_FIRST_MIXNR_FOR_LOGTABLE
@LogTableID int,
@MixNr varchar(20) output

AS

SELECT @MixNr = w.mix_nr
FROM Wagen w, Zetel z
WHERE w.Wagen_ID = z.Wagen_ID
      AND z.Zetel_ID = (SELECT MIN(zetel_id) FROM T_LOGDATA WHERE zetel_id > 1000000 and LOGTABLE_ID = @LogTableID)

if ( @MixNr IS NULL )
      set @MixNr = '0000000'

RETURN 0
GO




And now the code of the new one - which is always working in Delphi too :
CREATE PROCEDURE SP_GET_FIRST_MIXNR_FOR_LOGTABLE
@LogTableID int,
@MixNr varchar(20) output

AS

set @MixNr = (SELECT w.mix_nr
FROM Wagen w, Zetel z
WHERE w.Wagen_ID = z.Wagen_ID
      AND z.Zetel_ID = (SELECT MIN(zetel_id) FROM T_LOGDATA WHERE zetel_id > 1000000 and LOGTABLE_ID = @LogTableID) )

if ( @MixNr IS NULL )
      set @MixNr = '0000000'

RETURN 0
GO



As you can see, it was something in the assignment of my output-param @Mixnr.
When triggering the SP from within the MS Query Analyzer, it always works fine (both code).
But triggering the SP from Delphi-code returned always the same data when I once triggered the SP with a NULL-result (so the select-statement returned no records at all). In this case, the output-param was filled with the last value of a non-null select in the SP-Code.


Maybe that is also the cause in your case.
Please let met know if it has something to do with this in your case too.

Best regards,

The Mayor.
0
 

Author Comment

by:KPBecker
ID: 13653329
Hi,

still confused but on a higher level ...

I did some tests with your comments, here is what I found out.

The solution of  mokule (first comment) works, although I wanted to avoid the   qry.close / qry.open  which seems to be rather time-consuming.

esoftbg defines the sql-query again every time (That was what  I used: see question). The additional prepared:= true  gives no advantage.

Major:  The  'refresh'  (possibly without the need to close the query every time) seemed to be the best at the first glance. But whereever I placed this 'refresh' errors occured (with or without close).

Conclusion:  mokule gets the points

Thanks to all experts, of course !

KPBecker
0
 

Author Comment

by:KPBecker
ID: 13654488
Just an addition:

refresh  seems to destroy the list of parameters (After refresh, all parameters are unknown.)

Any explanation ?

KPBecker

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This is an update to some code that someone else posted on Experts Exchange. It is an alternate approach, I think a little easier to use, & makes sure that things like the Task Bar will update.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

572 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