Board index » delphi » MS SQL 2000 and runtime date parameters

MS SQL 2000 and runtime date parameters

I have a tadoquery that I am modifying at runtime to make a selection based
on dates if the user chooses to do so. The same query is used without
parameters to return all rows. I am using the code below to add lines to the
query if needed but I am having a couple of problems.

1. The parameter is not called startdate/enddate once the parameters.refresh
has been called - its param1/param2 depending on how many have been added. I
was testing to see if startdate/enddate exist and then set them but with the
wrong name this obviously wont work. I also tried creating the parameter but
his appeared to have no effect.

2. The query returns no results although it should. I tested the query in
query analyzer and it works fine. I had this problem when I first started
using ado with access 2000 and swapping to parameters for date selection
solved all the issues. Is this not the case with SQL?

In the code below edtCostSummStart and edtCostSummEnd are both
TwwDBDateTimePicker types



  with qryEquipmentServiceCosts do begin
     // Change query to select all costs for a certain type
     SQL[1] := 'where qryEquipmentServiceCosts.EquipmentId = ' +
        qryMainTableEquipmentId.AsString +
        ' and qryEquipmentServiceCosts.JobCostType = ''' +
        sp_EquipmentServiceCostsSummaryJobCostType.AsString + '''';

     // Check for date selection on the main equipment summary tab
     if rbsCostSummPeriod.Checked then begin
        if edtCostSummStart.Date > 0 then begin
           // Add start date
           SQL.Add('and qryEquipmentServiceCosts.Date >= :StartDate');
           Parameters[0].Value := edtCostSummStart.Date;

        if edtCostSummEnd.Date > 0 then begin
           // Add end date
           SQL.Add('and qryEquipmentServiceCosts.Date <= :EndDate');
           if Parameters.Count = 1 then
              Parameters[0].Value := edtCostSummEnd.Date
              Parameters[1].Value := edtCostSummEnd.Date;

     SQL.Add('order by qryEquipmentServiceCosts.Date');



Re:MS SQL 2000 and runtime date parameters

I have just found that if I create the query with the parameters at design
time it works fine. It only goes wrong if I add the parameters at runtime.
Not sure why though....


Other Threads