Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSACCESS SQL Error with ColdFusion

Posted on 2003-11-18
5
267 Views
Last Modified: 2013-12-24
OK

Here are two tables I am trying to join and output in my CF page.

tbldept
DeptID               Primary Key            Autonumber
Dept                  Text

tblCust
CustID               PrimaryKey             Autonumber
Fname               Text
Lname               Text
Phone                Text
Email                 Text
DeptID               Number



I created an SQL query with acess and here is the SQL it uses when I want to query Fname, Lname, Phone and Email.

SELECT tblDept.DeptID, tblCust.Fname, tblCust.Lname, tblCust.Phone, tblCust.Email
FROM tblDept INNER JOIN tblCust ON tblDept.DeptID=tblCust.DeptID
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
       value="#url.DeptID#"

Now on the page before, I have a link that looks like this.
<a href="deptlookup.cfm?DeptID=#DeptID#">Dept</a>
When they click on this it will take them to the page above.

This is the error I am getting.
-------------------------------------------------------------------------------------------------------------------------
Error Diagnostic Information
ODBC Error Code = S1000 (General error)


[Microsoft][ODBC Microsoft Access Driver] The specified field 'DeptID' could refer to more than one table listed in the FROM clause of your SQL statement.


SQL = "SELECT tblDept.DeptID, tblCust.Fname, tblCust.Lname, tblCust.Phone, tblCust.Email FROM tblDept INNER JOIN tblCust ON tblDept.DeptID=tblCust.DeptID WHERE DeptID=?"

Query Parameter Value(s) -

Parameter #1 = 5

Data Source = "SOFTTRACK"
------------------------------------------------------------------------------------------------------------------------

Now usually I just figure out diffent way to query the tables in what order, but this one is stumping me.

Can anyone help, this should not be this hard and for some reason, I feel like I am missing something.



0
Comment
Question by:fixx17
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:CFDevHead
ID: 9771892
You have to tell the database which table to test deptid
example

WHERE tblDept.DeptID=1
0
 
LVL 9

Accepted Solution

by:
CFDevHead earned 400 total points
ID: 9771915
The reason you got that error is that DeptID is not unique to that query meaning DeptID is in both tables.  That is why you need to reference the table before the felid like so

SELECT tblDept.DeptID, tblCust.Fname, tblCust.Lname, tblCust.Phone, tblCust.Email FROM tblDept INNER JOIN tblCust ON tblDept.DeptID=tblCust.DeptID WHERE tblDept.DeptID=1
0
 
LVL 1

Author Comment

by:fixx17
ID: 9772557
Actually, how would that work?

Because I am passing a variable from the link.

For instance:<a href="deptlookup.cfm?DeptID=#DeptID#">Dept</a>

then that number is passed over from the index.cfm page to deptlookup.cfm page.

That is why I have it like below.

WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
      value="#url.DeptID#"


OR are you saying I should write it like this.
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
      value="#url.tblDept.DeptID#"


0
 
LVL 1

Author Comment

by:fixx17
ID: 9772586
Ok, never mind,

I did it like this

WHERE tblDept.DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
       value="#url.DeptID#


Sorry about that.. thanks for the help.
0
 
LVL 9

Expert Comment

by:CFDevHead
ID: 9772589
The variable is not the problem.  Its the where clasue.

You have
Where DeptID= whatever

it should be
Where  tblDept.DeptID= whatever

or
Where  tblCust.DeptID= whatever
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question