UDF Query

Report · May 03, 2006

I want to check a form variable against a value in the database, if that value is not there, I then want to insert into the datbase. If the value IS there, then I want to return what the database has. I can do this in the code, but since I want to do this in many different pages, I would like to make a UDF for this. I tried but it does not appear to be working. The code that is calling it is

<cfoutput>#getnewdate(FORM.newvar)#</cfoutput>

The function itself is

<cffunction name="getnewdate" returntype="query" output="false">
<cfargument name="newdatevar" type="string" required="yes">

<cfset var datequery = "">

<cfquery datasource="#REQUEST.datasource#" name="datequery" cachedwithin="#createTimespan(0,1,0,0)#">
SELECT *
FROM peakpol
WHERE date_entry LIKE '%#ARGUMENTS.newdatevar#%'
</cfquery>

<cfif #datequery.RecordCount# IS 0>
<cfquery datasource="#REQUEST.datasource#">
INSERT INTO peakpol(date_entry,
time1000,
time1100,
time1200,
time1300,
time1400,
time1500,
time1600,
time1700,
time1800,
time1900,
time2000)
VALUES ('#ARGUMENTS.newdatevar#',
'Available',
'Available',
'Available',
'Available',
'Available',
'Available',
'Available',
'Available',
'Available',
'Available',
'Available')
</cfquery>
<cflocation url="index.cfm">
<cfelseif #datequery.RecordCount# IS 1>
<cfreturn datequery>
</cfif>

</cffunction>

If the date in the form is new, and not in the database, the value is inserted into the db and the cflocation sends me back to the index page (where the function is being called). If the value IS in the database I get the following error.

####ERROR OUTPUT FROM SCREEN#####
The value returned from function getnewdate() is not of type query.
If the component name is specified as a return type, the reason for this error might be that a definition file for such component cannot be found or is not accessible.

The error occurred in C:\Inetpub\wwwroot\index.cfm: line 13

11 :
12 :
13 : <cfoutput>#getnewdate(FORM.newvar)#</cfoutput>
14 : </cfif>
15 : <body>

I would like it to be able to add it to the db if it isn't already there, and if it is to just return the values that it found in the db. Is this design completely off base?

Thanks.

Report · May 14, 2006

I can't be sure about this since I don't know what data's in your DB, but based upon the logic you've got I am guessing it might be due to the fact that your query returns more than 1 record. According to your logic if no record exists a cflocation will abort the current function. If EXACTLY 1 record exists then the query datequery is returned, but if more than 1 record is returned then nothing is done and CF will complain that there is no query being returned even though you specified that there would be a return type of query for your function.

-Tim

Report · May 14, 2006

You didn't show your entire function, so it's hard to say why it isn't working. I don't see a cfreturn tag anywhere.

By the way, given your stated objective, this:
WHERE date_entry LIKE '%#ARGUMENTS.newdatevar#%'
should be this:
WHERE date_entry = '#ARGUMENTS.newdatevar#'

Also caching the query is going to cause you problems as well.

Adobe Community

UDF Query