Printing a field with a hyphen in ?

[David]

Hi,

I have a field in a select query: 'Metal-XS' which is causing a problem
The recordset should look like: RS2("Metal-XS") but it says this does
not exist, and I cannot get the SQL to work in ASP when I add this
field. Is there any code way around this, or should I edit the db and
change/create a new field ?

Thanks

David

 

[McKirahan]

Hi,

I have a field in a select query: 'Metal-XS' which is causing a problem
The recordset should look like: RS2("Metal-XS") but it says this does
not exist, and I cannot get the SQL to work in ASP when I add this
field. Is there any code way around this, or should I edit the db and
change/create a new field ?

Don't know why a hyphenated field name fails.

Try enclosing it in brackets -- as is needed for reserved words:

RS2("[Metal-XS]").Value

Or maybe it just wants ".Value"...

 

[Bob Barrows [MVP]]

Hi,

I have a field in a select query: 'Metal-XS' which is causing a
problem The recordset should look like: RS2("Metal-XS") but it says
this does not exist, and I cannot get the SQL to work in ASP when I
add this field. Is there any code way around this, or should I edit
the db and change/create a new field ?

I would recommend the latter. Nonstandard characters should definitely
be avoided*, and if you have the opportunity to eliminate them, you
should take it.
If you can't do this for some reason, you will need to remember to
surround the field name with brackets [] when referencing it in a query
run via ADO.


* Also, reserved keywords should be avoided:
http://www.aspfaq.com/show.asp?id=2080

 

[Dave Anderson]

Don't know why a hyphenated field name fails.

Try enclosing it in brackets -- as is needed for reserved words:

RS2("[Metal-XS]").Value

That suggestion will definitely cause an error with the MSSQL OLE DB
provider. If the SELECT statement looks anything like this...

SELECT [Metal-XS] FROM ...

....then the correct way to read the value is:

RS2.Fields("Metal-XS").Value

 

[Dave Anderson]

Nonstandard characters should definitely be avoided*, and
if you have the opportunity to eliminate them, you should
take it.

Why do you say that, Bob? For that matter, how do you define "nonstandard"?
According to this, there exist rules that dictate when delimiting is
required:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_6e9e.asp

But if delimited identifiers are acceptible to SQL Server, what demands that
they *definitely* be avoided?

There was a time when I might have said the same, but after the first time I
needed to deal with delimited identifiers (a vendor-supplied DB, of course),
it became -- for me -- a solved problem, and it only took a minute to
understand the concept. So what's the big deal?

 

[Bob Barrows [MVP]]

Why do you say that, Bob? For that matter, how do you define
"nonstandard"?

This is defined in the article you cite below. See "Rules for Regular
Identifiers"

According to this, there exist rules that dictate when
delimiting is required:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_6e9e.asp

But if delimited identifiers are acceptible to SQL Server, what
demands that they *definitely* be avoided?

I should have said "in my opinion" (but I'm definitely not alone in that
opinion). Just because the rdbms provides a way for those characters to
be handled does not mean one should make it handle them (not that this
is creating any kind of overhead for the dbms - I'm not saying that).

I hate having to remember to use the brackets in special cases so I
avoid creating situations requiring their use. If I used brackets for
all object names, regular and irregular, then it would not be such a big
deal for me, I admit.

There was a time when I might have said the same, but after the first
time I needed to deal with delimited identifiers (a vendor-supplied
DB, of course), it became -- for me -- a solved problem, and it only
took a minute to understand the concept. So what's the big deal?

Yes, I've had to deal with vendor-supplied and legacy databases, and
cursed the creators every time I had to type a delimiter. I like to use
RapidSQL, which has a nice code generator. Unfortunately, that code
generator does not delimit object names, so I wind up having to go in
and insert delimiters where necessary.

 

[Aaron Bertrand [SQL Server MVP]]

But if delimited identifiers are acceptible to SQL Server, what demands

that they *definitely* be avoided?

Because they are a P.I.T.A. to everyone who has to remember when to put
square brackets around a name.

Also, there are bugs in SQL Server, e.g. many of the internal procedures for
iterating through databases will fail if there is a dash in the name,
because the name is not always properly delimited. If Microsoft can forget
to bullet-proof code, so can the rest of us.

There was a time when I might have said the same, but after the first time
I needed to deal with delimited identifiers (a vendor-supplied DB, of
course), it became -- for me -- a solved problem, and it only took a
minute to understand the concept. So what's the big deal?

The range of expertise for accessing a database will vary -- maybe you are
very smart compared to the next guy who will work on your system. Why force
delimiters when you don't need to? This seems like putting kilometers
instead of miles on the speedometers of US-based vehicles because the math
is easy and everyone else in the world is using metric. While and having
grown up in Canada this wouldn't be a problem for me, I can certainly
envision the /*avoidable*/ chaos.

What makes the alternative (leaving the column name as is, and requiring
delimiters) so desirable?

A

 

[Bob Barrows [MVP]]

This is defined in the article you cite below. See "Rules for Regular
Identifiers"

I should have said that I tend to define this standard a little more
strictly. I doubt you will find an object name containing anything but
aA-zZ in any database I create. I will use underscores, but not for
objects that will be used in sql statements (constraint names, idex
names, etc.)

But this is just my prejudice. I'm not sure what lead me in that
direction. Maybe it was the first time I had a VB function bomb due to
the absence of delimiters in a sql statement (back when I was using
dynamic sql).

 

[Dave Anderson]

Also, there are bugs in SQL Server, e.g. many of the
internal procedures for iterating through databases
will fail if there is a dash in the name, because the
name is not always properly delimited.

I did not know this. I was under the impression that SQL Server uses the
internal identifiers [id column in sysobjects] for internal processes.

...Why force delimiters when you don't need to?

I generally don't. But I wouldn't be afraid to use them if I felt there were
a good reason to do so. More to the point, I do not care when someone else
does, and I certainly wouldn't admonish them for doing so. In my opinion, it
is no different than dealing with case sensitivity in JScript or having to
Dim your variables with Option Explicit in VBScript.

...What makes the alternative (leaving the column name
as is, and requiring delimiters) so desirable?

Well, that's a good question. Why would case sensitivity in JScript be
desirable when the alternative is "easier"? Why would
document.getElementById() be desirable when document.all[] is "easier"? Why
does Rice play Texas[1]?



[1] Kennedy's answer to his own rhetorical question is quite relevant here.