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 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281385 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
