Referencing colum names in recordset which is created by join - URGENT!

[mail]

Urgent help needed!

I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I have
the following problem:

If the join on two tables results on duplicate colum names (which
appear in both tables) I could reference them by using:

RECORDSET("TABLENAME.COLUMNAME")

However with SQLServer if I try this kind of reference I get an error
message.

How can between two colums with the same name from two differen tables?

Thanks in advance!

Adam

[Dan Guzman]

If you have duplicate column names in an ADO recordset, you can use ordinal
position so that the reference is unambiguous:

RECORDSET(0)
RECORDSET(1)

However, it's better to specify a column alias so that all column names in
the result are unique:

SELECT
t1.MyColumn AS Table1_MyColumn,
t2.MyColumn AS Table2_MyColumn
FROM dbo.MyTable1 AS t1
JOIN dbo.MyTable2 AS t2 ON
t2.MyColumn = t1.MyColumn

RECORDSET("Table1_MyColumn")
RECORDSET("Table2_MyColumn")

--
Hope this helps.

Dan Guzman
SQL Server MVP

<ma**@jazzis.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Urgent help needed!

I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I have
the following problem:

If the join on two tables results on duplicate colum names (which
appear in both tables) I could reference them by using:

RECORDSET("TABLENAME.COLUMNAME")

However with SQLServer if I try this kind of reference I get an error
message.

How can between two colums with the same name from two differen tables?

Thanks in advance!

Adam

[mail]

Dan Guzman wrote:

If you have duplicate column names in an ADO recordset, you can use ordinal
position so that the reference is unambiguous:

RECORDSET(0)
RECORDSET(1)

However, it's better to specify a column alias so that all column names in
the result are unique:

SELECT
t1.MyColumn AS Table1_MyColumn,
t2.MyColumn AS Table2_MyColumn
FROM dbo.MyTable1 AS t1
JOIN dbo.MyTable2 AS t2 ON
t2.MyColumn = t1.MyColumn

RECORDSET("Table1_MyColumn")
RECORDSET("Table2_MyColumn")

--
Hope this helps.

Dan Guzman
SQL Server MVP

<ma**@jazzis.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Urgent help needed!

I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I have
the following problem:

If the join on two tables results on duplicate colum names (which
appear in both tables) I could reference them by using:

RECORDSET("TABLENAME.COLUMNAME")

However with SQLServer if I try this kind of reference I get an error
message.

How can between two colums with the same name from two differen tables?

Thanks in advance!

Adam

Thanks Dan for your help.

I do know all this of course, I was just surprised by the difference in
behavior between MS Sqlserver and other database, which return the
table name as part of the name.

I have read quite a lot on the subject and some people claim that a
colum name should be UNIQUE in a database, i.e. rather than having

table1.column
table2.column

the design should be

table1.colum1
table2.colum2

This would solve ANY ambiguity as to colum names especially in joins.

What is your opinion on that?

P.S. Classis DB / SQL literature does not mention this as a
prerequisite for proper DB design.

Adam

[Erland Sommarskog]

(ma**@jazzis.com) writes:

I have read quite a lot on the subject and some people claim that a
colum name should be UNIQUE in a database, i.e. rather than having

table1.column
table2.column

the design should be

table1.colum1
table2.colum2

This would solve ANY ambiguity as to colum names especially in joins.

What is your opinion on that?

A very bad idea. In my strong opinion, columns that denote the same
entity should have the same name all tables, with one exception: there
are two such columns in the same table.

To take an example from the database I work with, there is a table
called currencies. The primary key is curcode, and there is an uncountable
number of table where currency codes appear. And these columns are
normally called curcode. But in the instruments tables there are three
curcode columns. One is simply called curcode, which is the currency the
instrument is traded in. Then there is dividendcurcode, which is the
currency dividens are paid in. Finally there is issuercurcode, which
I don't really know what it's good for.

But there is a grain of truth in the idea. In the currencies table
there is a column for the full name of the currency, and this column
is called "curname", not just "name". And the same goes for other
tables, they have a name column, but its never called just "name", but
the column name is unique to other name columns. Because, these name
column all describes different entities.

If you employ these rules there is good chance that you run into
these name clashes on client level.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Martijn Tonies]

I do know all this of course, I was just surprised by the difference in
behavior between MS Sqlserver and other database, which return the
table name as part of the name.

I have read quite a lot on the subject and some people claim that a
colum name should be UNIQUE in a database, i.e. rather than having

table1.column
table2.column

the design should be

table1.colum1
table2.colum2

This would solve ANY ambiguity as to colum names especially in joins.

What is your opinion on that?

P.S. Classis DB / SQL literature does not mention this as a
prerequisite for proper DB design.

Name the columns as they make sense.

Now, for a proper _resultset_ -- name the columns accordingly.

eg:

select table1.column as table1_column,
table2.column as table2_column
from ... join ... etc. etc ...

Problem solved.
--
Martijn Tonies
Database Workbench - development tool for MS SQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

[Dan Guzman]

I have read quite a lot on the subject and some people claim that a

colum name should be UNIQUE in a database, i.e. rather than having

snip <

This would solve ANY ambiguity as to colum names especially in joins.

What is your opinion on that?

IMHO, columns should be named appropriately rather than adding a
'uniqueifier' for convenience. For example, OrderNumber should be named
OrderNumber regardless of whether the column is in the OrderMaster table or
OrderDetails table. Naming OrderNumber something else will only obfuscate
it's purpose.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<ma**@jazzis.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

>
Dan Guzman wrote:

>If you have duplicate column names in an ADO recordset, you can use
ordinal
position so that the reference is unambiguous:

RECORDSET(0)
RECORDSET(1)

However, it's better to specify a column alias so that all column names
in
the result are unique:

SELECT
t1.MyColumn AS Table1_MyColumn,
t2.MyColumn AS Table2_MyColumn
FROM dbo.MyTable1 AS t1
JOIN dbo.MyTable2 AS t2 ON
t2.MyColumn = t1.MyColumn

RECORDSET("Table1_MyColumn")
RECORDSET("Table2_MyColumn")

--
Hope this helps.

Dan Guzman
SQL Server MVP

<ma**@jazzis.comwrote in message
news:11**********************@p79g2000cwp.googleg roups.com...

Urgent help needed!

I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I have
the following problem:

If the join on two tables results on duplicate colum names (which
appear in both tables) I could reference them by using:

RECORDSET("TABLENAME.COLUMNAME")

However with SQLServer if I try this kind of reference I get an error
message.

How can between two colums with the same name from two differen tables?

Thanks in advance!

Adam

Thanks Dan for your help.

I do know all this of course, I was just surprised by the difference in
behavior between MS Sqlserver and other database, which return the
table name as part of the name.

I have read quite a lot on the subject and some people claim that a
colum name should be UNIQUE in a database, i.e. rather than having

table1.column
table2.column

the design should be

table1.colum1
table2.colum2

This would solve ANY ambiguity as to colum names especially in joins.

What is your opinion on that?

P.S. Classis DB / SQL literature does not mention this as a
prerequisite for proper DB design.

Adam