If I understand correctly you are trying to have these date parameters of your stored procedure optional. Why not make them optional in the stored procedure? In your stored procedure when you define for example "@DOB datetime," Just change it to "@DOB datetime=null" This will set the parameter @DOB to null if it is not passed into the SP. (this is for SQL server, that is what you are using correct?)
Anthony On 7/15/05, Andy Mcshane <[EMAIL PROTECTED]> wrote: > OK, the code below is used to call the function > > <invoke object="application.playerreport" methodcall="InsertPageOne( > argumentcollection=DataFields )" returnVariable="InsertPageOne" /> > > All of my form fields are within the structure 'DataFields' and I know that > they are all there when it is passed to the CFC. > > Within my CFC my CFSTOREDPROC is as follows > > <cffunction name="InsertPageOne" access="public" returntype="query" > hint="Insert page one player report details"> > <cfstoredproc procedure="usp_insert_player_report_page_one" > datasource="#request.datasource#" > returncode="Yes" debug="Yes"> > <cfprocresult name="insertpageone" resultset="1"> > <cfprocparam type="IN" > value=#arguments.clubid# > cfsqltype = CF_SQL_INTEGER > [EMAIL PROTECTED]> > <cfprocparam type="IN" > value=#arguments.PLAYERID# > cfsqltype = CF_SQL_INTEGER > [EMAIL PROTECTED]> > ......other stuff here .... > <cfif len(arguments.dob)> > <cfprocparam type="IN" > value=#arguments.DOB# > cfsqltype = CF_SQL_DATE > [EMAIL PROTECTED]> > <cfelse> > <cfprocparam type="IN" > null="yes" > cfsqltype = CF_SQL_DATE > [EMAIL PROTECTED]> > </cfif> > <cfif len(arguments.fixdate)> > <cfprocparam type="IN" > value=#arguments.FIXDATE# > cfsqltype = CF_SQL_DATE > [EMAIL PROTECTED]> > <cfelse> > <cfprocparam type="IN" > null="yes" > cfsqltype = CF_SQL_DATE > [EMAIL PROTECTED]> > </cfif> > </cfstoredproc> > <cfreturn insertpageone> > </cffunction> > > The last part is giving me the problem. I have tried using 'isDate' as well > as the above which is just checking if there is any data there. If there is > nothing then I need to pass null. Unfortunately using the code above simply > loses the parameter so instead of my stored procedure receiving 19 parameters > as expected every blank date field loses one off the total i.e. 1 blank field > and then only 18 parameters are passed. I am going mad trying to solve this, > please help. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212009 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
