hypen in field names

[itman]

Hi.

Using Visual Studio 2002 and ODBC DataReader, I cannot work out how
to get SQL queries to work where there are hypens in the field name.

eg.

No problem.
"SELECT PUB.debtor.Name FROM PUB.debtor"

Won't work.
"SELECT PUB.debtor.deb-accn FROM PUB.debtor"

It's something to do with that hypen. I've tried everything I can
think of - but I'm at a loss as to how to solve this.

Ideas will be helpful.

Thanks.

 

[wollu]

Have You tried
SELECT PUB.table.field~-name
Tilde could make it noninterpreting. Also a backslash could help.

 

[itman]

Thanks for the idea. Unfortunately neither worked.

I think " would work, but in VB.NET the SELECT statement is enclosed in ""

e.g

WORKS OK
"SELECT PUB.debtor.deb-accn FROM PUB.debtor"

WILL NOT COMPILE
"SELECT PUB.debtor."deb-accn" FROM PUB.debtor"

I've tried all kinds of combinations (including now tilde and backslash).

 

[Lee Curzon]

WILL NOT COMPILE
"SELECT PUB.debtor."deb-accn" FROM PUB.debtor"

Try chr(34) in place of quotes.

eg. "SELECT PUB.debtor." & CHR(34) & "deb-accn" & CHR(34) & " FROM PUB.debtor"

or use """" in place of CHR(34) in the offending areas.


Lee

 

[itman]

Thanks for the ideal Lee.

I just gave that ago, and all combinations I can come up with.

None worked.

Seems this issue of SQL and hypens has appeared many times on this forum, but as yet, nobody seems to have a workable solution.

Fair enough, some problems just have to be worked on.

But, don't Progress monitor this forum? If so, surely they could come up
with the answer to this problem, given how many times it has appeared
on this forum.

 

[itman]

ok. At least I'm clearer on the problem.

The hyphen is being interpreted as a minus sign by the Progress SQL preprocessor.

I have heard of the -esqlnopad parameter but I think that was only
for Progress 7 and 8, and SQL89.

I however am using the ODBC DataReader addon to Visual Studio 2002 to access a Progress 9.1D database via an ODBC connection configured with the Merant 3.60 32-bit Progress SQL92 v9.1D driver.

I'm guessing I'm not the first person to ever attempt this

So there must be some way of getting around the hypen issue in field names using some kind of delimiter(s) in the Select statement I posted.

Some useful ideas so far, but no solution that works yet.

 

[itman]

yippedy doo dah

Dim myQuery As String = " SELECT ""debtor"".""deb-accn"", ""debtor"".""Name"" FROM ""PUB"".""debtor"" "

This works

Thanks Lee, I knew you were on to something.

itman

 

[Lee Curzon]

Thanks for letting us know your solution. It's always good to know what a correct approach is, even if you find it yourself.

 

[trafsta]

We are using Visual Studio 2005 (the version that comes with SQL 2005 SP2) and an ODBC DataReader (Merant 3.60 32-bit driver) to pull data from a Progress DB and we are having the exact same problem as itman. However, we haven't been able to figure out how to use the DIM Variable method to get around the hyphen problem. Can anyone give any more details on how to define a variable under a Report Server Project in VS2005 and then use that variable to get around this hyphen problem?