I'm trying to create a page with two lists consisting of data from two different sources. The first list is a table of real estate property listings that have been processed and the second is a list of properties *remaining* to be processed. First, I obtained the processed listings:
<CFQUERY name="GetRecord" dataSource="#DSN2"> SELECT MLS_number FROM VPT ORDER BY MLS_number </CFQUERY> Next I created a list from the first query: <CFOUTPUT query="GetRecord"> <CFSET list = "#GetRecord.MLS_number#"> </CFOUTPUT> To verify that the list was actually created, I ouptut the list to the screen with #list# Which produced this correct list of processed numbers: 87867 88623 89035 89094 89256 100336 100349 100506 100516 100519 100520 100521 100522 100708 100711 100713 100716 100719 100803 100804 100811 100815 100817 100868 101042 Then I tried to create a list of unprocessed listings from the other DB by comparing the MLS_Number to the list: <CFQUERY name="GetResidential" dataSource="#DSN1#"> SELECT MLS_Number FROM Residential WHERE List_Firm = #FirmID# AND MLS_Number NOT IN #list# ORDER BY MLS_Number </CFQUERY> This produces the following error: ODBC Error Code = 37000 (Syntax error or access violation) [MERANT][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '101042'. SQL = "SELECT MLS_Number, List_Price FROM Residential WHERE List_Firm = 175 AND MLS_Number NOT IN 101042 ORDER BY MLS_Number" First of all, I'm puzzled that it displays the last MLS number instread of th first. Secondly, I suspect I need to have the list separated by commas but it throws another syntax error if I include them in the CFSET tag. The results do not change whether I use single or double quotes or no quotes at all. So far, I am unable to find any other syntax for comparing the second query results to the list. Can anyone advise me? Dave Long -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321348 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
