Hello Mate, There are only 2 or 3 rows currently in the table, from where I've been running the stored procs from SSMS.
Maybe it would be worth you trying to replicate the issue, I dont want you to spend much time on it as I'd feel a little guilty, but you've got the schema, if you need the function code again I can post that for you. Thanks mate, Rob > How many rows are currently in the table? You are inserting into? > > I may fire up my pc, and run in your table schema and yet out for myself > now! > > > > > > > "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: Sun Jun 17 12:03:30 2007 > Subject: Re: Error converting data type varchar to int. > > Mate, > > I've just spotted a few blog posts that 3.3 is the out of date version > which was listed a bug with the 7.2 update, So I've just updated them > manualy, the log output now says this. > > [Macromedia][SQLServer JDBC Driver]Driver Version: 3.50.13 > (016217.009407.011311) > > So, I'm running 3.5 build of the drivers, but the issue still persists on > that page :-D I'm happy to have the new drivers, but its not helped on > this particular problem. > > Any other ideas? this is mental!!! > > Thanks, > > R > >> Thanks Niel, >> >> I just ran the code from that site to get my driver versions and it gave >> me the following output. >> >> [Macromedia][Oracle JDBC Driver]Driver Version: 3.3.48 >> (008852.005015.004259) >> [Macromedia][DB2 JDBC Driver]Driver Version: 3.3.48 >> (008852.005015.007851) >> [Macromedia][Informix JDBC Driver]Driver Version: 3.3.48 >> (008852.005015.002208) >> [DataDirect][SequeLink JDBC Driver]Driver Version: 5.4.0038 >> [Macromedia][SQLServer JDBC Driver]Driver Version: 3.3.48 >> (008852.005015.006337) >> [Macromedia][Sybase JDBC Driver]Driver Version: 3.3.48 >> (008852.005015.003526) >> >> I've just checked the Adobe KB article for the cumulative update and it >> looks as if it doesnt list anything specificaly about the drivers, so >> perhaps they need to be updated manualy? whats the best way to go about >> that? are my current versions in need up updating? >> >> Thanks mate, >> >> Rob >> >>> Use this to check your current version. >>> >>> http://www.sargeway.com/blog/rss.cfm?mode=full&mode2=cat&catid=12 >>> >>> Search for checking your driver version >>> >>> >>> >>> >>> "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: Sun Jun 17 10:44:08 2007 >>> Subject: Re: Error converting data type varchar to int. >>> >>> Thanks N, >>> >>> Ok, so it looks like that cumulative patch was already installed on the >>> box, I must have placed it on when I built the server and forgotten >>> about >>> it, I've reinstalled the patch and rebooted the box, So in the system >>> information page it now lists server version as: >>> >>> 7,0,2,142559 >>> >>> And patches applied as: >>> >>> /C:/path/to/my/coldusion/lib/updates/chf7020002.jar >>> >>> I've now tested the page and I'm still getting the same error, If you >>> want >>> to get a better look at it feel free, it might help out. >>> >>> http://80.244.184.136/ >>> >>> Thanks again for your help mate, >>> >>> Rob >>> >>>> OK, >>>> >>>> Head to Adobe and patch up with the latest culmulitive patch for >>>> 7.0.2. >>>> I >>>> think the latest drivers are in it, if not use this link >>>> >>>> >>> > http://kb.adobe.com/selfservice/viewContent.do?externalId=42dcb10a&sliceId=1 >>>> >>>> N >>>> >>>> >>>> >>>> >>>> >>>> >>>> "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: Sun Jun 17 10:08:28 2007 >>>> Subject: Re: Error converting data type varchar to int. >>>> >>>> Morning Niel, >>>> >>>> Thanks for that info, I'm running on MX7 Standard here. As for the >>>> encryption stuff, even with the encryption removed and just standard >>>> values hard coded into the cfproparam (see my last post) I still get >>>> the >>>> issue, so its not an encryption issue. >>>> >>>> As for my drivers, I'm using what ever comes as standard with MX7 and >>>> 2005 >>>> SQL Server. How can I find out which version I'm running and how can I >>>> update them? >>>> >>>> After looking at the error message thrown by this problem I'm going to >>>> guess its the JDBC drivers I'm using. >>>> >>>> [Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data >>>> type >>>> varchar to int. >>>> >>>> Thanks mate, >>>> >>>> Rob >>>> >>>>> If you haven't updated the drivers then I would suggest you give that >>>>> a >>>>> try >>>>> (try both the latest Adobe DataDirect 3.5 and Microsoft SQL 2005 JDBC >>>>> ones) >>>>> >>>>> You may also want to try a ToString() around the encrypted data as it >>>>> is >>>>> passed in (do they really need to be encrypted? And if so, can't you >>>>> shift >>>>> it server/DB side into SQL Server) >>>>> >>>>> Best thing to try here is remove all crypto stuff, use CFQuery to >>>>> insert >>>>> and >>>>> check if it's still an issue.. >>>>> >>>>> >>>>> >>>>> "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 >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>>> >>> >>> >>> >>> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281404 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
