Which version of ColdFusion? And which Drivers? I am assuming vanilla DataDirect but you never know, you could be running Microsoft ones or updated to, I think, DD 3.5.
"This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: [EMAIL PROTECTED] To: CF-Talk Sent: Sat Jun 16 21:32:59 2007 Subject: Re: Error converting data type varchar to int. Hello Neil, I'm running SQL Server 2005, and this is a create table script for you. CREATE TABLE Credential ( Credential_ID INTEGER NOT NULL IDENTITY(1,1) , User_ID INTEGER NOT NULL , Username VARCHAR(200) , Password VARCHAR(200) ) The stored proceedure is a simple insert script, which is somthing like this: CREATE PROC Credential_Create @User_ID INTEGER, @Password VARCHAR(200), @Username VARCHAR(200) AS INSERT INTO Credential (User_ID, Username, Password) VALUES (@User_ID, @Username, @Password) Thanks for any further insight mate, Rob > What version of SQL Server is it again? What are the underlying datatypes > specifically? And how many records? > > > > "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, > Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, > Registered in England, Number 678540. It contains information which is > confidential and may also be privileged. It is for the exclusive use of > the > intended recipient(s). If you are not the intended recipient(s) please > note > that any form of distribution, copying or use of this communication or the > information in it is strictly prohibited and may be unlawful. If you have > received this communication in error please return it to the sender or > call > our switchboard on +44 (0) 20 89107910. The opinions expressed within > this > communication are not necessarily those expressed by Reed Exhibitions." > Visit our website at http://www.reedexpo.com > > -----Original Message----- > From: [EMAIL PROTECTED] > To: CF-Talk > Sent: Sat Jun 16 20:06:54 2007 > Subject: Re: Error converting data type varchar to int. > > Hi Neil, > > Thanks for the tip on the name of sp's I didnt realize that was the case, > I'll rename them all from now on in, just to keep to the best practice. > > Now, as far as the type setting is concerned, how can I convert/cast the > input types on the server side? I dont think that somthing I've done > before, are you able to give me a code example or somthing? > > However, I'm doubtfull that is causing the issue, as if I remove > encryption off those fields so i'm just passing in the plain string of > 'testusername' and 'testpassword' and it still throws the error. > > I've cut down the stored proc so I have the values hard coded in to it, > and this still throws an issue. > > <!--- Query To Create Record ---> > <cfstoredproc procedure="Sp_Profile_Create" > datasource="#VARIABLES.Instance.Datasource.getDatasource()#" > username="#VARIABLES.Instance.Datasource.getUsername()#" > password="#VARIABLES.Instance.Datasource.getPassword()#"> > <cfprocparam value="1" variable="User_ID" cfsqltype="cf_sql_integer" > /> > <cfprocparam value="testusername" variable="MemberUsername" > cfsqltype="cf_sql_varchar" /> > <cfprocparam value="testpassword" variable="MemberPassword" > cfsqltype="cf_sql_varchar" /> > </cfstoredproc> > > If you spot anything that seems out of place that would be great. Like i > say, if i put those exact same values into a EXEC statement in SSMS then > it inserts the records absolutly fine. > > Thanks guys, this is all confusing, I really appreciate the help. > > Rob > >> This error can sometimes be raised when you are passing in a value to a >> column of a certain type but the column contains most if not all values >> of >> another - I.e. VARCHAR column but all the values in it are technically >> ints. >> Have your tried to convert/cast the input values server side as well? >> >> Also, it is advisable to not create a user SP with a prefix of sp_, >> these >> should be reserved for system procs as per info in BOL. >> >> >> >> >> >> >> "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, >> Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, >> Registered in England, Number 678540. It contains information which is >> confidential and may also be privileged. It is for the exclusive use of >> the >> intended recipient(s). If you are not the intended recipient(s) please >> note >> that any form of distribution, copying or use of this communication or >> the >> information in it is strictly prohibited and may be unlawful. If you >> have >> received this communication in error please return it to the sender or >> call >> our switchboard on +44 (0) 20 89107910. The opinions expressed within >> this >> communication are not necessarily those expressed by Reed Exhibitions." >> Visit our website at http://www.reedexpo.com >> >> -----Original Message----- >> From: Robert Rawlins - Think Blue >> To: CF-Talk >> Sent: Fri Jun 15 10:56:00 2007 >> Subject: RE: Error converting data type varchar to int. >> >> Right you are Jochem! >> >> Here is the stored proc code: >> >> CREATE PROCEDURE Sp_Credential_Create >> @User_ID INTEGER, >> @Username VARCHAR(128), >> @Password VARCHAR(128) >> AS >> SET NOCOUNT ON >> INSERT INTO Credential (User_ID, Username, Password) >> VALUES (@User_ID, @Username, @Password) >> >> And here is the function that fires the stored proc. >> >> <!--- Create Record ---> >> <cffunction name="create" access="public" output="false" >> returntype="void" >> hint="I create a record in persistance"> >> <cfargument name="UserID" required="true" type="numeric" hint="I'm >> the user id for the profile to be inserted" /> >> <cfargument name="Username" required="true" type="string" >> hint="Username" /> >> <cfargument name="Password" required="true" type="string" >> hint="Password" /> >> >> <!--- Create Temporary Local Structure ---> >> <cfset var LOCAL = structNew() /> >> >> <!--- Query To Create Record ---> >> <cfstoredproc procedure="Sp_Profile_Create" >> datasource="#VARIABLES.Instance.Datasource.getDatasource()#" >> username="#VARIABLES.Instance.Datasource.getUsername()#" >> password="#VARIABLES.Instance.Datasource.getPassword()#"> >> <cfprocparam value="#ARGUMENTS.UserID#" variable="User_ID" >> cfsqltype="cf_sql_integer" maxlength="128" /> >> <cfprocparam >> > value="#VARIABLES.Instance.Cryptographer.getEncrypted(ARGUMENTS.Username)#" >> variable="Username" cfsqltype="cf_sql_varchar" maxlength="128" /> >> >> <cfprocparam >> > value="#VARIABLES.Instance.Cryptographer.getEncrypted(ARGUMENTS.Password)#" >> variable="Password" cfsqltype="cf_sql_varchar" maxlength="128" /> >> </cfstoredproc> >> >> <cfreturn /> >> </cffunction> >> >> And finally the code that triggers the function. >> >> <cfset VARIABLES.DAO.create(1, 'TestUser, 'TestPassword') /> >> >> Hope that all makes sense to you, it would be great to figure out what's >> causing this. >> >> Thanks, >> >> Rob >> >> -----Original Message----- >> From: Jochem van Dieten [mailto:[EMAIL PROTECTED] >> Sent: 14 June 2007 18:21 >> To: CF-Talk >> Subject: Re: Error converting data type varchar to int. >> >> Robert Rawlins - Think Blue wrote: >>> >>> [Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data >>> type >>> varchar to int. >>> >>> This is a snippet of the query details thrown back in the error, it >>> simple >>> takes 3 parameters, the first is an integer and the second 2 are >>> strings/varchar. >>> >>> (param 1) = [type='IN', class='java.lang.Integer', value='1', >>> sqltype='cf_sql_integer'] , (param 2) = [type='IN', >>> class='java.lang.String', value='07C67BA421500B791090E92F4C3D7032', >>> sqltype='cf_sql_varchar'] , (param 3) = [type='IN', >>> class='java.lang.String', value='D5534BDE55CAB29E3932D6CE4F211C34', >>> sqltype='cf_sql_varchar'] >>> >>> The param java types appear to match the cf_sql_type. These types also >> match >>> those defined inside the stored proc and the table columns, I can't see >>> what's causing this issue. >> >> If you don't include the stored procedure and CFML code neither can we >> :) >> >> Jochem >> >> >> >> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281391 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
