Question .MDB Date Issues

[clockwork3orange]

I am using VB.NET 2008 Express edition with a connection to MS Access 2007 saved as an .mdb not .accdb. If I set the database to create the primary key and all the fields are text there is no issue. However if I set several fields as a simple date mm/dd/yyyy i run into problems. When I output the results of a date in a text field it prints like this 7/14/2009 12:00:00 AM. If I try to add a new record I cannot unless all of the fields set as dates in the database are typed in this format 7/14/2009 12:00:00 AM. I need a few fields as dates for sorting reasons. I also have it set so when I add records to the database they are in single quotes. I removed the quotes but it still complains.

 

[Tom]

DateTime fields are a large part of all databases. As far as displaying in a textbox, its only a matter of formatting the date. This can be done on the front end in the vb app or in your query itself. Inserting dates can be enclosed with # signs instead of quotes.

 

[clockwork3orange]

Hey thanks for the info. The #date# solved one of my problems. I still have the issue that the database is treating my date feilds as required even though they are not. If I turn all feilds to text I can insert empty rows. If I make one ore more a date it will not let me insert a row unless I have a date in each one. Any ideas?

 

[JuggaloBrotha]

You really should be using parameterized queries, also it might help if you look into using an xsd file for this as it'll greatly simplify things (it also generates parameterized queries too)

Here's a recent thread about xsd files that my post contains some info about them: http://www.vbdotnetforums.com/ado-net/36625-work-xsd.html

 

[clockwork3orange]

I figured it out. If you set the DB fields to dates you need to insert a NULL if you want to leave them blank. So what I did was put a few if statements before the db insert to test for an empty text box. If it is empty if made the text box text = to NULL.