Add DBNull to date field in database?

RandyB30

I have a date field in my database (StartDate). I have created a datatable which will access this field. The DataType in the DataTable is defined as System.DateTime. Allow DBNull is set to True and th DefaultValue is <DBNull>. I have an SQL statement to add (INSERT) a new record. I have created a parameter to be used with the SQL statement. In the parameter options I have set AllowDBNull to true, the DbType is set to Date and the ProviderType is DBDate. Everything works great as long as the date textbox has a date in it. The problem is there is not always a date provided to a DBNull value should added. When I try to run the SQL statement with no date provided I get the following error message:

Conversion from string "" to type 'Date' is not valid.

I have tried many things but cant seem to get rid of the error.

Thanks for any help,

Randy

js06

I have had some issues with this also.

It took this happening to me twice and then i got rid of the datetime types.

I set all my fields to either varchar or nvarchar and then i handle the formatting myself in my entry forms.

by setting to variable characters (varchar) then you can have anything you want in the field and that includes "" (null)

I guess the issue on the datetime type is that an empty string can never be a date but an empty string is still a string in a string field.

For my dates i use a datetimepicker to enter the date and format it how i want it. This takes out the guess work for the user. And for times i use a combobox that is populated with times for them to choose from. I even place the datetimepicker under the textbox for the datetime field and then hide it. I have a button next to the box that when clicked will show the picker and when they select a date it will hide again.

All these preset formats get difficult to handle sometimes. I haven't played with it because i don't use a datagridview for entering data, i use only textboxes, but i would imagine you could handle the keypress events for the datagridview also when editing by determining what column index you are in and then control the keypresses.

I like to stay away from a lot of the preset "stuff"

By the way, did you see the code i gave you in the other thread about bindingsource with option strict on

The code will give you what you need and still be option strict safe.

RandyB30

Hi JS. We meet again. Sounds like I should just get your Windows Messenger screen name I would like to be able to use DateTimePicker but when a project is entered it does not always have start and/or end dates so nothing is entered which results in a "" string which cant be converted to a date. I have tried everything I could think of. I did find that sending "Nothing" instead of "" or DBNull does work and I dont get the error.

I did get your solution for my question in the other thread but I haven't had the chance to try it yet.

This is the first time I have tried any kind of data access in .NET, which is of course the reason for all the recent questions. Usually I work on applications that require some sort of data acquisition via data acquisistion boards.

Thank for the help,

Randy

js06

I actually meant to say monthcalendar, not datetimepicker.

the datetimepicker will always show a value in it. I'm not sure if you can prevent it or not.

but the monthcalendar is not automatically shown in a textbox so you can add the selected date to a textbox if one is chosen.

After a while all the names start to sound the same on some things.

Maybe i am just tired here lately

And about messenger, i don't do any chat. I strictly stay in the forum.