Member Avatar for bklurch

Already typed my post once, having to do it again....

With the following code in MS SQL 2K:

SELECT Users.*,
Trainers.FirstName AS TFirstName,
Trainers.Surname AS TSurname,
Trainers.DisplayAs AS TDisplayAs,
Trainers.DIRealname AS TDIRealname,
Trainers.UserID AS TUserID,
Trainers.TrainerExpiry AS TTrainerExpiry
FROM Users
LEFT OUTER JOIN Users AS Trainers
ON Users.UserTrainerID = Trainers.UserID

I receive the following error:

Ambiguous column name 'Username'

(Username IS a field in the Users table for verification)

It works fine when I use it in enterprise manager for testing, but when called in an asp page, it returns the error.

It's doing my head in at the mo, so if anyone has any idea why it might be doing this I'll be greatly appreciative!

Thanks in advance

BKLurch
:?: :!: :?:

  • 2 Contributors
  • 6 Replies
  • 416 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by bklurch
Member Avatar for hollystyles

Hmm a tricky one, how are you testing it in Enterprise Manager? I have only ever used Query Analyser.

How are you connecting to the db from ASP ODBC, OLE DB or SQLSERVER?

Maybe trying a different provider might sort it.

Member Avatar for bklurch

I'm using the 'SQL Server' driver and connect using:
Server.CreateObject("ADODB.Recordset")

Any help?

Member Avatar for bklurch

Oh yes, the SQL statement is being pasted into the SQL editor in enterprise manager, SQL is verifying it is correct.

Previously, I had omitted the SELECT Users.* bit and just had SELECT * and that was understandably returning ambiguous columns via both methods.

Member Avatar for hollystyles

Oh yes, the SQL statement is being pasted into the SQL editor in enterprise manager, SQL is verifying it is correct

Right so it's parsing the statement, but that's not the same as using it.
Try pasting and running it in Query Analyser.

I would replace the Users.* with a full list of columns you require, although I can't see why that should make a difference, but try it anyway.

Member Avatar for hollystyles

Are you referencing the recordset like this:

rs.fields("Username")

Possibly Username is in the select list twice?

(my ASP is rusty as I'm all .NET now)

Member Avatar for bklurch

All sorted, twas to do with selecting fields.

Thanking you muchly.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.