BorlandTalk.com

Bob Dalton · Guest

As Brian from Team B suggested I used a parameter in my SQL syntax statement
to make the SQL statement acceptable to both MS SQL Server and MS Access Jet
when dealing with dates. This worked find for the first iteration of the
statement but generated an EOleException "Data Type mismatch in crieria
expression" error if the date was changed on a second try. Probably
something obvious I am missing but not having used parameters before it is
new to me, and not terribly well documented. Here is the code I am using:

procedure TPurgeForm1.Button2Click(Sender: TObject);
begin
try
FiltCrit := '';
if MonthCalendar2.Date < MonthCalendar1.Date then MonthCalendar2.Date :=
MonthCalendar1.Date;

ScheduleDB.Parameters.ParambyName('dateParam1').value :=
MonthCalendar1.Date;
ScheduleDB.Parameters.ParambyName('dateParam2').value :=
MonthCalendar2.Date;
FiltCrit := 'select * from Schedule where SCH_STARTTIME >= :dateParam1 '
+
' AND SCH_ENDTIME <= :dateParam2 ';
FiltCrit := FiltCrit + ' order by SCH_STARTTIME';
ScheduleDB.CommandText := FiltCrit;
ScheduleDB.Active := True;
ScheduleDB.Requery;
ScheduleDB.First;

OrganizationDB.Active := True;
FormsDB.Active := True;
if FormsDB.Locate('FRM_Number', 31, []) = False then
begin
FormsDB.Insert;
FormsDBFRM_Number.AsInteger := 31;
FormsDBFRM_HeaderInfo.AsString := 'Equipment Schedule Report';
FormsDBFRM_ModuleName.AsString := 'Dispatch';
FormsDB.Post;
FormsDB.Refresh;
end;
if FormsDB.Locate('FRM_Number', 31, []) then
begin
ppReport1.Reset;
ppReport1.Print;
end;
finally
Application.ProcessMessages;
FormsDB.Active := False;
ScheduleDB.Active := False;
OrganizationDB.Active := False;
end;

Note: I made sure to include the parameters using the names above in the
TADODatSet parameters property and set the values accepted to DateTime.

Regards;

Bob Dalton

Henry · Guest

"Bob Dalton" <bob.dalton (AT) removeme_digitallogistics (DOT) com> skrev i en
meddelelse news:41717105$1 (AT) newsgroups (DOT) borland.com...

Wayne Niddery [TeamB] · Guest

Bob Dalton wrote:

Bob Dalton · Guest

Thanks to both of you for the tips. BTW the requery is because the
TADODataSet component is used in two different ways on the same for. I only
included the code for the first routine.

One of the problems I am finding with the ADO components when used with SQL
is the general lack of documentation for them in Delphi 7. Yes there is some
spotty and generall brief information, but not all that helpful.

I purchased the "Advanced Delphi Developers Guide to ADO (for Delphi 5)
which I found useful in explaining the various ADO properties, methods, and
events. While helpful there was little code showing ADO in actual use or
demonstrating the various things in use. A good examples is the parameters
property of the TADODataSet. The book and Delphi documentation explain it in
a roundabout fashion but do not actually *show* you the proper way to use
them and how or the sequence of events needed to make them work correctly,
nor the pitfalls to avoid. This leaves most of us, including yours truly,
using trial and error most of the time to come up with how best to use these
properties, methods and events.

I do like ADO but just wish it was better documented....

Regards;

Bob Dalton

"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote

Bob Dalton · Guest

Tried as you suggested
ScheduleDB.Parameters.ParambyName('dateParam1').AsDateTime := Date and got
an Error: "Undeclared identifier: AsDateTime".

BTW The TParameter[0].DataType is set to ftDateTime, the TParameter[0].Name
is dateParam1 and everything else is left at default.

Something I am doing wrong or missing?

BTW fuill code is now:

procedure TPurgeForm1.Button2Click(Sender: TObject);
begin
try
FiltCrit := '';
if MonthCalendar2.Date < MonthCalendar1.Date then MonthCalendar2.Date :=
MonthCalendar1.Date;

FiltCrit := 'select * from Schedule where SCH_STARTTIME >= :dateParam1 '
+
' AND SCH_ENDTIME <= :dateParam2 ';
FiltCrit := FiltCrit + ' order by SCH_STARTTIME';
ScheduleDB.CommandText := FiltCrit;
ScheduleDB.Parameters.ParambyName('dateParam1').AsDateTime :=
MonthCalendar1.Date;
ScheduleDB.Parameters.ParambyName('dateParam2').AsDateTime :=
MonthCalendar2.Date;
ScheduleDB.Active := True;
ScheduleDB.Requery;
ScheduleDB.First;

