fixx17
asked on
MSACCESS SQL Error with ColdFusion
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.Dep tID
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?DeptI D=#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.Dep tID 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.
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.Dep
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?DeptI
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.Dep
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually, how would that work?
Because I am passing a variable from the link.
For instance:<a href="deptlookup.cfm?DeptI D=#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 #"
Because I am passing a variable from the link.
For instance:<a href="deptlookup.cfm?DeptI
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
ASKER
Ok, never mind,
I did it like this
WHERE tblDept.DeptID=<cfquerypar am cfsqltype="CF_SQL_INTEGER"
value="#url.DeptID#
Sorry about that.. thanks for the help.
I did it like this
WHERE tblDept.DeptID=<cfquerypar
value="#url.DeptID#
Sorry about that.. thanks for the help.
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
You have
Where DeptID= whatever
it should be
Where tblDept.DeptID= whatever
or
Where tblCust.DeptID= whatever
example
WHERE tblDept.DeptID=1