populating dropdown using tables from two databases

[cfdyn]

I tried it with populating the values from the first qury to an array and using
that array inside

NOT IN clause of second query. But its giving error
I am using Coldfusion 5.0 with SQL Server-2000


************************************************** ******************************
*********************

*************************************

<CFQUERY NAME ="UserExists"
PASSWORD="#Request.DataSources.FirstDB.DBPassword# "

USERNAME="#Request.DataSources.FirstDB.DBUserName# "

DATASOURCE="#Request.DataSources.FirstDB.DSN#"

DBTYPE="#Request.DataSources.FirstDB.ConnectionTyp e#">
SELECT UserID
FROM Signatorie
</CFQUERY>

<CFSET UserArray = ArrayNew(1)>
<CFLOOP query="UserExists" >
<CFSET UserArray[CurrentRow[1]] = #UserID#>
</CFLOOP>

<CFQUERY NAME="GetUsers" PASSWORD="#Request.DataSources.SecondDB.DBPassword #"

USERNAME="#Request.DataSources.Syn_Security.DBUser Name#"

DATASOURCE="#Request.DataSources.SecondDB.DSN#"

DBTYPE="#Request.DataSources.SecondDB.ConnectionTy pe#">
SELECT UserID, UserName
FROM Users
WHERE UserID NOT IN(#UserExists#)
</CFQUERY>

************************************************** ******************************
*********************

*************************************Error:
Expression result cannot be converted to a string

Expressions used inside tags like CFOUTPUT, CFQUERY, CFMAIL, etc. must
evaluate to a value

that can be converted to a string for output or dynamic text accumulation
purposes. Complex

objects, such as queries, arrays, and COM/DCOM objects, cannot be represented
as strings.

************************************************** ******************************
*********************

*************************************
Is there any way to solve this pblm
Thanks

[zoeski80]

Hi cfdyn

At the moment you are trying to compare a UserID to a query object - this
isnt' possible. You need to make the results of the query into a list of values
before you can use it in the query.

See attached code.

Zoe

<CFQUERY NAME="GetUsers">
SELECT UserID, UserName
FROM Users
WHERE UserID NOT IN(#ValueList(UserExists.UserID)#)
</CFQUERY>

[jonwrob]

A good suggestion, but before that, I'd try and connect the two databases on
the server side. I'm not a SQL Server guy, so I'm not sure of the syntax, but
I know this is possible:

select UserID, UserName
from db2.Users
where UserID not in (select UserID from db1.Signatorie)

That'll save you some work on the CF side, and of course transfering the data
twice. Let the DB do what it's good at. Even if your DB's are on different
servers, this should still be possible.

JR

[mxstu]

I agree with jonwrob. I do it all the time.

-- same server syntax
from database.owner.object_name .

-- linked server syntax
from linked_server.catalog.schema.object

[cfdyn]

Thank You zoeski80, it works fine..
but the sql server query syntax is not working
suppose my owner name is slpy/admin and database name seconddb what exactly should I writeText

[cfdyn]

yeah got the correct syntax, but now its giving 'Permission' Error

[mxstu]

I'm not sure which database is which, but make sure the datasource you use is
logged in as a user that has permissions to both database objects. For example:


firstDatasource
======================
user name: sa
database: firstDatabase
* "sa" has permissions for tables in both databases

Note: Using the "sa" login is not recommended. This is just an example

secondDatasource
======================
user name: otherSQLUser
database: secondDatabase
* "otherSQLUser" only has permissions for tables in "secondDatabase"




This query would work

<cfquery name="test" datasource="firstDatasource">
SELECT UserID, UserName
FROM secondDatabase.otherSQLUser.Users
WHERE UserID NOT IN (
SELECT UserID
FROM firstDatabase.dbo.Signatorie
)
</cfquery>

.... but this query would fail because the "otherSQLUser" has no permissions
to table "Signatorie" ...

<cfquery name="test" datasource="secondDatasource">
SELECT UserID, UserName
FROM secondDatabase.otherSQLUser.Users
WHERE UserID NOT IN (
SELECT UserID
FROM firstDatabase.dbo.Signatorie
)
</cfquery>