Re: Column_Name with periods in name
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Tue, 22 May 2007 07:06:25 -0400
pb wrote:
Thanks for your comments, but as a software developer I can't control
what users call fields in their databases,
You have my sympathies. I suspect other problems have yet to be discovered.
and there are other
databases being hooked into apart from SQL Server. It does actually
happen that periods are used in field names, which is how a user
discovered the error in my software. I don't see why rsSchema!
Column_Name returns an interpretation of the column name rather than
the real column name.
I suggested a very valid reason. Periods are used by many databases (not
just SQL Server) to form explicilty qualify table names. Also, ADO itself
uses periods for this purpose, which I suspect goes a long way toward
explaing the reason for this behavior.
If neither of those suggestions handles it, then you are going to have to
I'll try your suggestion to see what happens.
fall back on checking the retrieved column names, and if they contain a
hash, try running a sql statement that uses that name (something like
"select <suspect column name> from table where 1=2"), catching the error if
the object does not exist. At this point, I would display a message to the
user, explaining that periods in column names violate ANSI SQL standards
causing most client applications to have trouble processing that column
name, and strongly suggesting that they change the name of the column,
replacing the period. You might even offer to change the name of the column
for them. I would provide a way for the user to tell me to go ahead and
rename the column, or exit while he goes and changes the name of the column,
or go ahead and use the column as-is. If the latter option is selected, then
it's a matter of verifying that the offending character is a period, using
the method I previously described, and keeping in mind that you will need to
surround the column name with brackets to prevent the parser from choking on
it. Something like "select [my.column] from table where 1=2". You will need
to watch out for column names containing both # and period (my.field#3). You
should consider storing this metadata somewhere so you don't have to go
through this process every time the user runs your application.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
.
- References:
- Column_Name with periods in name
- From: pb
- Re: Column_Name with periods in name
- From: Bob Barrows [MVP]
- Re: Column_Name with periods in name
- From: pb
- Column_Name with periods in name
- Prev by Date: Re: snapshot isolation level
- Next by Date: Re: Snapshot Isolation Level - how to select?
- Previous by thread: Re: Column_Name with periods in name
- Next by thread: Re: snapshot isolation level
- Index(es):
Relevant Pages
|
|
