Storing a Time in Access Database

J

jbjavaman

I am working on A vb.net program for storing times for attendance purposes
and when i update my dataset and it goes into access the time is always 12:00
am but yet it does corrolate a date with no problem at all.. I checked the
dataset and the time is in there so it is a problem somewhere in access and I
can not get anyone who knows how to fix this problem. Any help would be much
appreciated ... Thank you ... !!!
 
B

Brendan Reynolds

The problem most likely lies in the SQL of the Insert and Update commands.
Have a look at those SQL statements, and if you still can't see the problem
yourself, try posting the SQL here.
 
J

John Vinson

I am working on A vb.net program for storing times for attendance purposes
and when i update my dataset and it goes into access the time is always 12:00
am but yet it does corrolate a date with no problem at all.. I checked the
dataset and the time is in there so it is a problem somewhere in access and I
can not get anyone who knows how to fix this problem. Any help would be much
appreciated ... Thank you ... !!!
Click to expand...

Care to post your code, or an example of the data in your dataset?


John W. Vinson[MVP]
 
J

jbjavaman

This is everthing you should need to help me.... thank you once again.!!!

MS Access Documenter (Access 2000):
F:\Time Test\Time Test\bin\TimeIn.mdb Wednesday, September 28, 2005
Table: TimeIn Page: 1
Columns
Name Type Size
ID Long Integer 4
TimeIn Date/Time 8

**********************************
VB.Net Solution info:

SQL from InsertCommand Object in data adapter odaTimeTest:
INSERT INTO TimeIn(TimeIn) VALUES (?)

Strongly typed dataset.
Schema:
<?xml version="1.0" standalone="yes"?>
<xs:schema id="dsTimeTest"
targetNamespace="http://www.tempuri.org/dsTimeTest.xsd"
xmlns:mstns="http://www.tempuri.org/dsTimeTest.xsd"
xmlns="http://www.tempuri.org/dsTimeTest.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="dsTimeTest" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="TimeIn">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" msdata:AutoIncrement="true"
type="xs:int" />
<xs:element name="TimeIn" type="xs:dateTime" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="Constraint1" msdata:primaryKey="true">
<xs:selector xpath=".//mstns:TimeIn" />
<xs:field xpath="mstns:ID" />
</xs:unique>
</xs:element>
</xs:schema>

Code:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
odaTimeTest.Fill(DsTimeTest1.TimeIn)
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Dim strTime As String
Dim drNew As DataRow
drNew = DsTimeTest1.TimeIn.NewRow

drNew("TimeIn") = "09:30 AM"
DsTimeTest1.TimeIn.Rows.Add(drNew)
odaTimeTest.Update(DsTimeTest1.TimeIn)
DsTimeTest1.AcceptChanges()
End Sub

Result in Access db:
TimeIn
ID TimeIn
14 12:00 AM
 
B

Brendan Reynolds

The problem appears to be specific to the use of an ADO.NET dataset. I can
reproduce the problem if I use a dataset as in your example, but the
following code, which uses the same connection and command, but by-passes
the dataset, inserts the correct values into the database ...

Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click

OleDbConnection1.Open()
OleDbInsertCommand1.CommandText = _
"INSERT INTO TimeIn (TimeIn) VALUES (#" & _
System.DateTime.Now.ToShortTimeString & "#)"
OleDbInsertCommand1.ExecuteNonQuery()
OleDbInsertCommand1.CommandText _
= "INSERT INTO TimeIn (TimeIn) VALUES (#9:30 AM#)"
OleDbInsertCommand1.ExecuteNonQuery()
OleDbConnection1.Close()

End Sub
--
Brendan Reynolds (MVP)

I don't have a great deal of experience of using ADO.NET datasets against
Jet databases, so I'm afraid I'm probably not going to be able to help you
further with this. As the problem appears to be specific to the use of an
ADO.NET dataset, I would suggest asking the question in the
microsoft.public.dotnet.framework.adonet newsgroup. If you're using
Microsoft's web-based interface to the newsgroup, you can access the above
newsgroup via the following URL ...

http://tinyurl.com/8pvom
 
Top