Specifying field's data type in select statement

[Benjamin Pratt]

am having a problem with an ADO query. The SELECT portion of the
query is:

SELECT ID, true AS Achieved, DateAchieved, Prompt

Here, I am creating a calculated field where the value is always true. The
problem is when the result set is returned, the 'Achieved' data type is
defined as
ShortInt by ADO. I need it to be Boolean.

Is there some way I can explicitly define the datatype in the select
statement?
Something like:
SELECT ID, BOOLEAN(true) AS Achieved, DateAchieved, Prompt

I am using BCB6 & Jet4.0. The entire query is below.


SELECT ID, true AS Achieved, DateAchieved, Prompt
FROM ACHIEVEMENTS, BENCHMARKS
WHERE ID = BenchmarkID
AND LanguageID = :LanguageID
AND CategoryID=:CategoryID
AND LevelID=:LevelID
AND ID In (SELECT BenchmarkID FROM BENCHMARK_GRADES WHERE Grade = :Grade)
AND StudentID = :StudentID
UNION
SELECT ID, false AS Achieved, Null AS DateAchieved, Prompt
FROM BENCHMARKS
WHERE ID NOT IN (SELECT BenchmarkID FROM ACHIEVEMENTS WHERE StudentID =
:StudentID)
AND LanguageID=:LanguageID
AND CategoryID=:CategoryID
AND LevelID=:LevelID
AND ID In (SELECT BenchmarkID FROM BENCHMARK_GRADES WHERE Grade = :Grade)
ORDER BY ID

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps, explicitly changing the data type to boolean in VBA:

CBool(rs!Achieved)

It depends on what value your db (Oracle?) gives for True & False.

In VBA: True = -1, False = 0.
In MS SQL Server: True = 1, False = 0

Is the data type that important? In VBA any non-zero number is
evaluated as True and 0 as False.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd7dCIechKqOuFEgEQLFEACfWSt/POAzDqjpTBRiwLYsbg5OPGIAnRdR
ElbV+UkpcuSK+bwXsLDGcbMF
=UQf3
-----END PGP SIGNATURE-----

 

[Benjamin Pratt]

Thanks for replying.

I am using Jet4 and Borland CPPBuilder6. I am using ADO to execute the
query. When ADO returns the dataset, it creates a schema based on the SQL.
In this case, the autogenerated schema is assigning a DataType field of
ShortInt to what should really be a boolean field. The reason this is
important is that I have a data grid which looks at the schema and decides
how to display and edit the fields based on their type. Since the schema is
specifying that the field is a ShortInt, my data grid displays a "1" and
wants the user to enter a number in that field. If the schema specifies the
field as Boolean, then the data grid displays a check, and the user simple
toggles the field by clicking on it. That is the fuctionallity I am looking
for, and it depends on the autogenerated schema specifing that the field is
boolean.

Any ideas?