asked on

cf8 cfinput spry autosuggest - error invoking cfc

This code works;

<cfinput name="sfCname#counter#" id="sfCname#counter#" size="40" autosuggest="cfc:400comps.tsijobads.getCompanies({cfautosuggestvalue})" maxResultsDisplay="50">

<cffunction name="getCompanies" access="remote" returntype="string">
<cfargument name="term" type="any" required="false" default="">
<cfset var CnameQry = "">
<cfquery name="CnameQry" datasource="AS400" dbtype="ODBC" maxrows="50">
SELECT rtrim(ltrim(csnam1)) as cname, rtrim(ltrim(csstat)) as state, rtrim(ltrim(cscity)) as city, csrec## as companyid
FROM cstatic
WHERE UPPER(csnam1) LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(left(arguments.term,255))#%">
order by csnam1,cscity
</cfquery>
<cfreturn ValueList(CnameQry.cname)>
</cffunction>

---------------------------------------------------------------------------------------------------
This code does not;

<cfinput type="text" name="sfiname#counter#" id="sfiname#counter#" autosuggest="cfc:400comps.tsijobads.getIndividualsH({cfautosuggestvalue})" maxResultsDisplay="50">

<cffunction name="getIndividualsH" access="remote" returntype="string">
<cfargument name="term" type="any" required="false" default="">
<cfset var IndivQry = "">
<cfquery name="IndivQry" datasource="AS400" dbtype="ODBC" maxrows="50">
SELECT rtrim(ltrim(isfnam)) as FNAME, rtrim(ltrim(islnam)) as LNAME, rtrim(ltrim(istitl)) as ITITLE, iswfon as WPHONE, isrec## as RECNUM
FROM istatic
WHERE UPPER(islnam) LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(left(arguments.term,255))#%">
ORDER by islnam,isfnam
</cfquery>
<cfreturn ValueList(IndivQry.isfnam)>
</cffunction>

----------------------------------------------------------------------------------------------------
I don't think the mapped folder is the problem. I solved part of the problem by changing the function output type to "string" and returning a list. Some of the examples and cfml ref are just plain wrong. Hard to believe, huh.

I just noticed that I have a type of "text" on one cfinput and not the other. I just tested it without "text" and it didn't make a difference.

73Spyder

Nothing jumps right out, have you been able to run the 2nd query on the DB server itself without any errors?

larksys

ASKER

I tried using the following code but get a "file in use" error. That may have something to do with the original error.

<cfform id="f1form" name="f1form" action="jbrectest.cfm" method="post">
<cfset f1="">
<cfinput id="f1" name="f1" type="text">
<cfquery name="IndivQry" datasource="AS400" dbtype="ODBC" maxrows="5">
             SELECT rtrim(ltrim(isfnam)) as FNAME, rtrim(ltrim(islnam)) as LNAME, rtrim(ltrim(istitl)) as ITITLE, iswfon as WPHONE, isrec## as RECNUM
             FROM istatic
            WHERE UPPER(islnam) LIKE '#ucase(f1)#%'
ORDER by islnam,isfnam
FOR READ ONLY
      </cfquery>

<cfoutput query="IndivQry">
#IndivQry.isfnam#   #IndivQry.isfnam# <br />
</cfoutput>
</cfform>
</html>

73Spyder

I've used an AS400 before but this line " FOR READ ONLY " is prolly giving you the "file in use " error.

Try it without that.

73Spyder

Correction:

I've never used an AS400 before.

larksys

ASKER

With out the "For Read Only" I get the error. That's why I put it in. I was also getting "variable F1 not defined" before I put in the cfset. The orginal Spry Framework code still works on that table ISTATIC. I can import from ISTATIC into Excel.

ASKER CERTIFIED SOLUTION

larksys

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vee_Mod

Closed, 500 points refunded.
Vee_Mod
Community Support Moderator