Perhaps one of you sagacious types can help me with this. I'm having problems with the value of '0' being passed to a database.
I have a form that tracks users' personal info - among other things, their weight and age.
I'm using CF 5 with MS SQL. The input fields for weight and age look like this:
The patient_age and patient_weight variables get inserted via a stored procedure to the table MY_USERS, which looks like:
CREATE TABLE [dbo].[MY_USERS] (
[USER_ID] [int] NOT NULL ,
[USER_AGE] [int] NULL ,
[USER_WEIGHT] [int] NULL
) ON [PRIMARY]
GO
The stored procedure that is used to do the insert is:
CREATE proc spAddCase
@USER_ID int = 0,
@USER_AGE int =0,
@USER_WEIGHT int = 0
as
set nocount on
Declare @TimeCreated datetime
SELECT @TimeCreated = GetDate()
insert into MY_USERS
(
USER_ID,
USER_AGE,
USER_WEIGHT,
DTC)
values
(
@USER_ID,
@USER_AGE,
@USER_WEIGHT,
@TimeCreated)
GO
[/code]
Basically, the age and weight fields are nullable INT fields because they're obviously both supposed to be numbers, but they're both optional fields.
But when I try to pass a blank value (that is, ""
to MY_USERS.USER_AGE or MY_USERS.USER_WEIGHT, it says:
Here's what the stored procedure looks like:
I have tried using cfparam and cfif statements to set default values of 0, '0', (0), and "0," but the INT field just isn't having any of that. I have stumped everyone in my office. Does anyone have any other ideas, short of changing the datatype in MY_USERS to VARCHAR?
I have a form that tracks users' personal info - among other things, their weight and age.
I'm using CF 5 with MS SQL. The input fields for weight and age look like this:
Code:
User Age: <input type=text size=3 name="user_age"> yrs.<br>
User weight: <input type=text size=3 name="user_weight"> lbs.<br>The patient_age and patient_weight variables get inserted via a stored procedure to the table MY_USERS, which looks like:
CREATE TABLE [dbo].[MY_USERS] (
[USER_ID] [int] NOT NULL ,
[USER_AGE] [int] NULL ,
[USER_WEIGHT] [int] NULL
) ON [PRIMARY]
GO
The stored procedure that is used to do the insert is:
CREATE proc spAddCase
@USER_ID int = 0,
@USER_AGE int =0,
@USER_WEIGHT int = 0
as
set nocount on
Declare @TimeCreated datetime
SELECT @TimeCreated = GetDate()
insert into MY_USERS
(
USER_ID,
USER_AGE,
USER_WEIGHT,
DTC)
values
(
@USER_ID,
@USER_AGE,
@USER_WEIGHT,
@TimeCreated)
GO
[/code]
Basically, the age and weight fields are nullable INT fields because they're obviously both supposed to be numbers, but they're both optional fields.
But when I try to pass a blank value (that is, ""
to MY_USERS.USER_AGE or MY_USERS.USER_WEIGHT, it says:
Code:
Invalid character value for cast specificationHere's what the stored procedure looks like:
Code:
<cfstoredproc procedure="spAddUser" datasource="#attributes.dsn#" username="xxxx" >
<cfprocresult name = RS5>
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" maxlength="1" value=#USER_ID#>
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" maxlength="1" value=#USER_AGE#>
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" maxlength="1" value=#USER_WEIGHT#>
</cfstoredproc>I have tried using cfparam and cfif statements to set default values of 0, '0', (0), and "0," but the INT field just isn't having any of that. I have stumped everyone in my office. Does anyone have any other ideas, short of changing the datatype in MY_USERS to VARCHAR?