DateTime problems

[cfisher440]

In a nutshell, I need to add time values to a MS Access database in the format HH:mm (05:14)
The program is working for the most part, but if somebody could look at what I have below and offer me a solution I would appreciate it.
First the program will do this:

Dim strHDTimeLine As String() = Regex.Split(strHDLine71, " ")
Dim strHDTime(6) As String
strHDTime(0) = strHDTimeLine(58)
strHDTime(1) = strHDTimeLine(59)
strHDTime(2) = strHDTimeLine(60)

In this case strHDTime(0) = "05:49" then I convert the values to times.

Dim dates(6) As DateTime
dates(0) = FormatDateTime(CDate(strHDTime(0)), DateFormat.ShortTime)
dates(1) = FormatDateTime(CDate(strHDTime(1)), DateFormat.ShortTime)
dates(2) = FormatDateTime(CDate(strHDTime(2)), DateFormat.ShortTime)

Now dates(0) = #5:49:00 AM#
From there, I attempt to put these values in the database with my function call I made

AddDatabase(totHDRec, totHDAnsd, TotHDAbd, HDxfer, fHDrec, dates(0), dates(1), dates(2))

In the function (Don't worry about a1 - a5 those values have been established and add themselves to the database)

Public Sub AddDatabase(ByVal a1 As Int32, ByVal a2 As Int32, ByVal a3 As Int32, ByVal a4 As Int32, _
ByVal a5 As Int32, ByVal a6 As DateTime, ByVal a7 As DateTime, ByVal a8 As DateTime)
Dim newRow As DataRow = DsVRUB1.ACDSysSum.NewRow
newRow("TotalRecieved") = a1
newRow("TotalAnswered") = a2
newRow("TotalAbandoned") = a3
newRow("Transfered") = a4
newRow("FirstRecord") = a5
newRow("AvgTotTalk") = FormatDateTime(CDate(a6), DateFormat.ShortTime)
newRow("AvgTotAnsd") = FormatDateTime(CDate(a7), DateFormat.ShortTime)
newRow("AvgWork") = FormatDateTime(CDate(a8), DateFormat.ShortTime)
'newRow("AvgTotTalk") = a6.ToShortTimeString
'newRow("AvgTotAnsd") = a7.ToShortTimeString
'newRow("AvgWork") =
'newRow("AvgWork") = a8.ToShortTimeString
' dates(1)
'newRow("AvgWork") = dates(2)
Try
DsVRUB1.ACDSysSum.Rows.Add(newRow)
daVRUSys.Update(DsVRUB1, "ACDSysSum")
lblDone.Text = "Count of records Imported = " & x
x = x + 1
Catch ex As Exception
lblDone.Visible = False
lblDone.Text = ""
MsgBox("Error: " & ex.ToString, MsgBoxStyle.Critical, "ERROR IN Add to DB")
Finally
If conVRU.State = ConnectionState.Open Then
conVRU.Close()
End If
End Try
End Sub

Now a6 is equal to #1/21/2006 5:49:00 AM# {Date}
All the other values (that are not DateTime) are successfully being added to the database. My values set up in my MS Access database for the Times are as DateTime with a format of ShortTime. The values in the datset for those values (a6 - a8) are set as datetime. I have tried many ways of formatting the datetime as a short time value before adding it to the database to no avail.

In summary, the value coming into the function is the format #5:49:00 AM#. Before it attempts to add itself to the database, it is the format #1/21/2006 5:49:00 AM# {Date}. I need it to be in the format #05:49# as a short time.

Any help would be greatly appreciated.

 

[cfisher440]

Is it the dataset?

I figured it doesn't matter how I format the data, it always goes to the same format in the end. This leads me to believe something is wrong in the dataset. Is there a way to format it within the dataset to a shorttime?

 

[jmcilhinney]

"Format" is meaningless when it comes to DateTime objects except when you want to actually display them as a string. Every DateTime object is stored in exactly the same way, and that includes a date and a time portion. You should just be storing your DateTime objects in Access directly, set the column property in Access if you want to display just the time portion in Access and use code in your app to display just the time portion when you want to display it. If you are talking about a time of day then the date portion is irrelevant so just ignore it. Again it is not possible to get a DateTime object without a date portion. You can use the TimeOfDay property to get a TimeSpan object but you cannot save those directly to Access.

 

[jmcilhinney]

Additionally, if you do want to format a DateTime object a certain way when displaying it you should forget the Runtime functions. You have ToLongDateString, ToShortDateString, ToLongTimeString, ToShortTimeString and ToString. In your case you would use ToString("HH:mm"). This doesn't affect the underlying DateTime object at all though. It still contains data for all date and time portions.

 

[cfisher440]

thank you, but . . .

When I generate the dataset those fields will be generated as date/time fields since that is what they are in the Access database, so should I change the dataset value of date/time to string?

 

[jmcilhinney]

When I generate the dataset those fields will be generated as date/time fields since that is what they are in the Access database, so should I change the dataset value of date/time to string?

No you shouldn't. If you are dealing with dates the it is approriate to maintain them as DateTime object s so that you can manipulate them as dateTime objects. The only time you need to worry about format is when they are being displayed to the user. If you want to display a DateTime object as a string in a control then you can call one of the methods I mentioned to format it in the way you want, but this will still not affect the object itself, which will still be stored in the same binary form as it always was.

 

[cfisher440]

Not sure why it's still not working

The value will not be added to the database still. I believe I am going to rephrase my whole problem into one simple question.

From a VB front end, can you tell me or give me an example of how to add a time value in the format of HH:mm to an Access database?

It needs to be a time value ShortTime value within the Access database so it can be queried properly later on in my program process for reporting purposes.

I don't mean to come off like you haven't helped me because you have, I just don't think I am understanding it totally and this may simplify it.

Thank you.

 

[DavidT_macktool]

You will not save the Time value to the access database in any format. Save the entire DateTime value as date and time. You will access the data ('later on in your project') as DateTime also. Once the data is retrieved from the database you will manipulate it to display only the time in HH:mm format.

Saving the date along with the time will not effect your project, It would be like saving a persons First and Last name in the datbase but only displaying the last names in a grid.

The whole Date and Time will be loaded/saved, YOU will choose to ignore the date portion and only display the time in any format you choose.