ASP.NET - VB.NET - TextBox control to accept time only - SQL Server 2000 db parameters

I have a webform that will be used for data entry into a sql server 2000 db.
I have two textbox controls on the form where a time will be entered:  txtTimeIn and txtTimeOut

I am currently trying to write the code that will allow me to add (insert) a new record into the database -- inserting what has been entered on screen.
When I test the Add feature, I am getting the following error:
Cast from string "" to type 'Date' is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Cast from string "" to type 'Date' is not valid.

Source Error:

Line 261:        cmdHomeCompliances.Parameters.Add("@WhereSeen", SqlDbType.Int, 4).Value = HomeCompliance.WhereSeen
Line 262:
Line 263:        If HomeCompliance.TimeIn = "" Then
Line 264:            cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = System.DBNull.Value
Line 265:        Else

Here is a little more of my actual code from that section:

        If HomeCompliance.TimeIn = "" Then
            cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = System.DBNull.Value
            cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = HomeCompliance.TimeIn
        End If

        If HomeCompliance.TimeIn = "" Then
            cmdHomeCompliances.Parameters.Add("@TimeOut", SqlDbType.DateTime).Value = System.DBNull.Value
            cmdHomeCompliances.Parameters.Add("@TimeOut", SqlDbType.DateTime).Value = HomeCompliance.TimeOut
        End If

What is the syntax I need to use to handle Time input from this textbox control?  Also, I want it to allow for a 24-hour format as input, in case that makes any difference in the syntax.

Thank you!!!

Who is Participating?
rstraderConnect With a Mentor Commented:
try using DateTime.Parse

would look something like
cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = DateTime.Parse(HomeCompliance.TimeIn.ToString())

what is the datatype of HomeCompliance.TimeIn? ..

If HomeCompliance.TimeIn is nothing Then
sah18Author Commented:
The datatype of HomeCompliance.TimeIn and HomeCompliance.TimeOut are:
    Public TimeIn As DateTime
    Public TimeOut As DateTime

I didn't see an option for sqlserver nor in .net for a time-only datatype.

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

sah18Author Commented:
rstrader -- what does DateTime.Parse do?
converts string representation of a date and time to its DateTime equivalent
sah18Author Commented:
How do I check if a DateTime field is null?

rejojohny --
"If HomeCompliance.TimeIn Is Nothing then ..."
does not work -- it gives the following message in the compiler:
"'Is' requires operands that have reference types, but this operand has the value type 'Date'"

the value coming from the web form is a string
use String.IsNullOrEmpty Method

then convert it to DateTime before inserting it into the db.

If String.IsNullOrEmpty(HomeCompliance.TimeIn) Then
            cmdHomeCompliances.Parameters.Add("@TimeOut", SqlDbType.DateTime).Value = System.DBNull.Value
            cmdHomeCompliances.Parameters.Add("@TimeOut", SqlDbType.DateTime).Value = DateTime.Parse(HomeCompliance.TimeIn.ToString())
        End If

sah18Author Commented:
I'm using Visual Studio 2003 -- not yet on 2005.

It looks like on the weblink, they indicate the following:
String.IsNullOrEmpty Method
Note: This method is new in the .NET Framework version 2.0.

When I tried to use it, I get "IsNullOrEmpty is not a member of String"

Is there a way in .NET version 1.1 to do the same check to see if a string is null?
sah18Author Commented:
Also, just to make sure it's clear, HomeCompliance.TimeIn is not a string -- it is a DateTime datatype.
sah18Author Commented:
Okay, for the moment -- just to get a little farther -- I temporarily got rid of my ifthenelse statement and am not checking whether it is null yet or not (since I still can seem to get the syntax right for that).  For testing, I entered 10:00 for TimeIn and 11:00 for TimeOut.  When the insert is actually attempted in the database, I'm getting the following SQL Server exception error:

"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."

And in the watch for HomeCompliance.TimeIn I see #10:00:00 AM#
and for HomeCompliance.TimeOut I see #11:00:00 AM#

There are also 2 more date fields that I've chosen not yet to populate via the form (but the fields exist in the database, and also in HomeCompliance).  Their values in the watch appear as #12:00:00 AM#

I still must not be handling these times correctly.  What am I doing wrong?

This is my first time trying to have a field that will hold a time-only and not just a date, so I'm sure I'm doing something wrong!!
I really need to get this working!!

"Cast from string "" to type 'Date' is not valid.
Exception Details: System.InvalidCastException: Cast from string "" to type 'Date' is not valid."

HomeCompliance.TimeIn as it is captured from the webform is a String then you are trying to store it as a date in your db

you just need to check if the string is empty or null and then if not convert it to a DateTime and store it

If HomeCompliance.TimeIn != "" 

