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