?
Solved

How do I insert/update an empty date field with a stored procedure

Posted on 2008-01-31
10
Medium Priority
?
642 Views
Last Modified: 2010-04-21
I have a form with 3 optional date fields (smalldatetime).  Whenever one of the date fields is left empty and the stored procedure is executed I get the following error: "Error converting data type nvarchar to smalldatetime."  If a date is input into the date field II would like that date to be saved or if the date field is left empty then the stored procedure would enter a NULL value.
/****** 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
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20792538
you will have to pass NULL instead of ''

0
 

Author Comment

by:pigsooie
ID: 20792695
How do I do that?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20793656
>>How do I do that?<<
Unfortunately, while many of us are fairly competent, none of us are clairvoyant.  So unless we know/see how you are calling the stored procedure we cannot even remotely guess.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:pigsooie
ID: 20793673
I'm using ColdFusion
<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_char">
<cfprocparam variable="@phone2" value="#phone2#" cfsqltype="cf_sql_char">
<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_char">
<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_tinyint">
<cfprocparam variable="@lastcontactdate" value="#lastcontactdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@initials" value="#initials#" cfsqltype="cf_sql_char">
<cfprocparam variable="@status" value="#status#" cfsqltype="cf_sql_tinyint">
<cfprocparam variable="@followupdate" value="#followupdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@archive" value="#archive#" cfsqltype="cf_sql_tinyint">
<cfprocparam variable="@comments" value="#comments#" cfsqltype="cf_sql_varchar">
 
<cfprocresult name="clientupdate">
</cfstoredproc>

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20793696
I am afraid I have no idea how you pass a Null parameter using ColdFusion.  You may have better luck posting in that Zone.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20794618
please try to add the following for the lines in question:
null="#(len(initials) is 0)#"

like this:

<cfprocparam variable="@initials" value="#initials#" cfsqltype="cf_sql_char" null="#(len(initials) is 0)#" >
0
 
LVL 4

Expert Comment

by:Maxi84
ID: 20795281
Null dates are a PIA, and quite a few applications don't even support null in date fields.  It's better to use some default dummy date, such as 01.01.1970 or 01.01.1900, instead of null values in date fields.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20796496
>>Null dates are a PIA, and quite a few applications don't even support null in date fields.<<
I beg to differ with you on all counts.  First it is not the application that supports or does not support Nulls, but the back end database.  In this specific case we are talking about MS SQL Server which doess support it as most good RDMS do.  Why in the world would you resort to using a "dummy" value when there is a perfectly valid values to use?

We should be encouraging the questioner to use good relational concepts, rather than finding artificial workarounds.
0
 

Author Closing Comment

by:pigsooie
ID: 31426963
Thank you angellll, that's exactly what I was looking for.
0
 
LVL 4

Expert Comment

by:Maxi84
ID: 20804344
Avoiding null values in general in the database is a good practice IMO.  Sure, there are valid uses for null, but not many, and they increase the complexity of your queries unnecessarily.  You can't use regular comparison operator on nulls, but have to use the IS operator.  Not all host languages support nulls, so you may have to make sure that you don't return null in your queries.  I could go on, but better to quote Joe Celko's 'SQL for Smarties, 2nd ed.': :"Think of NULLs as a drug; use it properly and it works for you, but abuse it and it can ruin everything.  Your best policy is to avoid them when you can and use them properly when you have to"
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

588 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