ASP.NET: Inserting Null DateTime values in SQL Server

A problem I run into from time to time is inserting Null values into a SQL Server DateTime field. We typically use Stored Procedures for everything and catch all statements to handle errors during insert. Usually, when you don't set a default on a control its value is Null. Likewise, if you explicitly set a control to Nothing (vb.net) It sends a Null. However, DateTime fields like to send the time with no date, which creates a non-null and invalid date for SQL server leading to an overflow exception.

After a lot of frustration with setting values to nothing, DbNull, etc... I found a solution. I check to make sure it is a valid SQL date (not VB.net) by testing if it is greater than 01/01/1900 and if not, setting it to System.SqlTypes.SqlDateTime.Null. You could place this in a BLL, or directly before you pass the date in.

If Not EventDate > "01/01/1900" Then
EventDate = System.Data.SqlTypes.SqlDateTime.Null
End If

I hope this helps someone as it has become standard practice for me now.

Comments

Post a Comment

Popular posts from this blog

Apple TV - Recover from nothing Take 2

Ok, so I have updated this for Take 2 and Leopard. This is more of a rought draft, but it worked well for me. Please comment if I have missed any steps since I am writing this after the fact. One big change from the original article us you can let Disk Utility do some of the work for you now. Also, I just skipped the restore partition to keep it simple since that was the source of many problems. Connect your Apple TV drive in Leopard. Remember disk1 may not be the name of your drive. From the terminal: Wipe out and re-create the partition structure sudo diskutil unmountDisk /dev/disk1 sudo gpt destroy /dev/disk1 sudo gpt create /dev/disk1 Create the OSBoot Partition sudo gpt add -b 888872 -i 3 -s 1843200 -t hfs /dev/disk1 diskutil unmountDisk /dev/disk1 sudo diskutil eraseVolume "Journaled HFS+" OSBoot /dev/disk1s3 diskutil unmountDisk /dev/disk1 Create the Media Partition Go to Disk Utility and format the Media partition - Name i
Read more

Convert a LiveCycle Form back to an Acrobat Form

Image
PDF Forms that were saved with Adobe LiveCycle cannot be programatically filled and flattened using most API's, like ActivePDF. However, you can convert your LiveCycle PDF form back into a static PDF. 1. Open the form in LiveCycle and save it as Adobe Static PDF form. (Example using Acrobat X Pro) 2. Download PDFTK  http://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/ 3. Remove extra XML data with the following command line. I copied the creditApplication.pdf file to the same folder. "pdftk creditApplication.pdf output test.pdf drop_xfa" 4. Now you can open the form in Acrobat and rename the forms fields. By default, the conversion might append form[0] stuff to the field names, so I rename them. 5. Save the new PDF using "Save As". Choose "Adobe PDF Files, Optimized (*.pdf)" and in the optimizer window, make it compatible with Acrobat 4.0 and later. 6. You now have a form fillable PDF that you can use programatically and will flatten properly. [gallery
Read more

Converting a MySQL database to SQLite on a Mac

Image
I recently decided to migrate an e-commerce shopping cart system I have been developing for a long time from MySQL to SQLite. I went back and forth over this idea, but I decided it made sense to have an e-commerce shopping cart that the user can install by literally dropping the folder into the site. In order to make the transition as easy as possible, I decided to try to convert the database rather than re-create the many tables and fields needed to make it all work. During my research I found some paid products and a bunch of scripts scattered around, many in Perl. I was looking for something simple that I could try out with minimal effort. I found a script posted at http://www.jbip.net/content/how-convert-mysql-sqlite that worked like a charm for me. I use a Macbook Pro running Snow Leopard. I develop using a local MySQL database and Apache using  MAMP . In order to get started, you need to do a MySQL dump of your database (in t
Read more