Solved

from TQuery-proc to TADOQuery proc

Posted on 2010-08-20
13
769 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:peterkiers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
13 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33483983
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33483985
ugh ... in the from clause that should be
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33483994
Was there a calculated column in the TQuery that did not get copied to the TADOQuery?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33484002
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
 
LVL 1

Author Comment

by:peterkiers
ID: 33484029
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33484050
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
 
LVL 1

Author Comment

by:peterkiers
ID: 33484082
But the SQL works with the TQuery-component I used before.
Now I use a TADOQuery instead.

P.
0
 
LVL 1

Accepted Solution

by:
peterkiers earned 0 total points
ID: 33484136
I have got it:

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

Peter Kiers

0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33484168
that's one other way, table_name.column_name

or using the alias
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33484170
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33484189
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33484203
PeterKiers,
it's not very accurate your solution, it will only work roughly 10% of the times

cyberkiwi also pointed out your luck
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 33484204
I have figure out my own solution.

... not completely !
you were guided to the solution
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month3 days, 17 hours left to enroll

630 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