473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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.
Oct 31 '07 #1
11 3865
r035198x
13,262 8TB
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?
Oct 31 '07 #2
shweta123
692 Expert 512MB
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.
Oct 31 '07 #3
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 'tblPrivateLesson'.

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'RetrieveAMOrPM() grabs the radio button value.
  3. LessonTime = msktxtLessonTime.Text & " " & RetrieveAMOrPM()
  4.  
  5. 'Adds a whole bunch of other unimportant fields to a table in the database, including LessonTime.
  6. LearnToRideDataSet.tblPrivateLesson.Rows.Add(LessonTime) 'And other fields.
  7.  
  8. Me.Validate()
  9. Me.TblPrivateLessonBindingSource.EndEdit()
  10.                     Me.TblPrivateLessonTableAdapter.Update(Me.LearnToRideDataSet.tblPrivateLesson)
  11.  
  12.  
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!
Oct 31 '07 #4
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. =(
Oct 31 '07 #5
Plater
7,872 Expert 4TB
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?
Oct 31 '07 #6
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.
Nov 1 '07 #7
Plater
7,872 Expert 4TB
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?
Nov 1 '07 #8
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 'LessonStartTime'.

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. ><
Nov 3 '07 #9
balabaster
797 Expert 512MB
The column's name isn't actually time, I was just using that to make its purpose clearer. It's actually 'LessonStartTime'.

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.Globalization 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.
Nov 3 '07 #10
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.Globalization 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.
Nov 3 '07 #11
Plater
7,872 Expert 4TB
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.
Nov 5 '07 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Lauren Quantrell | last post by:
I have two columns in a table: StartDate DateTime and StartTime DateTime. The StartDate column holds a value such as 07/16/2004 The StartTime column holds a value such as 3:00:00 PM I want to...
16
by: PK9 | last post by:
I have a string variable that holds the equivalent of a DateTime value. I pulled this datetime from the database and I want to strip off the time portion before displaying to the user. I am...
4
by: Polly | last post by:
I had a macro that ran a parameter query and created and opened an Excel file with the system date as part of the file name, but I had to change the file name by hand. So I converted the macro to...
1
by: Scott269 | last post by:
So I've got an old MS Works database I imported into Access. I needed a primary key so I created a record number field that was just the record number I manually inserted when I entered it in the...
3
by: Reney | last post by:
I am using Access Database in my program. The column in the table that I am going to use has date/time value with Medium Time selected. (HH:mm). The program is recording a clock in time to this...
15
by: Khurram | last post by:
I have a problem while inserting time value in the datetime Field. I want to Insert only time value in this format (08:15:39) into the SQL Date time Field. I tried to many ways, I can extract...
1
by: Dave | last post by:
I'm working with SQL Server 2005 and VB.Net. I only want to store the date (not the time) in the DateTime field. I have a text field that is bound to a DateTime field in SQL. When I run the...
3
by: Jim in Arizona | last post by:
I have a gridview that's being populated from an access db query. The problem I'm having is that the date/time fields in access that are populating the gridview are showing both date and time, when...
4
by: SilentThunderer | last post by:
Hey folks, Let me start out by letting you know what I'm working with. I'm building an application in VB 2005 that is basically a userform that employees can use to "Clock in". The form...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.