Stored Procedure Not Working
I have a MS SQL database and I have a stored procedure I'm trying to
call with the following code..
cfset FutureTime=DateAdd('', 3, Now())
cfoutput#futureTime#/cfoutput
cfstoredproc procedure=GetDate dataSource=#datasource#
cfprocparam type=IN CFSQLType=CF_SQL_DATE value=#FutureTime#
dbVarName=@Future
cfprocparam type=IN CFSQLType=CF_SQL_VARCHAR
value=#session.UserID# dbVarName=@UserID
cfprocresult name=GetDate
/cfstoredproc
Here is the Stored Procedure
ALTER PROCEDURE [dbo].[GetDate]
@UserID varchar(50),
@Future Datetime
AS
BEGIN
SET NOCOUNT ON;
SELECT [DateTaken]
, [DateExpire]
from TestResults
Where UserID = @UserID
and DateExpire @Future
Order by Score DESC
END
Here's the kicker... It works perfect on Firefox... IE and Opera, I
get the following coldfusion error...
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data
type varchar to datetime.
First of all... Wha???
Second of all, if there is error in the code, why isn't firefox having
a problem displaying it. My boss (who knows a bit about coldfusion
says that firefox may be ignoring the error But that doesn't seem
logical to me..
Can someone shed some light on this?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308430
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored Procedure Not Working
Hmm, right off the bat, I don't think the browser would make any difference unless you have ColdFusion code that behaves differently depending on the browser. Perhaps something is being cached. Since getdate is the name of a function, maybe you should call your proc something else. Regardless, if appears your data types are mixed up. Your proc params list the date first and the varchar second, but the proc has the varchar first and the date second. ~Brad -Original Message- From: Phillip Vector [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 4:52 PM To: CF-Talk Subject: Stored Procedure Not Working I have a MS SQL database and I have a stored procedure I'm trying to call with the following code.. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308431 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored Procedure Not Working
On Tue, Jul 1, 2008 at 2:55 PM, Brad Wood [EMAIL PROTECTED] wrote: Hmm, right off the bat, I don't think the browser would make any difference unless you have ColdFusion code that behaves differently depending on the browser. Perhaps something is being cached. I cleared out the cache and still the same issue. Also, no browser specific code. Regardless, if appears your data types are mixed up. Your proc params list the date first and the varchar second, but the proc has the varchar first and the date second. I didn't know that it needs to be the same order. I switched it and it's working now. Thanks. :) Wierd though how firefox didn't error out. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308433 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Procedure Not Working
Named parameters didn't work in CF7. There was a Hotfix that made them work but I don't think it was ever part of the 7.0.1 or 7.0.2 release. I don't know if it made it into CF 8 or not. Chances are ColdFusion was defaulting to the usual practice of going off the order of the proc param tags. FWIW, running a trace on the database while you made the call would probably have shown the inputs going in the wrong place. ~Brad -Original Message- From: Phillip Vector [mailto:[EMAIL PROTECTED] I didn't know that it needs to be the same order. I switched it and it's working now. Thanks. :) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308436 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Stored Procedure Not Working, Help?
I use stored Procs as much as possible (as you should - much quicker). I would certainly suggest doing what Heather suggested by putting the @ before the dbVarName. also remeber that it does matter what order you declare your params in CF. They must be declared in the same order as they appear in the SP. -Original Message- From: Avi Flax [mailto:[EMAIL PROTECTED]] Sent: 22 August 2000 20:16 To: [EMAIL PROTECTED] Subject: Stored Procedure Not Working, Help? Sending this question in for a friend. Thanks for any replies! Do you do much with stored procedures in Cold Fusion? It's one thing I do little with, and I'm stuck. I keep getting an error from the SP saying it isn't getting @CustomerID, which it expects. Here's the code I'm using: CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" DBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" I just tried adding the DBVARNAME parameter but that didn't help. Help? :) -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Stored Procedure Not Working, Help?
Sending this question in for a friend. Thanks for any replies! Do you do much with stored procedures in Cold Fusion? It's one thing I do little with, and I'm stuck. I keep getting an error from the SP saying it isn't getting @CustomerID, which it expects. Here's the code I'm using: CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" DBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" I just tried adding the DBVARNAME parameter but that didn't help. Help? :) -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Stored Procedure Not Working, Help?
I've used stored procedures a little, and that looks pretty much OK. The "variable" attribute isn't needed for input parameters, though. The DBVarName should definitely help. All I can suggest is the obvious - you're passing in the wrong variable name or type, or calling the wrong stored procedure. Have you tried calling it from query analyzer? David Cummins Avi Flax wrote: Sending this question in for a friend. Thanks for any replies! Do you do much with stored procedures in Cold Fusion? It's one thing I do little with, and I'm stuck. I keep getting an error from the SP saying it isn't getting @CustomerID, which it expects. Here's the code I'm using: CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" DBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" I just tried adding the DBVARNAME parameter but that didn't help. Help? :) -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Stored Procedure Not Working, Help?
try dbvarname="@CustomerID" - Original Message - From: Avi Flax [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 22, 2000 2:15 PM Subject: Stored Procedure Not Working, Help? Sending this question in for a friend. Thanks for any replies! Do you do much with stored procedures in Cold Fusion? It's one thing I do little with, and I'm stuck. I keep getting an error from the SP saying it isn't getting @CustomerID, which it expects. Here's the code I'm using: CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" DBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" I just tried adding the DBVARNAME parameter but that didn't help. Help? :) -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Stored Procedure Not Working, Help?
I thought of that, but I looked at some old code I had and you don't have to do that... David Cummins "Heather R. Moll" wrote: try dbvarname="@CustomerID" - Original Message - From: Avi Flax [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 22, 2000 2:15 PM Subject: Stored Procedure Not Working, Help? Sending this question in for a friend. Thanks for any replies! Do you do much with stored procedures in Cold Fusion? It's one thing I do little with, and I'm stuck. I keep getting an error from the SP saying it isn't getting @CustomerID, which it expects. Here's the code I'm using: CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" DBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" I just tried adding the DBVARNAME parameter but that didn't help. Help? :) -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Stored Procedure Not Working, Help?
This is a multi-part message in MIME format. --038B626790AA5924CFF82AAF Content-Type: multipart/alternative; boundary="8CB5F653C5DC7DE914071AAA" --8CB5F653C5DC7DE914071AAA Content-Type: text/plain; charset=us-ascii; x-mac-type="54455854"; x-mac-creator="4D4F5353" Content-Transfer-Encoding: 7bit Could you get a copy of the Stored Procedure too? From memory though, you don't need to specify the VARIABLE parameters as they are used when the TYPE is INOUT or OUT And I suspect that you've left the bottom part of the script off, but make sure that the /CFSTOREDPROC is specified!! Avi Flax wrote: Sending this question in for a friend. Thanks for any replies! Do you do much with stored procedures in Cold Fusion? It's one thing I do little with, and I'm stuck. I keep getting an error from the SP saying it isn't getting @CustomerID, which it expects. Here's the code I'm using: CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" DBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" I just tried adding the DBVARNAME parameter but that didn't help. Help? :) -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. --8CB5F653C5DC7DE914071AAA Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit !doctype html public "-//w3c//dtd html 4.0 transitional//en" html Could you get a copy of the Stored Procedure too? pFrom memory though, you don't need to specify the VARIABLE parameters as they are used when the TYPE is INOUT or OUT pAnd I suspect that you've left the bottom part of the script off, but make sure that the lt;/CFSTOREDPROCnbsp; is specified!! brnbsp; brnbsp; brnbsp; brnbsp; pAvi Flax wrote: blockquote TYPE=CITESending this question in for a friend. Thanks for any replies! pDo you do much with stored procedures in Cold Fusion?nbsp; It's one thing I do brlittle with, and I'm stuck.nbsp; I keep getting an error from the SP saying it brisn't getting @CustomerID, which it expects.nbsp; Here's the code I'm using: plt;CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" brlt;CFPROCPARAM brTYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" brDBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" brlt;CFPROCPARAM brTYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" brlt;CFPROCPARAM brTYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" brlt;CFPROCPARAM brTYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" pI just tried adding the DBVARNAME parameter but that didn't help. pHelp? :) p-- brArchives: a href="http://www.mail-archive.com/cf-talk@houseoffusion.com/"http://www.mail-archive.com/cf-talk@houseoffusion.com//a brTo Unsubscribe visit a href="http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk"http://www.houseoffusion.com/index.cfm?sidebar=listsamp;body=lists/cf_talk/a or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body./blockquote /html --8CB5F653C5DC7DE914071AAA-- --038B626790AA5924CFF82AAF Content-Type: text/x-vcard; charset=us-ascii; name="nickslay.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Nick Slay Content-Disposition: attachment; filename="nickslay.vcf" begin:vcard n:Slay;Nick tel;cell:0414 810284 tel;home:02 4381 0284 tel;work:02 4381 0284 x-mozilla-html:FALSE url:http://www.webbods.com.au org:Webbods Pty Ltd adr:;;PO Box 4054;Copacabana;NSW;2251;Australia version:2.1 email;internet:[EMAIL PROTECTED] title:Technical Director x-mozilla-cpt:;3 fn:Nick Slay end:vcard --038B626790AA5924CFF82AAF-- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Stored Procedure Not Working, Help?
Is the customer id always going to get passed into the proc? Maybe in the procedure it should be = NULL??? Just tossing out ideas - Original Message - From: David Cummins [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 22, 2000 6:29 PM Subject: Re: Stored Procedure Not Working, Help? I thought of that, but I looked at some old code I had and you don't have to do that... David Cummins "Heather R. Moll" wrote: try dbvarname="@CustomerID" - Original Message - From: Avi Flax [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 22, 2000 2:15 PM Subject: Stored Procedure Not Working, Help? Sending this question in for a friend. Thanks for any replies! Do you do much with stored procedures in Cold Fusion? It's one thing I do little with, and I'm stuck. I keep getting an error from the SP saying it isn't getting @CustomerID, which it expects. Here's the code I'm using: CFSTOREDPROC PROCEDURE="InsertOrder" DATASOURCE="#DSN#" CFPROCPARAM TYPE="In" CFSQLTYPE="CF_SQL_INTEGER" VARIABLE="CustomerID" DBVARNAME="CustomerID" VALUE="#CustomerID#" NULL="No" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="NetAmount" VALUE="#NetAmount#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="Tax" VALUE="#Tax#" CFPROCPARAM TYPE="IN" CFSQLTYPE="CF_SQL_REAL" VARIABLE="TotalAmount" VALUE="#TotalAmount#" I just tried adding the DBVARNAME parameter but that didn't help. Help? :) -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
