Advertisement

01.31.2008 at 10:13AM PST, ID: 23127214 | Points: 500
[x]
Attachment Details

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

Asked by pigsooie in Cold Fusion Markup Language, MS SQL Server, Miscellaneous Web Development

Tags:

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?


Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
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
 
Loading Advertisement...
 
[+][-]01.31.2008 at 10:43AM PST, ID: 20789598

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 10:43AM PST, ID: 20789611

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 12:55PM PST, ID: 20791012

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 01:06PM PST, ID: 20791133

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 01:13PM PST, ID: 20791201

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 01:14PM PST, ID: 20791226

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 02:10PM PST, ID: 20791803

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 14-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 04:02PM PST, ID: 20792852

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.31.2008 at 04:31PM PST, ID: 20793037

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 14-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20081112-EE-VQP-43 / EE_QW_2_20070628