By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Help! VB.Net Date to Access 2000 Short Time

P: n/a
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.Para meters.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

Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
-----Original Message-----
Hi Jay,

It should go something like this.......

me.oleDbUpdateCommand1.CommandText = "UPDATE Stories SET
ProdDate = ?"

me.oleDbUpdateCommand1.Parameters.Add("ProdDate ",
System.Data.OleDb.OleDbType.DBDate, 0, "ProdDate")

me.oleDbUpdateCommand1.Parameters("ProdDate").Val ue
= "12:30"

You need to define your parameter more specifically and
once that's done a String will be converted to the
database type properly.
-----Original Message-----
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 aAccess 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.Pa rameters. I
t
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

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

.

.

Jul 19 '05 #2

P: n/a
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()
"Jay" <ru***********@bigfoot.com> wrote in message
news:84****************************@phx.gbl...
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.Para meters.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

Jul 19 '05 #3

P: n/a
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
-----Original Message-----
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
-----Original Message-----
Hi Jay,

It should go something like this.......

me.oleDbUpdateCommand1.CommandText = "UPDATE Stories SET
ProdDate = ?"

me.oleDbUpdateCommand1.Parameters.Add("ProdDate" ,
System.Data.OleDb.OleDbType.DBDate, 0, "ProdDate")

me.oleDbUpdateCommand1.Parameters("ProdDate").Va lue
= "12:30"

You need to define your parameter more specifically and
once that's done a String will be converted to the
database type properly.
-----Original Message-----
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 havingwith 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 timehas been input, I store the value in a Date variable.

I then try to insert the Time portion of that Date intoaAccess 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.P arameters ..
I
t
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

thetime portion does not get stored (seems to default to
00:00). The Date/Time retrieved from the
DateTimePickerhas 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 placeitin the DB, that would help too! I can worry about

parsing
the time from the date later. I just can't seem to findthe format I'm supposed to use for Access 2000.

Also, if changing the datatype of the field in the DB tosomething 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

.

.

.

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.