Help! VB.Net Date to Access 2000 Short Time

[Jay]

I previously posted this question under Visual Basic
newsgroup, but was advised to re-post here.

I'm hoping someone can help me solve an issue I'm having
with VB.Net and Access 2000.

Here's the issue. I hope I've included all relevant
information.

On a form, I have a DateTimePicker with the following
settings:
CustomFormat- HH:mm (to allow 24-hour clock)
ShowUpDown- True

I'm trying to have the user input a time. Once the time
has been input, I store the value in a Date variable.

I then try to insert the Time portion of that Date into a
Access 2000 DB, using a DataAdapter. The problem I'm
having is I can't seem to get the proper format to have
the time saved correctly. No matter what I'v tried, I
keep getting 00:00 in the time field.

The Time field in the DB is of type Short Time, with an
Input Mask of 99:99.

I've tried the following to save the data into the DB:

The line to input the value is:
Me.PlannedActivitiesDataAdapter.InsertCommand.Parameters.It
em("finishTime").Value =

I've tried the following to properly save the time, but
have no found one that works. The variable fTime is of
type Date and IS storing the time portion correctly, but I
can't seem to get it out of the variable to save it in the
DB.

fTime
"#" & fTime & "#"
FormatDateTime(fTime, DateFormat.LongTime)
"#" & FormatDateTime(fTime, DateFormat.LongTime) & "#"
FormatDateTime(fTime, DateFormat.ShortTime)
"#" & FormatDateTime(fTime, DateFormat.ShortTime) & "#"
FormatDateTime(fTime, 4)
FormatDateTime(fTime, 2)

I've tried inserting a Date variable into the DB, but the
time portion does not get stored (seems to default to
00:00). The Date/Time retrieved from the DateTimePicker
has the correct date and time, but it won't store it in
the DB correctly, and this is where I'm getting the error.

Also, if anyone knows how to hardcode a time and place it
in the DB, that would help too! I can worry about parsing
the time from the date later. I just can't seem to find
the format I'm supposed to use for Access 2000.

Also, if changing the datatype of the field in the DB to
something other than ShortTime will solve the problem, I'd
like to know. As long as the time gets stored somehow!

I hope that's clear enough. Any help would be greatly
appreciated! I'm at my wits end!

Thanks,
Jay

 

[Jay]

Hi Jim,

Thanks for the response. I've checked my code, and I
believe I'm already doing that, if somewhat differently.
I'm only INSERTing, thankfully there are no updates.

Here's the code (generated by the DataAdapter):

Me.OleDbInsertCommand12.CommandText = "INSERT INTO
ActivitiesList(finishTime) VALUES (?)"

Me.OleDbInsertCommand12.Parameters.Add(New
System.Data.OleDb.OleDbParameter("finishTime",
System.Data.OleDb.OleDbType.DBDate, 0, "finishTime"))

Me.ActivitiesDataAdapter.InsertCommand.Parameters.Item
("finishTime").Value = "12:30"


Even coded like that, the time is displayed as 00:00

If you have any other suggestions, I'm definitely willing
to try them!

Thanks again,
Jay

 

[vMike]

Here is a simple asp.net example, I don't know if it will help at all.

dim conn as OleDBConnection = new
OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=yourpath\file\dbnamehere")
Dim dbCommand as OleDBCommand
dim strsql as string
dim myTime as datetime = timevalue("10:30")
strsql = "Insert into tblTime (fldtime) Select #" & ctype(myTime,string) &
"# as fldtime;"
dbCommand=New OleDBCommand(strSql, conn)
Dim dbComm as OleDbDataAdapter = New OleDbDataAdapter
dbComm.SelectCommand = dbCommand
dbCommand.Connection.Open()
dbCommand.ExecuteNonQuery()
dbCommand=nothing
conn.dispose()

 

[Steven J. Reed]

Jay,

I was having the same problem. I changed the
System.Data.OleDb.OleDbType.DBDate to
System.Data.OleDb.OleDbType.DBTimeStamp and it worked!

....Steve