CODEWITHSUNDEEP
sql
Tom Gullen
Tom Gullen

Reputation: 61729

Simple SQL query throws error

Microsoft OLE DB Provider for SQL Server error '80040e14'

The column prefix 'tblUsers' does not match with a table name or alias name used in the query

The query being executed is:

SELECT tblProjects.projectName, tblUsers.username
FROM tblProjects
INNER JOIN
    tblUsers ON tblProjects.userID = tblUsers.ID
WHERE (tblProjects.ID = 190)

When I run this in SQL Server 2005 though, it runs fine and returns the correct result! In my code, this throws the error.

It is being invoked through a classic ASP script:

strSQL = "SELECT tblProjects.projectName, tblUsers.username FROM tblProjects INNER JOIN tblUsers ON tblProjects.userID = tblUsers.ID WHERE (tblProjects.ID = " & lngProjectID & ")" 
rsCommon.open strSQL, adoCon
    strProjectPath = "../projects/" & rsCommon("username") & "/" & rsCommon("projectName")
rsCommon.close

Upvotes: 2

Views: 172

Answers (4)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

Personally I do not see any reason why this would throw error. Query and the code seems fine. Can the problem be somewhere else? You can try adding brackets as suggested by @Micheal. Hope that works.

Upvotes: 2

Alex
Alex

Reputation: 14618

Try to prefix the table names with the schema. I.E.

SELECT [dbo].[tblProjects].[projectName], [dbo].[tblUsers].[username]
FROM [dbo].[tblProjects]
INNER JOIN
    [dbo].[tblUsers] ON [dbo].[tblProjects].[userID] = [dbo].[tblUsers].[ID]
WHERE ([dbo].[tblProjects].[ID] = 190)

if you use a schema other than dbo, specify it.
When you just run a query, maybe there is a default schema selected, unlike the case when you connect via a simple connection string and try to run this query.

Upvotes: 1

DRapp
DRapp

Reputation: 48139

Not sure, but is it POSSIBLE???? that the Ole DB provider is falsely choking on "INNER JOIN" instead of a simple "JOIN" and the token parsing by the engine is missing the alias tblUsers?

Upvotes: 1

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74340

For starters, I would try putting all table and column names in square brackets and see if the problem gets resolved:

SELECT [tblProjects].[projectName], [tblUsers].[username] 
FROM [tblProjects] 
INNER JOIN 
    [tblUsers] ON [tblProjects].[userID] = [tblUsers].[ID] 
WHERE [tblProjects].[ID] = 190

Upvotes: 1

Related Questions