from TQuery-proc to TADOQuery proc

Hi,

I have a component called utfScheduleManager1 from  Jedi VCL
it has a event that looks like this:

procedure TPhotoOpMain.utfScheduleManager1LoadBatch(Sender: TObject;
  BatchName: String; BatchStartDate, BatchEndDate: TDate);
var
  Appt : TJvTFAppt;
  NewAppt : Boolean;
begin
  With NeedApptsQuery do
    Begin
      // Set the query parameters so the query will return
      // all appointments for the given resource that fall
      // on the given date.
      ParamByName('D1').AsDate := BatchStartDate;
      ParamByName('D2').AsDate := BatchEndDate;
      ParamByName('SchedName').AsString := BatchName;

      // Next, loop through the returned records to add the data
      Open;
      First;
      While not EOF do
        Begin
          // Request an appointment object from the server
          utfScheduleManager1.RequestAppt(FieldByName('ApptID').AsString,
            Appt, NewAppt);

          // If it is a newly loaded appt we want to set its properties
          If NewAppt Then
            Begin
              Appt.SetStartEnd(FieldByName('StartDate').AsDateTime,
                               FieldByName('StartTime').AsDateTime,
                               FieldByName('EndDate').AsDateTime,
                               FieldByName('EndTime').AsDateTime);
              Appt.Description := FieldByName('Description').AsString;
              Appt.AlarmEnabled := FieldByName('AlarmEnabled').AsBoolean;
              Appt.AlarmAdvance := FieldByName('AlarmAdvance').AsInteger;

              // Now manage the Appt --> Schedule(s) relationship
              With ApptSchedulesQuery do
                Begin
                  ParamByName('ApptID').AsString := Appt.ID;
                  Open;
                  First;
                  While not EOF do
                    Begin
                      Appt.AddSchedule(FieldByName('SchedName').AsString);
                      Next;
                    End;
                  Close; // ApptSchedulesQuery
                End;
            End;
          Next; // NeedApptsQuery record
        End;
      Close;  // NeedApptsQuery
    End;
end;

It calls for 2 TQuery components called: NeedApptsQuery & ApptSchedulesQuery.
I have change these components for 2 ADOQuery-Components with the same name.
I have changed the event but get stuck at line:

    =>    utfScheduleManager1.RequestAppt(FieldByName('ApptID').AsString, Appt, NewAppt);

I get the error message:
EDatabaseError with message 'NeedApptsQuery: Field 'ApptID' not found'.

I have the event that I have changed put in the code-section.
Does someone know the answer to my prolbem and willing to help me.

Greetings,

Peter Kiers

procedure TMainForm.utfScheduleManager1LoadBatch(Sender: TObject;
  BatchName: string; BatchStartDate, BatchEndDate: TDate);
var
  Appt : TJvTFAppt;
  NewAppt : Boolean;
begin
  With NeedApptsQuery do
    Begin
      // Set the query parameters so the query will return
      // all appointments for the given resource that fall
      // on the given date.

      Parameters.ParamByName('D1').Value := BatchStartDate;
      Parameters.ParamByName('D2').Value := BatchEndDate;
      Parameters.ParamByName('SchedName').Value := BatchName;

      // Next, loop through the returned records to add the data
      Open;
      First;
      While not EOF do
        Begin
          // Request an appointment object from the server

          utfScheduleManager1.RequestAppt(FieldByName('ApptID').AsString,
            Appt, NewAppt);




          // If it is a newly loaded appt we want to set its properties
          If NewAppt Then
            Begin
              Appt.SetStartEnd(FieldByName('StartDate').AsDateTime,
                               FieldByName('StartTime').AsDateTime,
                               FieldByName('EndDate').AsDateTime,
                               FieldByName('EndTime').AsDateTime);
              Appt.Description := FieldByName('Description').AsString;
              Appt.AlarmEnabled := FieldByName('AlarmEnabled').AsBoolean;
              Appt.AlarmAdvance := FieldByName('AlarmAdvance').AsInteger;

              // Now manage the Appt --> Schedule(s) relationship
              With ApptSchedulesQuery do
                Begin
                  Parameters.ParamByName('ApptID').Value := Appt.ID;
                  Open;
                  First;
                  While not EOF do
                    Begin
                      Appt.AddSchedule(FieldByName('SchedName').AsString);
                      Next;
                    End;
                  Close; // ApptSchedulesQuery
                End;
            End;
          Next; // NeedApptsQuery record
        End;
      Close;  // NeedApptsQuery
    End;
