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
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281377
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
