database time field question

[safra]

Hi,

I just started with delphi and am experimenting a bit with databases.

I have two questions regarding a time field in a paradox table:

1) I have a field table 'Time' (Type set to Time). When I try to insert rows I get the following error:

'class EDBEngineError with message: Invalid use of keyword. Token: Time'

Changing the field name solves the problem but now the new fieldname is displayed as the column name in the grid. I would like it to display 'Time'. Is this possible?

2) Apparantly the default format for a time field in Paradox is hh:mm:ss. Is it possible to only show hh:mm in the grid?

Many thanks,
Raoul

 

[LucieLastic]

hi

Is it a DBGrid you're using? If so, then

1) double click the Query component to show the Fields editor. If not fields are showing, right click inside the Fields Editor and 'Add All fields', it may prompt you to log in. Then click on the relevant 'time' field and change it's name in the DisplayLabel property to 'Time'.

2) When you've changed the DisplayLabel, change the DisplayFormat property to hh:mm, that should do it.

Let us know if you have any further problems with it.

lou

 

[DaZZleD]

the first problem could be solved by using a select statement like this: (say your field is now named myTime)

'select myTime as Time from myTable';

for the second, there are several sollutions:
the first is to try from BDE Admin to go to configuration, open System, open Formats, select Time and set Seconds to FALSE.
the second is to try to take the seconds out from the select statement:
'select substr(myTime, 0, 5) from myTable';

if you still need help, post back.

 

[safra]

Thanks for helping!

AnnaDapter, I think I am missing something. When I right click on the Query component and select 'Add all fields' I get a message "No sql statement available"? I can't figure out how to add the fields.

Dazzled, both sql queries return errors. The first (select myTime as Time) returns the same error as when I use Time as the field name. The second sql statemetn returns:'Capability not supported'?

I also set SECONDS to false in BDE admin and clicked 'apply' but the time field still displays hh:mm:ss ?

Any ideas what I can try?

Thanks,
Raoul

 

[lespaul]

When I right click on the Query component and select 'Add all fields' I get a message "No sql statement available"?

in the properties of your Query component, there is one called SQL, you need to enter your SQL there. I usually put the basic query without a where statement then in the code I:

qry.SQL.Clear;
qry.SQL.Add('SELECT * FROM TABLE WHERE FIELDNAME = ' + strParameter);
qry.Active := True;

Once you have your basic SQL in the properties, you will be able to select from the field list.

HTH

Leslie

 

[safra]

Ah, yes problems solved!

Thanks a lot
Raoul

 

[safra]

Ok, I know understand how to change displayName and format by adding the fields to the field editior of the Query component.

But how do you get access to the properties of each field when you select the fields dynamically through the sql query?

In other words, you keep the field editor empty and you select the fields by for example:

Q1.SQL.Add('select myTime,Name,Players from resdb.db');

Or do you not have access to the individual field properties this way?

Thanks,
Raoul

 

[DaZZleD]

Q1.SQL.Add('select myTime,Name,Players from resdb.db');

by that command you select the fields myTime, Name, Players from all the records of the database.

what properties do you want to access?

 

[safra]

For example, I would like to change the format of myTime into hh:mm instead of hh:mm:ss and change the DisplayName into 'Time'?

I was wondering, do I first have to declare an instance of myTime (TTimeField) in the TDBGrid after the query statement to access the properties of the myTime field or is that already done because myTime is included in the sql query?

Thanks,
Raoul

 

[DaZZleD]

if you don't create the fields with the wizard, then all the fields in the query are automatically included in the DBGrid with the names in the query. Therefore myTime will be included with the name you assign in the query.

 

[LucieLastic]

hi

"how do you get access to the properties of each field when you select the fields dynamically"

Have you tried something like this? Your query may have to opened before calling these, try it before and after the .open:-

Query.FieldByName('MyTime').DisplayName := 'Time';
Query.FieldByName('MyTime').DisplayFormat := 'hh:mm';

Hope this helps
lou

 

[safra]

Thanks,

Dazzled, in other words, you have no control over the properties if you do not use the wizard? The problem is that when you add fields to the wizard and one of these fields is not included in the sql query it raises an exception. What I am trying to do is allow the final user to select which fields he would like to include in the dbgrid. The selected fields in the sql query is based on the users choice. Perhaps I am not using the right method for achieving this, but I feel this must be possible while you still have control over the display name and display format of certain fields.

AnnaDapter, when I use:

Query.FieldByName('MyTime').DisplayName := 'Time';

I get the error 'cannot assign to a read-only property' during compilation. So I tried:

Query.FieldByName('MyTime').ReadOnly := False;
Query.FieldByName('MyTime').DisplayName := 'Time';

But I still get the same error. And when I try:

Query.FieldByName('MyTime').DisplayFormat := 'hh:mm';

I get the error: 'Undeclared identifier: DisplayFormat'

I feel perhaps there is a solution as 'Query.FieldByName('MyTime').ReadOnly := False;' does not raise an error.

Any ideas?

btw. I get these errors both before and after Query.Open.

Thanks,
Raoul

 

[LucieLastic]

Sussed it!

Example sql:
select MyTime, MyDate from MyTable

NOTE: the number in the Fields[] bit, should be the index of the field in your select statement - the first field (MyTime) starts at 0, and MyDate has index of 1 etc.

So, for the MyTime field:-

(qq.fields[0] as TDateTimeField).displayformat := 'HH:mm';
qq.fields[0].displayLabel := 'Time';


For the MyDate field
(qq.fields[1] as TDateTimeField).displayformat := 'dd mmm, yyyy';
qq.fields[1].displayLabel := 'Date';

lou

p.s. Don't forget, if you change the field order in your select statements, then change the fields' indexes to match.

 

[safra]

Great, thanks AnnaDapter! It is working now although I think I messed up something else as I now get a weird message "Table is not Indexed" when I run the application. But when I run the sql statement the dbgrid is filled correctly.

Thanks a lot!
Raoul

 

[LucieLastic]

Are you calling Refresh? as I think that can cause the 'Table not indexed' message, close and re-open the table instead.

lou

 

[safra]

I found the problem. While messing with the Table properties to get something to work I also set 'CachedUpdate' to True. Setting it back to False solved the problem.

Raoul