OrganizationDB.Active := True;
FormsDB.Active := True;
if FormsDB.Locate('FRM_Number', 31, []) = False then
begin
FormsDB.Insert;
FormsDBFRM_Number.AsInteger := 31;
FormsDBFRM_HeaderInfo.AsString := 'Equipment Schedule Report';
FormsDBFRM_ModuleName.AsString := 'Dispatch';
FormsDB.Post;
FormsDB.Refresh;
end;
if FormsDB.Locate('FRM_Number', 31, []) then
begin
ppReport1.Reset;
ppReport1.Print;
end;
finally
Application.ProcessMessages;
FormsDB.Active := False;
ScheduleDB.Active := False;
OrganizationDB.Active := False;
end;
end;

Regards;

Bob Dalton

Bob Dalton · Guest

Found the solution. The problem was the TMonthCalendar components. They
return a DATE and not a DateTime which is what the paraemeters ftDateTime
value expects. Here is the code which will work with eitther mS SQL Serevr
OR MS Access for dates and will hopefully save someone else the "head
bashing against brick wall" problems I experienced with this issue:

procedure TPurgeForm1.Button2Click(Sender: TObject);
begin
try
FiltCrit := '';
if MonthCalendar2.Date < MonthCalendar1.Date then MonthCalendar2.Date :=
MonthCalendar1.Date;

FiltCrit := 'select * from Schedule where SCH_STARTTIME >= :dateParam1 '
+
' AND SCH_ENDTIME <= :dateParam2 ';
FiltCrit := FiltCrit + ' order by SCH_STARTTIME';
ScheduleDB.CommandText := FiltCrit;
ScheduleDB.Parameters.ParambyName('dateParam1').DataType := ftDate;
ScheduleDB.Parameters.ParambyName('dateParam2').DataType := ftDate;
ScheduleDB.Parameters.ParambyName('dateParam1').value :=
MonthCalendar1.Date;
ScheduleDB.Parameters.ParambyName('dateParam2').value :=
MonthCalendar2.Date;
ScheduleDB.Active := True;
ScheduleDB.Requery;
ScheduleDB.First;

OrganizationDB.Active := True;
FormsDB.Active := True;
if FormsDB.Locate('FRM_Number', 31, []) = False then
begin
FormsDB.Insert;
FormsDBFRM_Number.AsInteger := 31;
FormsDBFRM_HeaderInfo.AsString := 'Equipment Schedule Report';
FormsDBFRM_ModuleName.AsString := 'Dispatch';
FormsDB.Post;
FormsDB.Refresh;
end;
if FormsDB.Locate('FRM_Number', 31, []) then
begin
ppReport1.Reset;
ppReport1.Print;
end;
finally
Application.ProcessMessages;
FormsDB.Active := False;
ScheduleDB.Active := False;
OrganizationDB.Active := False;
end;
end;

Thanks to all who tried to help me! It is you all who make the forums work
for everyone else!

Regards;

Bob Dalton

Bob Dalton · Guest

The first example assumes values of Date and not DateTime. Here is an
example using a DateTime value:

procedure TPurgeForm1.Button1Click(Sender: TObject);
begin
if MessageDlg('Are you sure you want to clear all scheduled items which
have an end date prior to today?',
mtConfirmation, [mbYes, mbNo], 0) = mrYes then
begin
try
OldCursor := Screen.Cursor;
Screen.Cursor := crHourglass;
FiltCrit := '';
FiltCrit := 'select * from Schedule where SCH_ENDTIME < :dateParam1';
FiltCrit := FiltCrit + ' order by SCH_STARTTIME';
ScheduleDB.CommandText := FiltCrit;
ScheduleDB.Parameters.ParambyName('dateParam1').DataType :=
ftDateTime;
ScheduleDB.Parameters.ParambyName('dateParam1').value := Date;
ScheduleDB.Active := True;
ScheduleDB.Requery;
ScheduleDB.First;
while not ScheduleDB.eof do
begin
if ScheduleDBSCH_ENDTIME.AsDateTime < Date then
begin
ScheduleDB.Delete
end
else
begin
ScheduleDB.Next;
end;
end;
finally
Screen.Cursor := OldCursor;
ScheduleDB.Active := False;
ShowMessage('All done. You may now close this form.');
end;
end;

Regards;

Bob Dalton