Date/Time in Access

[Nathan]

I'm having trouble storing the value of Now in an Access table. I'm using
VB.NET 2002. I need to store a time value, but no matter what I do I end up
getting just a date value in Access. I'm doing something like this:

Dim NewRow as DataRow = MyTable.NewRow
NewRow("InTime") = Now
MyTable.Rows.Add(NewRow)
DataAdaptor.Update(MyTable)

The value of CDate(NewRow("InTime")) is correct until I refresh the dataset
with data from the database. All I get is the correct date and a time of
12:00:00. I've tried using every possible format of Date/Time in Access,
including my own custom format.

Thanks for the help

 

[Cor Ligthert]

Nathan,

I tried this and it gives the correct time as I expected.

\\\If you want to try it you have to set a reference to ADO X for ....2.x
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\test1\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\test1\db1.mdb")
'To make tables we use Adonet
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=C:\test1\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE Times ( " & _
"AutoId int identity ," & _
"TTime datetime, " & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Dim da As New OleDb.OleDbDataAdapter("Select * from Times", conn)
Dim ds As New DataSet
da.Fill(ds)
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(0)("TTime") = Now
Dim cmb As New OleDb.OleDbCommandBuilder(da)
da.Update(ds)
ds.Clear()
da.Fill(ds)
MessageBox.Show(ds.Tables(0).Rows(0)("TTime").ToString)
End Sub
End Class
///
I hope this helps

Cor

 

[Paul Clement]

¤ I'm having trouble storing the value of Now in an Access table. I'm using
¤ VB.NET 2002. I need to store a time value, but no matter what I do I end up
¤ getting just a date value in Access. I'm doing something like this:
¤
¤ Dim NewRow as DataRow = MyTable.NewRow
¤ NewRow("InTime") = Now
¤ MyTable.Rows.Add(NewRow)
¤ DataAdaptor.Update(MyTable)
¤
¤ The value of CDate(NewRow("InTime")) is correct until I refresh the dataset
¤ with data from the database. All I get is the correct date and a time of
¤ 12:00:00. I've tried using every possible format of Date/Time in Access,
¤ including my own custom format.

What does your InsertCommand look like?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Nathan]

The insert command is generated by the designer:
"INSERT INTO Register(ClockDate, InTime, OutTime, StaffID, TotalHours)
VALUES (?, ?, ?, ?, ?)"

(In creating a new row, I also create values for the ClockDate and StaffID
columns)

 

[Nathan]

Cor,
Thanks for the help, but your method is a bit too complicated for a beginner
like me. I want to be able to set the properties of the Access table
directly in Access, and then access the table from the application.

Nathan,

I tried this and it gives the correct time as I expected.

\\\If you want to try it you have to set a reference to ADO X for ....2.x
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\test1\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\test1\db1.mdb")
'To make tables we use Adonet
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=C:\test1\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE Times ( " & _
"AutoId int identity ," & _
"TTime datetime, " & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Dim da As New OleDb.OleDbDataAdapter("Select * from Times", conn)
Dim ds As New DataSet
da.Fill(ds)
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(0)("TTime") = Now
Dim cmb As New OleDb.OleDbCommandBuilder(da)
da.Update(ds)
ds.Clear()
da.Fill(ds)
MessageBox.Show(ds.Tables(0).Rows(0)("TTime").ToString)
End Sub
End Class
///
I hope this helps

Cor

I'm having trouble storing the value of Now in an Access table. I'm
using VB.NET 2002. I need to store a time value, but no matter what I do
I end up getting just a date value in Access. I'm doing something like
this:

Dim NewRow as DataRow = MyTable.NewRow
NewRow("InTime") = Now
MyTable.Rows.Add(NewRow)
DataAdaptor.Update(MyTable)

The value of CDate(NewRow("InTime")) is correct until I refresh the
dataset with data from the database. All I get is the correct date and a
time of 12:00:00. I've tried using every possible format of Date/Time in
Access, including my own custom format.

Thanks for the help

 

[Cor Ligthert]

Nathan,

Cannot be complicated, what I showed is a complete sample where I as well
make an access database including a table to make it possible to test.

In fact is this the part where you should dealing with.
\\\
Dim da As New OleDb.OleDbDataAdapter("Select * from Times",
connection)
'Make a dataadapter using a selectstring and the connection
Dim ds As New DataSet
'make a dataset
da.Fill(ds)
'file the first table of the dataset
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
'Add a new empty row to that table
ds.Tables(0).Rows(0)("TTime") = Now
'Set in that the time in the field TTime
Dim cmb As New OleDb.OleDbCommandBuilder(da)
'Make all commands including the insert
da.Update(ds)
'Do the update
ds.Clear()
'Cear the dataset to show the sample
da.Fill(ds)
'Read the dataset back with the same adapter
MessageBox.Show(ds.Tables(0).Rows(0)("TTime").ToString)
'Show that is taken the date and time from today
///


(See that in this sample now is at the end only one row in that table and it
assumes it start with an empty table)

I hope this makes it clear for you?

Cor

 

[Paul Clement]

¤ The insert command is generated by the designer:
¤ "INSERT INTO Register(ClockDate, InTime, OutTime, StaffID, TotalHours)
¤ VALUES (?, ?, ?, ?, ?)"
¤
¤ (In creating a new row, I also create values for the ClockDate and StaffID
¤ columns)

I don't see how you have defined your parameters but I will assume you are using OleDbType.DBDate.
For Access/Jet you should use OleDbType.Date instead if you don't want the time to be truncated.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Chef Groovy]

Its a bug (unknown or unacknowledged by ms), but open the "Windows Form
Designer generated code" #region and replace all instances of DBDATE with
DATE, works like a charm.

I deal with vb.net 2003 daily with access databases, and have to make
that replace everytime I start a new sln. Its a pain, and I hope they
fix it.

But trust me, I feel your pain, took me for friggin ever to figure that
one out.

Chef Groovy

 

[Nathan]

Weird . . . but I'll give it a try. Thank you all for your help.