[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-01-31
10
Medium Priority
?
624 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:pigsooie
  • 5
  • 3
9 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1344 total points
ID: 20789598
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1344 total points
ID: 20789611
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
 

Author Comment

by:pigsooie
ID: 20791012
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20791133
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
 

Author Comment

by:pigsooie
ID: 20791201
How does that effect the data that is inserted, would it still be smalldate?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20791226
Yes because of the explicit cast

cast(@lastcontactdate as smalldatetime)

0
 

Author Comment

by:pigsooie
ID: 20791803
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
 
LVL 3

Assisted Solution

by:Kerrdo
Kerrdo earned 672 total points
ID: 20792852
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 20793037
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Suggested Courses

613 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question