How do I prevent errors with empty string date fields passed with CFSTOREDPROC to Sql Stored Procedure?

I have a front end coldfusion page calling a sql stored procedure and passing parameters including 2 date fields.  Whenever the date fields are empty I receive an error "Error converting data type nvarchar to smalldatetime"  What do I need to handle this empty string?



Front-end webpage:
 
<cfif parameterexists(updated)>
<cfstoredproc procedure="client_update" datasource="#dsn#">
<cfprocparam variable="@clientid" value="#id#" cfsqltype="cf_sql_smallint">
<cfprocparam variable="@firstname" value="#firstname#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@lastname" value="#lastname#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@phone" value="#phone#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@phone2" value="#phone2#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@email" value="#email#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@type" value="#type#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@listdate" value="#listdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@tailnumber" value="#tailnumber#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@listprice" value="#listprice#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@location" value="#location#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@website" value="#website#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@contacted" value="#contacted#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@lastcontactdate" value="#lastcontactdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@initials" value="#initials#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@status" value="#status#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@followupdate" value="#followupdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@archive" value="#archive#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@comments" value="#comments#" cfsqltype="cf_sql_varchar">
 
<cfprocresult name="clientupdate">
</cfstoredproc>
 
Stored Procedure:
****** Object:  StoredProcedure [dbo].[client_update]    Script Date: 01/04/2008 21:36:45 ******/
@clientid smallint,
@firstname varchar (50),
@lastname varchar (50),
@phone varchar (25),
@phone2 varchar (25),
@email varchar (50),
@type varchar (50),
@listdate smalldatetime,
@tailnumber varchar (10),
@listprice varchar (12),
@location varchar (50),
@website varchar (250),
@contacted varchar (10),
@lastcontactdate smalldatetime,
@initials varchar (2),
@status varchar (10),
@followupdate smalldatetime,
@archive varchar (3),
@comments varchar (1000)
 
AS
 
UPDATE client
SET
 
firstname=@firstname,
lastname=@lastname,
phone=@phone,
phone2=@phone2,
email=@email,
type=@type,
listdate=@listdate,
tailnumber=@tailnumber,
listprice=@listprice,
location=@location,
website=@website,
contacted=@contacted,
lastcontactdate=@lastcontactdate,
initials=@initials,
status=@status,
followupdate=@followupdate,
archive=@archive,
comments=@comments
 
WHERE clientid=@clientid
 
GO

Open in new window

pigsooieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
You can define your datatypes for @lastcontactdate  and CASE your insert.

UPDATE client
SET
 
firstname=@firstname,
lastname=@lastname,
phone=@phone,
phone2=@phone2,
email=@email,
type=@type,
listdate=@listdate,
tailnumber=@tailnumber,
listprice=@listprice,
location=@location,
website=@website,
contacted=@contacted,
lastcontactdate=cast when isdate(@lastcontactdate)=1 then cast(@lastcontactdate as smalldatetime) else null end,
initials=@initials,
status=@status,
followupdate=@followupdate,
archive=@archive,
comments=@comments
 
WHERE clientid=@clientid

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
Sorry...

You can define your datatypes for @lastcontactdate  and CASE your insert.

Should read...

You can define your datatypes for @lastcontactdate  as varchar(32) and CASE your insert as above
0
pigsooieAuthor Commented:
I don't think I need to redefine the data type, I want it to either not process that fields insert or enter it as null, whichever is best
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BrandonGalderisiCommented:
I am just suggesting you redefine the input variable.

Because you are obviously not getting a NULL string to the stored procedure with the following error:

 "Error converting data type nvarchar to smalldatetime
0
pigsooieAuthor Commented:
How does that effect the data that is inserted, would it still be smalldate?
0
BrandonGalderisiCommented:
Yes because of the explicit cast

cast(@lastcontactdate as smalldatetime)

0
pigsooieAuthor Commented:
This is where I'm confused... if my table column is set as smalldatetime why should I modify an input variable with a different datatype?  That seems like a work-around, not a solution.  What I need the stored procedure to do is check for an empty string and if so, insert NULL or else insert the @date value.  Is this what your solution does, because I cannot get it to work.  Not trying to be difficult I just don't understand your solution
0
KerrdoCommented:
Just param the date value.

<cfset oneweek = createODBCDate(DateAdd('d', 7, NOW()))>
<cfparam name="followupdate" default="#oneweek#" type="date">

That will give the follow up date one week from the time of the insert.
0
BrandonGalderisiCommented:
What I need the stored procedure to do is check for an empty string and if so, insert NULL or else insert the @date value.  Is this what your solution does, because I cannot get it to work

No because an empty string is not a valid date nor is it NULL.  If it were NULL, it would work.  So you have to send it in as a string and check if it's a date on insert.  At least from SQL.  I don't know if you can get a true NULL in from cold fusion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.