Would trying to put an empty string into the agendatime cause any problems?
-----Original Message----- From: angela alston [mailto:[EMAIL PROTECTED]] Sent: 29 June 2002 05:08 To: CF-Talk Subject: Re: Stored procedure problem Thanks Joshua. I ran the proc in the analyzer as you suggested & it ran successfully, although, that was only one instance, not a loop. Could that make a difference? Also the sp is wrapped in a cftransaction with another sp that functions properly when on it's own. The code earlier was somewhat simplified. Thought it would be easier, but turns out more is better :) Here's the full code: stored proc: CREATE PROCEDURE spn_AddAgenda @attendeeid as integer, @eventid as integer, @exhibitorid as integer, @sessionid as integer, @agendatime as datetime, @notes as varchar(4000), @opt_in as integer AS BEGIN DECLARE @return_code as integer SET NOCOUNT ON select * from agenda where attendeeid = @attendeeid and eventid = @eventid and exhibitorid = @exhibitorid and sessionid = @sessionid if @@rowcount > 0 begin set @return_code = 1 end else begin insert into agenda ( attendeeid, eventid, exhibitorid, sessionid, begin_datetime, notes, opt_in) values ( @attendeeid, @eventid, @exhibitorid, @sessionid, @agendatime, @notes, @opt_in) if @@error = 0 set @return_code = 0 else set @return_code = -1 END return @return_code END CF: <cfloop list="#form.Meeting#" Index="sessionid"> <!--- Check database for the particular person and exhibitor & add if necessary---> <cfstoredproc datasource="#temp_vts_datasource#" procedure="spn_AddAgenda" returncode="Yes"> <cfprocparam type="IN" dbvarname="@attendeeid" value="#session.StAttendee.id#" cfsqltype="CF_SQL_INTEGER" > <cfprocparam type="IN" dbvarname="@eventid" value="#temp_vts_eventid#" cfsqltype="CF_SQL_INTEGER" ><!--- eventid is session var ---> <cfprocparam type="IN" dbvarname="@exhibitorid" value="0" cfsqltype="CF_SQL_INTEGER" > <cfprocparam type="IN" dbvarname="@sessionid" value="#sessionid#" cfsqltype="CF_SQL_INTEGER" > <cfprocparam type="IN" dbvarname="@agendatime" value="" cfsqltype="CF_SQL_TIMESTAMP"> <cfprocparam type="IN" dbvarname="@notes" value="" cfsqltype="CF_SQL_VARCHAR" > <cfprocparam type="IN" dbvarname="@opt_in" value="0" cfsqltype="CF_SQL_INTEGER" > </cfstoredproc> <!--- set up error handling ---> <cfset return = CFSTOREDPROC.STATUSCODE> <cfif #return# is "-1"> <!--- error ---> <cfelseif #return# is "0"> <!--- record added ---> <cfelseif #return# is "1"> <!--- already in agenda ---> </cfif> </cfloop> See anything wrong? Thanks. Angie ----- Original Message ----- From: "Joshua Tipton" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, June 28, 2002 11:22 PM Subject: RE: Stored procedure problem > Angela please past the code that is within your proc that error means > that something is wrong in it. Try coping all the code out of your proc > or running the proc in query analyzer with the inputs that would be > passed to it via the cfstoredproc tag. > > Joshua Tipton > > -----Original Message----- > From: angela alston [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 28, 2002 11:10 PM > To: CF-Talk > Subject: Stored procedure problem > > Hello, > > I have many checkboxes all named "meeting" with value populated by a > query. > They are submitted to another template where I'm *trying* to loop thru > the > list & insert a record for each one that was checked. > > Can this be done? It seems simple, but I'm getting this error: > > ODBC Error Code = 22005 (Error in assignment) > [Microsoft][ODBC SQL Server Driver]Invalid character value for cast > specification > SQL = "sp_meeting" > Data Source = "datasource" > > The datatypes & parameter order exactly match the stored procedure. I'm > using CF 4.5/SQL7.0. > > > Here's the code: > > <cfloop list="#form.Meeting#" Index="meetingid"> > <cfstoredproc datasource="datasource" procedure="sp_meeting" > returncode="Yes"> > <cfprocparam type="IN" dbvarname="@attendeeid" > value="#session.Attendeeid#" cfsqltype="CF_SQL_INTEGER" > > <cfprocparam type="IN" dbvarname="@eventid" value="#session.eventid#" > cfsqltype="CF_SQL_INTEGER" > > <cfprocparam type="IN" dbvarname="@meetingid" value="#meetingid#" > cfsqltype="CF_SQL_INTEGER" > > </cfstoredproc> > </cfloop> > > > Any help is greatly appreciated. > Angie > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
