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

  • Thread starter Thread starter Reney
  • Start date Start date
R

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
 
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
 
Back
Top