473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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

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

Jul 30 '06 #1
5 5435
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

Jul 30 '06 #2

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

Jul 30 '06 #3
(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
Jul 30 '06 #4
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
Jul 30 '06 #5
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

Jul 31 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
1
by: Pragati | last post by:
Hi ! I have an SQL query which im developing on the click of a command button on a form. but am stuck up in creating a recordset of that query. how should one connect to database? use adodb...
14
by: deko | last post by:
For some reason this does not seem to be working... Am I missing something basic? Dim rst As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb Set rst = db.OpenRecordset("qryEmailS") '...
22
by: Gerry Abbott | last post by:
Hi all, I having some confusing effects with recordsets in a recent project. I created several recordsets, each set with the same number of records, and related with an index value. I create...
2
by: Lyn | last post by:
Hi, I am opening a form in Continuous mode to list the records from a recordset created in the calling form. The recordset object is declared as Public and is set into the new form's Recordset...
1
by: kchatel | last post by:
Is it possible in Access to do the following. I filtered my table to have the folowing values ProdID Color ------------------------ 2 Black 2 Red 2 Green Now is...
3
by: Bhavsan | last post by:
Here is what I am trying to do. Kindly, help me. 1. I'm creating a query dynamically based on User input using VBA (strSQL and DotSQL in the code below) 2. Executing the created query with in VBA...
4
by: Bob | last post by:
Hi all, I've got a table that I've imported and it has junk at the top of the table, so after import I run a delete query to remove the junk lines then I'm left with the field names I want for...
3
by: 150563a | last post by:
I have a form that has 5 sub forms on it. I want to know how to reference the form properties of these sub forms. This I can do but what I am having trouble with is substituting the form name...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.