Spaces in Column Aliases

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon Woods - 26 Sep 2006 16:51 GMT

Hi

I've this SQL

SELECT Item1,Item2,Item3,
CASE WHEN Item4='-99999' THEN NULL ELSE Item4 END AS
[IFMISSING(Item4, "NULL")],
CASE WHEN Item4='-99999' THEN '99' ELSE Item4 END AS
[IFMISSING(Item4, 99)]
FROM .....

I'm passing this through ADO to a SQL Server DB. Ideally I want to be able
to access the underlying data set via the column alias for the Case columns,
but there are spaces in it and it seems to be causing me a bit of a problem
in that if I have a look at the underlying column names the aliases are set
(without the Square brackets), but if I try and access by the alias either
with square brackets, with double quotes or with both, ADO throws an error.
Is there any way round this?

Thanks

Simon

Reply to this Message

Dmitriy Antonov - 27 Sep 2006 01:29 GMT

> Hi
>
[quoted text clipped - 18 lines]
>
> Simon

I would not recommend you to use such names for fields. Anyway, you can just
access recordset's field by ordinal position and check what is field's name.
Like here:

?rst.fields(3).name

This will show you exact name used by recordset. And, as I said, you can
always access fields by ordinal position. It even gives you some performance
gain (for a price of maintainability).

Dmitriy.

Reply to this Message

VB Forum / Database Access / September 2006

Spaces in Column Aliases