Why on earth VB.NET gives me this date, "12/30/1899", when it's supposed to give just a time value?

Reney

I am using Access Database in my program. The column in the table that I am
going to use has date/time value with Medium Time selected. (HH:mm). The
program is recording a clock in time to this field, which is the time when
the entry is made. If you check the database, it shows the correct time in
the correct format. But when you are calling it with a dataset and showing
with a datagrid, it doesn't show the time value but it always shows
12/30/1899. I searched for this error a lot, and found out that, that's the
minimum year used in Access, but why is it not bringing the time value which
I am calling for? Any help would be highly appreciated. I have been trying
to resolve this error for two days and two nights now and really bored of
it. (Kinda like it though)

Here is the code that inserts the clock in time to the database
-----------------------------------------------------------------------

Dim dayOfWeek As Date
Dim clockIn As Date

dayOfWeek = CDate(String.Format("{0:d}", Now()))
'************************************************* *
'THAT'S WHERE I DEFINE THE TIME VALUE TO BE INSERTED IN THE ACCESS DATABASE
clockIn = CDate(String.Format("{0:t}", Now()))
'************************************************* *

Dim conn As New OleDbConnection(connectionString)
Dim sql As String = "SELECT * FROM TimeLog WHERE employeeID = " _
& "'" & userName & "'"

Try
conn.Open()
Dim da As New OleDbDataAdapter(sql, conn)
Dim ds As New DataSet()

da.Fill(ds, "TimeLog")
Dim cmdBuilder As New OleDbCommandBuilder(da)
da.InsertCommand = cmdBuilder.GetInsertCommand
da.UpdateCommand = cmdBuilder.GetUpdateCommand
Dim dt As DataTable = ds.Tables(0)

With dt
Dim dr As DataRow = .NewRow
dr("employeeID") = userName
dr("dayOfWeek") = dayOfWeek
'***************************************
'THE DATA IS INSERTED HERE
dr("clockStart") = clockIn
'***************************************
.Rows.Add(dr)
End With

da.Update(ds, "TimeLog")
da.Dispose()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
Me.Close()
End Try

----------------------------------------------------------------------------
--------
Now in another form, when I call the database table, the time value(clockIn)
above is shown as #12/30/1899#
Here is the second form that calls the values in the database
----------------------------------------------------------------------------
--------

Dim conn As New OleDbConnection(connectionString)
Dim sql As String = "SELECT dayOfWeek As [Dates], TimeLog.employeeID AS
[UserName], " _
& "firstName, lastName, " _
& "clockStart AS [ClockIn], clockEnd As
[ClockOut], " _ ' THOSE TWO VALUES ARE BROUGHT TO THE DATAGRID WITH
12/30/1899 VALUES
& "payRate, DATEDIFF (dd, clockStart, clockEnd)
AS [Hours] " _
& "FROM Employee INNER JOIN TimeLog ON
Employee.employeeID = TimeLog.employeeID " _
& "WHERE TimeLog.employeeID = " & "'" & userName
& "' " _
& "AND dayOfWeek >= " & "#" & weekOneStartDay &
"# " _
& "ORDER BY dayOfWeek"
Try
conn.Open()
Dim da As New OleDbDataAdapter(sql, conn)
Dim ds As New DataSet()
da.Fill(ds, "EmployeeTimeLog")
da.Dispose()

If ds.Tables.Count = 0 Then
MessageBox.Show("Invalid username", "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
Else
dgrTimeLog.DataSource = ds.Tables(0)
End If

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
dgrTimeLog.Visible = True
End Try

Thanks in advance for your times and efforts to read those codes..
RENEY

Nov 20 '05 #1

Reney

Hi Joe,

Thanks a lot...Now I understand why it is giving me this weird date. I
changed the program as it inserts a general date(G)and time now. But still,
when I run the query, it only gives me the year part of it. But I need just
the time portion. How could I cut the date part from the query? I am trying
to use the Datepart function, but I couldn't do it yet. If you have any
idea, I would be so glad,

Thanks,
Reney

"Joe Fallon" <jf******@nospamtwcny.rr.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

Access date/time values are based on the integer and decimal parts of the
stored value.
So, 0.5 is date zero and it is 1/2 way through the day so it is Noon.

If you only pass a time value, then the date part is always 0.
That is why you get 12/30/1899.

--
Joe Fallon

Nov 20 '05 #2

Cindy Meister -WordMVP-

Hi Jerry,

As far as I know, the minimum date used in Access is 1/1/1000

It may be the minimum Access can handle, but it does not equate
to 0 (zero), it would be a (very large) negative number. (See
Joe's reply)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail :-)

Nov 20 '05 #3

Cindy Meister -WordMVP-

Hi Reney,

How could I cut the date part from the query?

I might use the Format() function:

Format([clockStart], "hh:mm:ss") AS clockIn

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail
:-)

Nov 20 '05 #4