end;

Open in new window

LVL 1
peterkiersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Geert GOracle dbaCommented:
1: what is the select statement of NeedApptsQuery
2: what is the table definition of the tables in the where clause ? (column names)

check the column name.
typo or missing probably
0
 
Geert GOracle dbaCommented:
ugh ... in the from clause that should be
0
 
cyberkiwiCommented:
Was there a calculated column in the TQuery that did not get copied to the TADOQuery?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Geert GOracle dbaCommented:
for ado queries you have to add the parameters and set the type yourself
you can do this at design time too.

check the parameters of the query in the form designer

0
 
peterkiersAuthor Commented:
Here are the settings:
  object NeedApptsQuery: TADOQuery
    Connection = ADOConnection
    CursorType = ctStatic
    Parameters = <
      item
        Name = 'SchedName'
        Attributes = [paNullable]
        DataType = ftString
        Size = 510
        Value = Null
      end
      item
        Name = 'D1'
        Attributes = [paNullable]
        DataType = ftDateTime
        NumericScale = 255
        Precision = 255
        Size = 510
        Value = Null
      end
      item
        Name = 'D2'
        Attributes = [paNullable]
        DataType = ftDateTime
        NumericScale = 255
        Precision = 255
        Size = 510
        Value = Null
      end>
    SQL.Strings = (
      'Select * From GroupLink, GroupAppt'
      '  Where (SchedName = :SchedName)'
      '    And (GroupLink.ApptID = GroupAppt.ApptID)'
      '    And ((StartDate >= :D1) and (EndDate <= :D2))')
    Left = 416
    Top = 248
  end

Open in new window

0
 
Geert GOracle dbaCommented:
you probably have 2 times ApptId in your column list

you'll have to be more specific with the sql statement

change
Select *

to
Select l.schedname, l.apptid, a. ... etc
From GroupLink l, GroupAppt a
Where (SchedName = :SchedName)  
  And (GroupLink.ApptID = GroupAppt.ApptID)
0
 
peterkiersAuthor Commented:
But the SQL works with the TQuery-component I used before.
Now I use a TADOQuery instead.

P.
0
 
peterkiersAuthor Commented:
I have got it:

utfScheduleManager1.RequestAppt(FieldByName('GroupLink.ApptID').AsString,Appt, NewAppt);

Peter Kiers

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Geert GOracle dbaCommented:
that's one other way, table_name.column_name

or using the alias
0
 
cyberkiwiCommented:
That's strange.
BDE (TQuery) normally names them ApptID and ApptID_1 and you are just lucky that your table order was GroupLink l, GroupAppt a.  The first one gets the one without _1.
Never thought ADO would put the entire table name as prefix, but then again, I never use queries with identical column names being returned!
0
 
Geert GOracle dbaCommented:
you could find the real column names in a query like this

var column_names: string;

column_names := '';
aquery.open;
for i := 0 to query.fieldcount-1 do
  column_names := format('%s, %s', [column_names, aquery.fields.fields[i].fieldname]);
showmessage(column_names);

0
 
Geert GOracle dbaCommented:
PeterKiers,
it's not very accurate your solution, it will only work roughly 10% of the times

cyberkiwi also pointed out your luck
0
 
Geert GOracle dbaCommented:
I have figure out my own solution.

... not completely !
you were guided to the solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.