Printing a field with a hyphen in ?

Discussion in 'ASP General' started by David, Sep 25, 2006.

  1. David

    David Guest

    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
    David, Sep 25, 2006
    #1
    1. Advertising

  2. David

    McKirahan Guest

    "David" <> wrote in message
    news:...
    > 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"...
    McKirahan, Sep 25, 2006
    #2
    1. Advertising

  3. David wrote:
    > 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
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Sep 25, 2006
    #3
  4. McKirahan wrote:
    > 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

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
    Dave Anderson, Sep 25, 2006
    #4
  5. Bob Barrows [MVP] wrote:
    > 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?




    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
    Dave Anderson, Sep 25, 2006
    #5
  6. Dave Anderson wrote:
    > Bob Barrows [MVP] wrote:
    >> 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"?


    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.



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Sep 25, 2006
    #6
  7. > 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
    Aaron Bertrand [SQL Server MVP], Sep 25, 2006
    #7
  8. Bob Barrows [MVP] wrote:
    > Dave Anderson wrote:
    >> Bob Barrows [MVP] wrote:
    >>> 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"?

    >
    > 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).

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Sep 25, 2006
    #8
  9. Aaron Bertrand [SQL Server MVP] wrote:
    > 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.

    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
    Dave Anderson, Sep 25, 2006
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mm
    Replies:
    1
    Views:
    6,057
  2. Gilbert Saint-Flour

    non-breakable hyphen

    Gilbert Saint-Flour, May 17, 2004, in forum: HTML
    Replies:
    2
    Views:
    1,834
    Philipp Lenssen
    May 17, 2004
  3. KellyJ
    Replies:
    0
    Views:
    420
    KellyJ
    Jul 7, 2006
  4. Joe Stateson
    Replies:
    3
    Views:
    406
    Joe Stateson
    Oct 25, 2007
  5. Prasad
    Replies:
    6
    Views:
    457
    Joe Kesselman
    Oct 31, 2007
Loading...

Share This Page