Yes your problem is definitely a string to integer problem.
When you
select foundOn
from dbo.menu
where menuId=#form.selTopLevel2#
you are getting back a string. So when the rest of the statement is compiled
it would be as though you were doing this...
select menuId, label, foundOn
from dbo.menu
where menuId IN ('11, 12, 13')
order by menuId
when what you want is
select menuId, label, foundOn
from dbo.menu
where menuId IN (11, 12, 13) -- no quotes
order by menuId
You could try doing it in two steps:
1. query for the found_on and assign to a variable.
<cfquery name="query1" datasource="#application.datasource#">
SELECT foundOn
FROM dbo.menu
WHERE menuId=#form.selTopLevel2#
</cfquery>
2. build the sql statement
<cfset ssql = "SELECT menuId, label, foundOn from dbo.menu WHERE menuId IN (" +
query1.foundOn + ") ORDER BY menuID />
3. exec the statement.
<cfquery name="query2" datasource=... >
exec (#ssql#)
</cfquery>
My string build and exec statements may not be quite right because I tend to do
things in stored procs. Here is how I would do it.
First create a stored proc in TSQL.
CREATE PROCEDURE dbo.usp_getMenus
@selTopLevel2 varchar(75)
AS
DECLARE @SSQL VARCHAR(1000)
DECLARE @foundOn varchar(75)
SELECT @foundOn = foundOn
FROM dbo.menu
WHERE menuId = @selTopLevel2
SELECT @SSQL = 'SELECT menuId, label, foundOn
FROM dbo.menu
WHERE menuId IN (' + @foundOn + ') ORDER BY menuID'
EXEC (@SSQL) -- PARENS ARE NECESSARY IN TSQL
GO
Then call <cfstoredproc>
<cfstoredproc procedure="usp_getMenus" datasource="#application.datasource#"
returncode="No" >
<cfprocparam
dbvarname="selTopLevel2 "
type="In"
cfsqltype="CF_SQL_VARCHAR"
value="#form.selTopLevel2#/>
<cfprocresult name="query2">
</cfstoredproc>
Then use the results of query2 in your page.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299530
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
