Adding a Date to a database

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
How do I add a Date to my Access database,

The field in my access database is of type date, but I don't know how to add a date using VB.net and oledb,

Any ideas?
 
Help Arrived

Treat the date as a normal string, except that you have to make sure the date conforms to the particular date-format of the field, eg short date 01/12/2004.
Furthermore, the you have to place the date between hashes, like this "#01/12/2004#'.

Maybe if you give a more detailed description of how you want to use the date, I could be of more assistance, since there are various date functions. (And of course different implementations for different situations).
 
My Code

This is my code, I have a simple application where I can store events of appoinments that i need to remember. I'm trying to make a planner or diary but it's not going to be very complexe.

All it does is store a title, date and details about the appointment. The problem is when I need to add the date to the database.

It is an Access database, the date field is a type called short date.

VB.NET:
[size=2][/size][size=2][color=#0000ff]Private[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub[/color][/size][size=2] ButtonAddEvent_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] ButtonAddEvent.Click[/size]
[size=2][color=#008000]'check that all fields are filled, easier than doing it on the database[/color][/size]
[size=2][color=#0000ff]If[/color][/size][size=2] TextBoxTitle.Text = "" [/size][size=2][color=#0000ff]And[/color][/size][size=2] [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxDetails.Text = "" [/size][size=2][color=#0000ff]And[/color][/size][size=2] [/size][size=2][color=#0000ff]Me[/color][/size][size=2].TextBoxDate.Text = "" [/size][size=2][color=#0000ff]Then[/color][/size]
[size=2]StatusBar1.Text = "You must fill all fields"[/size]
[size=2][color=#0000ff]Else[/color][/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] sql [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size]
[size=2]sql = "INSERT INTO events(Details, Title, [Date])VALUES(@Details, @Title, @Date)"[/size]
[size=2][color=#0000ff]Dim[/color][/size][size=2] SQLreturn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]Integer[/color][/size]
[size=2]OleDbCommand1.CommandText = sql[/size]
[size=2][color=#0000ff]Try[/color][/size]
[size=2]OleDbConnection1.Open()[/size]
[size=2]OleDbCommand1.Parameters.Add("@Details", TextBoxDetails.Text)[/size]
[size=2]OleDbCommand1.Parameters.Add("@Date", TextBoxDate.Text)[/size]
[size=2]OleDbCommand1.Parameters.Add("@Title", TextBoxTitle.Text)[/size]
[size=2]SQLreturn = OleDbCommand1.ExecuteNonQuery[/size]
[size=2][color=#0000ff]Catch[/color][/size][size=2] ex [/size][size=2][color=#0000ff]As[/color][/size][size=2] Exception[/size]
[size=2]StatusBar1.Text = ex.Message[/size]
[size=2][color=#0000ff]Finally[/color][/size]
[size=2]OleDbConnection1.Close()[/size]
[size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Try[/color][/size]
[size=2][color=#0000ff]If[/color][/size][size=2] SQLreturn <> 1 [/size][size=2][color=#0000ff]Then
[/color][/size][size=2]StatusBar1.Text = "Insert failed"
[/size][size=2][color=#0000ff]Else[/color][/size]
[size=2]StatusBar1.Text = "INSERT completed"[/size]
[size=2]TextBoxDetails.Text = ""[/size]
[size=2]TextBoxDate.Text = ""[/size]
[size=2]TextBoxTitle.Text = ""
[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If[/color][/size]
[size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If[/color][/size]
[size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub
[/color][/size]
 
DO it the EZ way

Probably the most effective way would be to place a date-time picker on the form. Use the string-value of the date time picker for the @date parameter. If it kicks you out just place it between "#"`es (hashes).

(Sorry I don't have my code with me right now, but I reckon it's straightforward enough)
 
Back
Top