Space in a column name + ASP

[baronmatthews]

OK, I am dealing with some tables I am inheriting that have column names with spaces (for instance ITEM NUMBER instead of ITEMNUM OR ITEMNUMBER OR ITEM_NUMBER, etc). I can't change the column names as there are too many dependencies that rely on the names. I can write in sqlplus, Access, etc, pretty much anything I need simply using tablename."column name" and that works fine.

My problem is when I am using ASP (classic ASP, not ASP.net) for a super simple report, I am unable to reference the columns with the spaces. If I wanted to say Item Number = xxx, it won't let me. I've tried every iteration I can think of, but I am not having any luck. I know the answer is looking me in the face, but alas, I am unable to see it right now. Any help would be great.

 

[baronmatthews]

Never mind, it would be that the very next thing I tried after posting here worked!

 

[SantaMufasa]

So, Baron, You are not going to leave "bread crumbs" for the next poor SAP (no pun intended) that needs to know how you dealt with your issue? <grin>

(I presume it has to do with always and forever using double quotes to surround every reference to the column name that contains the space, right?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[baronmatthews]

Nope, I sure don't want to leave bread crumbs for someone else! Also, you are correct, it did have to do with the double quote issue. It was the translation of the double quotes into the ASP that was part part of the problem. Here is my "solution" that I just posted in a cross referenced thread on the ASP group:

OK, so, if you are using classic ASP and you have to deal with badly named columns that you can't change (i.e. mine had spaces -- Item Number -- and also other characters like -- Item #), you would run them in sqlplus like tablename."column name"... Well, that is what was giving me trouble in ASP. What I found was that you had to do tablename.""column name"" within your sql statement. I actually tried that once, but then I found that if do have to use double quotes, you have to take into mind what the case of the column name is. For instance, Item Number is not the same as item number or ITEM NUMBER. I have always made it a point to keep all my sql statements in caps (no reason, just how I started doing it). That was really throwing me off until I found a post about the case with double quotes. Hope this helps someone in the future.