and as
Rejojohny said
If HomeCompliance.TimeIn is nothing Then

If HomeCompliance.TimeIn is nothing || String.Empty(HomeCompliance.TimeIn)  then

sah18Author Commented:
I feel like we're going in circles.  No, I defined the class HomeCompliance, so I definitely know what datatypes each variable is.  Here is the full class, if that helps:
Public Class HomeCompliance
    Public FormID As Integer
    Public SubjID As String
    Public DateCollected As Date
    Public VisitNum As Integer
    Public SiteNum As Integer
    Public SubjInit As String
    Public EvalID As String
    Public DEID As String
    Public DEDate As Date
    Public WhereSeen As Integer

    Public TimeIn As DateTime
    Public TimeOut As DateTime

    Public Skill1Correct As Integer
    Public Skill1Teach As Integer
    Public Skill2Correct As Integer
    Public Skill2Teach As Integer
    Public Skill3Correct As Integer
    Public Skill3Teach As Integer
    Public Skill4Correct As Integer
    Public Skill4Teach As Integer
    Public Skill5Correct As Integer
    Public Skill5Teach As Integer
    Public Skill6Correct As Integer
    Public Skill6Teach As Integer
    Public NotEMG As Integer
    Public CurrThreshold As Integer
    Public SubjOpinion As Integer
    Public EMGTrial1 As Decimal
    Public EMGTrial2 As Decimal
    Public EMGAve As Decimal
    Public EMG10pAve As Decimal
    Public EMGNewThres As Decimal
    Public PrevAmp As Integer
    Public NewAmp As Integer
    Public PrevStimPeriod As Integer
    Public NewStimPeriod As Integer
    Public PrevRest As Integer
    Public NewRest As Integer
    Public DevUses As Integer
    Public DevMins As Integer
    Public ParamCorrect As Integer
    Public DiaryDone As Integer
    Public PT As Integer
    Public PTWhere As String
    Public PTDCDate As Date
    Public OT As Integer
    Public OTWhere As String
    Public OTDCDate As Date
    Public Botox As Integer
    Public Baclofen As Integer
    Public Steroid As Integer
    Public TherapyNone As Integer
    Public Sling As Integer
    Public SlingDays As Integer
    Public SlingHours As Integer
    Public Splint As Integer
    Public SplintDays As Integer
    Public SplintHours As Integer
    Public DevicesNone As Integer
    Public LastModWho As String
    Public LastModDate As Date
End Class

So, you can see that HomeCompliance.TimeIn and HomeCompliance.TimeOut are of datatype DateType, as I mentioned previosly.

and the textbox controls on the form are called:
txtTimeIn and txtTimeOut

I get the values from the textbox controls and save everything on the form into the HomeCompliance object.  For TimeIn/TimeOut those lines look like:

HomeCompliance.TimeIn = CType(Me.txtTimeIn.Text, DateTime)
HomeCompliance.TimeIn = CType(Me.txtTimeIn.Text, DateTime)

(these lines may not be right -- I'm not sure -- this is part of what I'm not understanding with how to handle grabbing the time data from the textboxes)

So, I get errors when I try to use string functions on the HomeCompliance.TimeIn variable because it isn't a string -- it is a DateTime datatype.

Then, I'm trying to build a sql query string with parameters to insert a row into the database.  I've done this before several times, without a problem, however, I've never had a Time only field to deal with before.  Currently, from your suggestions, I have the following for setting the value of my parameter:

cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = DateTime.Parse(HomeCompliance.TimeIn.ToString())

(i got rid of the if/then/else for the moment, since it kept erroring when i tried to check if it was null)

At this point then, with the syntax you see above, when the sql query executes, I get this error as a sqlserver exception:
"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."

I still don't know how to do what I want!

I need to be able to grab the time data from txtTimeIn, store it in HomeCompliance.TimeIn, and insert it into my sql server 2000 database field TimeIn, which is of type DateTime also.  I still am not sure what syntax to use to do this.

Is this clearer now - what I'm trying to do?

sah18Author Commented:
Also, I still don't really understand the line:

cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = DateTime.Parse(HomeCompliance.TimeIn.ToString())

It seems like I'm taking something that is already a DateTime, converting it to a string, only to convert it back again to DateTime.  I don't understand why this is necessary - ?
Thanks for the additional info.

"It seems like I'm taking something that is already a DateTime, converting it to a string, only to convert it back again to DateTime.  I don't understand why this is necessary - ?"

you are right, It wouldn't -- I made an incorrect assumption based on your initial post.  I clearly didn't have enough information.

but your issue is still
"Cast from string "" to type 'Date' is not valid."  

so before you set HomeCompliance.TimeIn you need to check txtTimeIn.Text for a null or empty string

