Glad that helped! SQL is really picky about what you can call a "Date" but if you call it a "Char", SQL is usually pretty good at figuring it out.
Chris ---------------------------------------------- Original Message From: "Neil H."<[EMAIL PROTECTED]> Subject: Re: SP pulling my hair out!!! Date: Tue, 16 Apr 2002 15:40:51 -0400 >It was not addressed and you are the man! How in the world was I supposed >to know it has to be CHAR?! That seems so backwards. I guess the >CF_SQL_DATE is for another DB server?! > >Thanks, > >Neil > >----- Original Message ----- >From: "Chris Terrebonne " <[EMAIL PROTECTED]> >To: "CF-Talk" <[EMAIL PROTECTED]> >Sent: Tuesday, April 16, 2002 3:14 PM >Subject: Re: SP pulling my hair out!!! > > >> Not sure if this has been addressed yet but you should be able to send the >> date as a CHAR and have SQL determine if its a date: >> >> <cfprocparam type="In" cfsqltype="CF_SQL_CHAR" variable="DateValue" >> value="#DateFormat(Now(), "mm/dd/yyyy")#"> >> >> @DateValue varchar(10) >> >> As long as the date is formated properly, SQL will accept a char/varchar >as >> a datetime value. >> >> HTH, >> Chris >> >> ---------------------------------------------- >> Original Message >> From: "Neil H."<[EMAIL PROTECTED]> >> Subject: Re: SP pulling my hair out!!! >> Date: Tue, 16 Apr 2002 15:05:01 -0400 >> >> >Anyone have any code samples of CFSTOREDPROC and date usage. I just >can't >> >get it to work! >> > >> >Neil >> > >> >----- Original Message ----- >> >From: "Neil H." <[EMAIL PROTECTED]> >> >To: "CF-Talk" <[EMAIL PROTECTED]> >> >Sent: Tuesday, April 16, 2002 2:19 PM >> >Subject: Re: SP pulling my hair out!!! >> > >> > >> >> I receive this error when I readded it: >> >> >> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: text >> is >> >> incompatible with int >> >> >> >> Neil >> >> >> >> ----- Original Message ----- >> >> From: "Mark A. Kruger - CFG" <[EMAIL PROTECTED]> >> >> To: "CF-Talk" <[EMAIL PROTECTED]> >> >> Sent: Tuesday, April 16, 2002 11:07 AM >> >> Subject: RE: SP pulling my hair out!!! >> >> >> >> >> >> > Excellent. I'm glad it's working for you. Yes it is strange. From >the >> >> > command line (query analyzer) you can put '5/26/2002' and SQL will >> >> > automatically parse it. But the ODBC driver cannot differentiate >that >> >> > syntax from string syntax - it doesn't automatically parse it as a >date >> >> just >> >> > because you've identified it as a date. I've always thought that was >a >> >> bit >> >> > of an oversite. >> >> > >> >> > mark >> >> > >> >> > -----Original Message----- >> >> > From: Neil H. [mailto:[EMAIL PROTECTED]] >> >> > Sent: Tuesday, April 16, 2002 9:54 AM >> >> > To: CF-Talk >> >> > Subject: Re: SP pulling my hair out!!! >> >> > >> >> > >> >> > I have found this to be the problem. So a data formatted 05/26/2002 >> >will >> >> > not work I have to use CreateODBCDATE to get that to work? That >seems >> >> dumb >> >> > :) >> >> > >> >> > Neil >> >> > >> >> > ----- Original Message ----- >> >> > From: "Mark A. Kruger - CFG" <[EMAIL PROTECTED]> >> >> > To: "CF-Talk" <[EMAIL PROTECTED]> >> >> > Sent: Monday, April 15, 2002 11:04 PM >> >> > Subject: RE: SP pulling my hair out!!! >> >> > >> >> > >> >> > > Neil, >> >> > > >> >> > > I usually use #createodbcdatetime(var)# or #createodbcdate(var)# to >> >> > > correctly format the date string. >> >> > > >> >> > > Mark >> >> > > >> >> > > -----Original Message----- >> >> > > From: Neil H. [mailto:[EMAIL PROTECTED]] >> >> > > Sent: Monday, April 15, 2002 9:50 PM >> >> > > To: CF-Talk >> >> > > Subject: Re: SP pulling my hair out!!! >> >> > > >> >> > > >> >> > > <cfprocparam type="In" cfsqltype="CF_SQL_DATE" >> >> variable="3" >> >> > > value="#3#"> >> >> > > @3 datetime, >> >> > > >> >> > > >> >> > > This is the culprit. What is the normal way to handle this?! >> >> > > >> >> > > Neil >> >> > > >> >> > > >> >> > > >> >> > > ---- Original Message ----- >> >> > > From: "Neil H." <[EMAIL PROTECTED]> >> >> > > To: "CF-Talk" <[EMAIL PROTECTED]> >> >> > > Sent: Monday, April 15, 2002 10:33 PM >> >> > > Subject: Re: SP pulling my hair out!!! >> >> > > >> >> > > >> >> > > > <cfstoredproc datasource="#DSN#" procedure="sp_MYSP"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" >> >variable="1" >> >> > > > value="#1#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_MONEY" >> >variable="2" >> >> > > > value="#2#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_DATE" >> >> > variable="3" >> >> > > > value="#3#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" >> >variable="4" >> >> > > > value="#4#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_LONGVARCHAR" >> >variable="5" >> >> > > > value="#5#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_LONGVARCHAR" >> >variable="6" >> >> > > > value="#6#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_LONGVARCHAR" >> >variable="7" >> >> > > > value="#7#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" >variable="8" >> >> > > > value="#8#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" >variable="9" >> >> > > > value="#9#"> >> >> > > > <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" >variable="10" >> >> > > > value="#10#"> >> >> > > > <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" >variable="11" >> >> > > > dbvarname="@11"> >> >> > > > </cfstoredproc> >> >> > > > >> >> > > > >> >> > > > IF EXISTS (SELECT name FROM sysobjects >> >> > > > >> >> > > > WHERE name = 'sp_MYSP' AND type = 'P') >> >> > > > >> >> > > > DROP PROCEDURE sp_MYSP >> >> > > > >> >> > > > GO >> >> > > > >> >> > > > CREATE PROCEDURE sp_MYSP >> >> > > > @1 int, >> >> > > > @2 money, >> >> > > > @3 datetime, >> >> > > > @4 int, >> >> > > > @5 varchar(100), >> >> > > > @6 varchar(30), >> >> > > > @7 varchar(30), >> >> > > > @8 int, >> >> > > > @9 int, >> >> > > > @10 int, >> >> > > > @11 int OUT >> >> > > > >> >> > > > >> >> > > > Names were changed to protect the innocent >> >> > > > >> >> > > > Neil >> >> > > > >> >> > > > ----- Original Message ----- >> >> > > > From: "Mark A. Kruger - CFG" <[EMAIL PROTECTED]> >> >> > > > To: "CF-Talk" <[EMAIL PROTECTED]> >> >> > > > Sent: Monday, April 15, 2002 10:02 PM >> >> > > > Subject: RE: SP pulling my hair out!!! >> >> > > > >> >> > > > >> >> > > > > Neil, >> >> > > > > >> >> > > > > better post your code - hard to deal with this error without >> >looking >> >> > at >> >> > > > it. >> >> > > > > >> >> > > > > Mark >> >> > > > > >> >> > > > > -----Original Message----- >> >> > > > > From: Neil H. [mailto:[EMAIL PROTECTED]] >> >> > > > > Sent: Monday, April 15, 2002 8:46 PM >> >> > > > > To: CF-Talk >> >> > > > > Subject: SP pulling my hair out!!! >> >> > > > > >> >> > > > > >> >> > > > > I only have a little hair left so PLEASE throw ideas at me! >> >> > > > > >> >> > > > > I get this error: >> >> > > > > >> >> > > > > Microsoft][ODBC SQL Server Driver]COUNT field incorrect or >syntax >> >> > error >> >> > > > > Hint: The cause of this error is usually that your query >contains >> >a >> >> > > > > reference to a field which does not exist. You should verify >that >> >> the >> >> > > > fields >> >> > > > > included in your query exist and that you have specified their >> >names >> >> > > > > correctly. >> >> > > > > >> >> > > > > >> >> > > > > >> >> > > > > I have tested the SP and it works properly. The stored >procedure >> >> does >> >> > > > exist >> >> > > > > and I copied and pasted every single variable name from the >> stored >> >> > > > > procedure. There is no way the field doesn't exist and I Have >> >> counted >> >> > > > that >> >> > > > > the number of variables is identical. What could I be >missing!!!? >> >> > > > > >> >> > > > > >> >> > > > > >> >> > > > > Neil >> >> > > > > >> >> > > > > >> >> > > > > >> >> > > > > >> >> > > > >> >> > > >> >> > > >> >> > >> >> > >> >> >> > >> > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
