VB .NET - Entering 'time' as a string into database... But database attaches a date!

[dheroan]

Okay, so after adding an Access database as a data source to my project, I decided to change the 'time' field in a particular table from date/time to a string value for various reasons. I made this change in the original database, the copied database in my project's bin\Debug folder and the dataset. The changes were all saved and everything looked fine.

But then when I went to add an actual 'time' string into the database (in the format 12:00 PM), when I checked the value entered in the field for that record in the database, it had today's date attached to it as well (so 31/10/2007 12:00 PM). I checked that I was actually entering a '12:00 PM' string into the field, and that was fine. But for some reason, when it is actually entered in the database, a date is attached... Even though the field is a string in the database as well!

I really have no idea what the deal with this is... Any advice at all is appreciated.

[r035198x]

Okay, so after adding an Access database as a data source to my project, I decided to change the 'time' field in a particular table from date/time to a string value for various reasons. I made this change in the original database, the copied database in my project's bin\Debug folder and the dataset. The changes were all saved and everything looked fine.

But then when I went to add an actual 'time' string into the database (in the format 12:00 PM), when I checked the value entered in the field for that record in the database, it had today's date attached to it as well (so 31/10/2007 12:00 PM). I checked that I was actually entering a '12:00 PM' string into the field, and that was fine. But for some reason, when it is actually entered in the database, a date is attached... Even though the field is a string in the database as well!

I really have no idea what the deal with this is... Any advice at all is appreciated.

Any chance that you are going to post the actual code you used?

[shweta123]

Hi,

Please check that if you have put any validations while designing the table
like validations or default value.Because of which it is possible that , it will attach date to the time even if you add only time value.

[dheroan]

Well the thing is that the program is doing its job fine (and it's pretty difficult to explain briefly). If I check the value right before it goes into the database, it shows that it's a string in the exact format that I want (so 12:00 PM or whatever). But when I open the database to see what values got entered, it shows this time as well as today's date in the 'time' field.

For example, I have a masked text box that accepts a string in the format 00:00 and radio buttons that allow the user to select AM or PM. This value is then entered as a field in the database table 'tblPrivateLess on'.

'RetrieveAMOrPM() grabs the radio button value.
LessonTime = msktxtLessonTime.Text & " " & RetrieveAMOrPM()

'Adds a whole bunch of other unimportant fields to a table in the database, including LessonTime.
LearnToRideDataSet.tblPrivateLesson.Rows.Add(LessonTime) 'And other fields.

Me.Validate()
Me.TblPrivateLessonBindingSource.EndEdit()
                    Me.TblPrivateLessonTableAdapter.Update(Me.LearnToRideDataSet.tblPrivateLesson)

I thought it must've had something to do with the data source and how I changed the data type of the time field to string... But it doesn't make sense because the field has visibly been changed to a string everywhere that I can see (in the dataset AND database itself). This is driving me nuts!

[dheroan]

Hi,

Please check that if you have put any validations while designing the table
like validations or default value.Because of which it is possible that , it will attach date to the time even if you add only time value.

I didn't set any validation rules or default values on the field in the database. =(

[Plater]

Try adding ANOTHER column of type varchar/string and adding that lessontime string a SECOND time to that new column. (So it will try to save the same string into two different columns).
Maybe there will be a different result?

[dheroan]

Try adding ANOTHER column of type varchar/string and adding that lessontime string a SECOND time to that new column. (So it will try to save the same string into two different columns).
Maybe there will be a different result?

I tried that by adding a new column in the Access table in the database and updating the dataset.xsd file. When I went to add the same string into the new column (using the same method as in the above code example, but with an extra column in the Add() method), nothing appeared in that column when the record was created.

I tried completely deleting the new column and the old column (in both the data source and the dataset). I added a completely new 'time' column in the same position as the old one, with a string data type. Same thing again when I went to add the time in as a string... The record was created successfully, but the 'time' field for that record was blank.

I have no clue what would be causing this.

[Plater]

Maybe what you are naming the column has a special meaning? Is the column's name "Time"? try naming it like "columnT" or something maybe?

[dheroan]

Maybe what you are naming the column has a special meaning? Is the column's name "Time"? try naming it like "columnT" or something maybe?

The column's name isn't actually time, I was just using that to make its purpose clearer. It's actually 'LessonStartTim e'.

The only way I can think of to deal with this is to delete the current data source and then re-attach it... But that'll be an enormous pain in the butt. ><

[balabaster]

The column's name isn't actually time, I was just using that to make its purpose clearer. It's actually 'LessonStartTim e'.

The only way I can think of to deal with this is to delete the current data source and then re-attach it... But that'll be an enormous pain in the butt. ><

This usually has to do with the data format of the field in your table. A DateTime is always stored as just that Date & Time. If one or the other is not important you then you can just ignore it at runtime. When you save it in the database it would be stored as 1/1/1800 and the relevant time. The alternative is to make sure the field is actually of string data type and store the time as a time string, maybe in 24 hour - i.e. "1402" (for 2:02pm). Now in .NET we also have cultures where you can use the System.Globaliz ation to force a parse of an exact date or time string using ParseExact to set the format "HH:mm:ss". Hopefully that should point you in the right direction.

[dheroan]

This usually has to do with the data format of the field in your table. A DateTime is always stored as just that Date & Time. If one or the other is not important you then you can just ignore it at runtime. When you save it in the database it would be stored as 1/1/1800 and the relevant time. The alternative is to make sure the field is actually of string data type and store the time as a time string, maybe in 24 hour - i.e. "1402" (for 2:02pm). Now in .NET we also have cultures where you can use the System.Globaliz ation to force a parse of an exact date or time string using ParseExact to set the format "HH:mm:ss". Hopefully that should point you in the right direction.

The column already has a string data type (I changed it to this from DateTime after attaching the database as a data source) and I'm passing in the time as a string. The program outputs a time string in the format '00:00 PM', but as soon as it hits the database, the database treats it as if the column were still DateTime, rather than string, and attaches a date. I know the program has the right output, as I've checked this numerous times. Which leads me to believe that the problem occurred when I changed the data type of the column after attaching the database as the project's data source.

[Plater]

I think when you attach something as a DataSource it retains it's "layout" (what columns, what datatypes, etc). You will probably have to re-do that reference.