"I've never had a Time only field to deal with before."
I'm not a SQL Server expert but if your using a DateTime datatype there has to be a date portion.  It doesn't appear that SQL 2000 (assumption that you are using this version) does not have a time only datatype

I'm sure that I still don't completly understand what your doing here, but I don't that I would store just a timestamp i.e 20:00.00 and 01:00:00 without the date portion means what?  you couldn't do an accurate calculation without the date part?

So, I think that I would store the timein/ timeout as date and time or if you really just want times why not just store them as a varchar? or use a fixed date and still use the datetime datatype, then just strip off the date portion when you pull it back from the db?

I think at this point I'm hurting more than helping

sah18Author Commented:
I actually put right in the subject of my question "sql server 2000 db", to attract someone with that specific knowledge.  I certainly do appreciate *all* your efforts, though, even though it sounds like this really isn't your area of expertise.

At this point, I've made the following changes & am getting quite close (with all your help!):

Since it was causing problems with your suggestions, I changed my HomeCompliance Class TimeIn and TimeOut vars to be of type String (previously they were DateTime).

I've decided I'll use a required field validator to make sure that a value is entered in the txtTimeIn and txtTimeOut boxes, so I'm not going to worry about checking for a null/empty value at this point.

So, that leaves me with this:

        cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = DateTime.Parse(HomeCompliance.TimeIn)
        cmdHomeCompliances.Parameters.Add("@TimeOut", SqlDbType.DateTime).Value = DateTime.Parse(HomeCompliance.TimeOut)

I tried an test data entry, and entered TimeIn = 9:00 and TimeOut = 13:00

It saved back to the database without any .net or sql errors!!!

When I pulled up the record, however, the values inserted look like this:
TimeIn:  5/19/2006 9:00:00
TimeOut: 5/19/2006 13:00:00

Yes, you are correct that the DateTime field, even if only a time is entered, will always carry a date portion.  However, normally when you enter just a time, the date should default to 1900-01-01.  For some reason, the syntax above is inserting today's date in instead.  Do you know why the DateTime.Parse does this -- or how to get it not to do this?
If I can get the time to be saved with the default date of 1900-01-01, I will be able to successfully do time difference calculations later, since they will have have the same date (which is what we want).

Thanks again for spending some much time on this.  If I could increase the points past 500 I would!
sah18Author Commented:
Hmm, I'm still confused.  It turns out that DateTime.Parse is not really needed either.  I get the same results when I use the following syntax:

        cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = HomeCompliance.TimeIn
        cmdHomeCompliances.Parameters.Add("@TimeOut", SqlDbType.DateTime).Value = HomeCompliance.TimeOut

I get the correct time, but today's date instead of the default date of 1900-01-01.

Any other ideas on how to get it to use the default date instead of the current date upon insert?

I can't believe how hard it is just to get a time inserted into this database!!!!!!!
DateTime.Parse ignores unrecognized data if possible and fills in missing month, day, and year information with the "current" time.
sah18Author Commented:
For lack of a better way (since I don't understand why it's defaulting to the current date), I changed my syntax to force the date to the default date:

        cmdHomeCompliances.Parameters.Add("@TimeIn", SqlDbType.DateTime).Value = "1900-01-01 " & HomeCompliance.TimeIn
        cmdHomeCompliances.Parameters.Add("@TimeOut", SqlDbType.DateTime).Value = "1900-01-01 " & HomeCompliance.TimeOut

This is working!!

My only issue now, is that when the TimeIn / TimeOut are returned when re-displaying the times in the textboxes, they look include the date too:

ex:  txtTimeIn = 1/1/1900 4:30:00 AM  and txtTimeOut = 1/1/1900 9:33:00 PM

but I want them to look just like the format that I originally entered:  txtTimeIn = 4:30  and txtTimeOut = 22:33

How can I format these in the textboxes?

When the data is retrieved from the db, I first get the times from here (load them from the datarow into HomeCompliance again (strings again at this point):
            HomeCompliance.TimeIn = drHomeCompliance.Item("TimeIn")
            HomeCompliance.TimeOut = drHomeCompliance.Item("TimeOut")

Then, they are loaded into the textboxes here:
                Me.txtTimeIn.Text = HomeCompliance.TimeIn
                Me.txtTimeOut.Text = HomeCompliance.TimeOut

Where/how can I do the formatting, so that I'm only seeing the time in 24hr format?

you could either format the time in sql select statement coming out with something like

select  CONVERT(char(5), YourColumnName, 114)
will give you something like 14:04
this returns a string

or you can manipulate the date after you have it back in
if it comes back as a date -- take a look at DateTime.ToString() using HH:mm

didn't test it but should be something like
Me.txtTimeOut.Text = HomeCompliance.TimeOut.ToString(HH:mm)

sah18Author Commented:
I appreciate all the time you've spent on helping.  Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.