String was not recognized as a valid DateTime.

The below code is supposed to facilitate the txtDateCompleted.text field being blank so NUlls can be inserted into the DATECOMPLETED field. However when it is blank I get the error

String was not recognized as a valid DateTime.

so it does not work. What am I doing wrong!

            If txtDateCompleted.Text Is Nothing Then

                cmd.Parameters.Add("@DATECOMPLETED",  SqlDbType.Date).Value = DBNull.Value

            Else

                cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.Date).Value = txtDateCompleted.Text.Trim()

            End If

Open in new window

EdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
Hi,

Check this

If String.IsNullOrEmpty(txtDateCompleted.Text) Then
	cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.[Date]).Value = DBNull.Value
Else
	Dim dateValue As DateTime
	If DateTime.TryParse(txtDateCompleted.Text, dateValue) Then
		cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.[Date]).Value = dateValue.[Date]
	Else
		cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.[Date]).Value = DBNull.Value
		Response.Write("Not A Proper Date Format..!!!")
	End If
End If

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChloesDadCommented:
You might want to use IsNullorWhiteSpace rather than isnullorEmpty as this will also catch a string that is just spaces rather than empty.
0
EdAuthor Commented:
Hi, Im still getting

String was not recognized as a valid DateTime.

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.FormatException: String was not recognized as a valid DateTime.

Source Error:


Line 69:                 con.Open()
Line 70:
Line 71:                 cmd.ExecuteNonQuery()



whenever I try to submit with the field blank.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

EdAuthor Commented:
I've also tried  IsNullorWhiteSpace   and am still getting the error when I try to submit with the field blank.
0
Franck GaspozSoftware Architect, Technical ExpertCommented:
Try to use System.Data.SqlTypes.SqlDateTime.Null instead of DBNull.Value and if needed System.Data.SqlDbType.DateTime instead of System.Data.SqlDbType.Date
0
ChloesDadCommented:
Put a break point on the if datetime.tryparse.... line and tell us what the value of the .text property is. Is that the line that causes the error?
0
EdAuthor Commented:
The textfield is  txtdatecompleted

I get  the error

String was not recognized as a valid DateTime.

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.FormatException: String was not recognized as a valid DateTime.

Source Error:


Line 71:             con.Open()
Line 72:
Line 73:             cmd.ExecuteNonQuery()


Every time I submit and the field txtdatecompleted.text is left blank.

If I put a date in txtdatecompleted in this format dd/mm/yyyy it all works fine.


     If String.IsNullOrEmpty(txtDateCompleted.Text) Then
                cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.DateTime).Value = DBNull.Value
            Else
                Dim dateValue As DateTime
                If DateTime.TryParse(txtDateCompleted.Text, dateValue) Then
                    cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.DateTime).Value = dateValue.[Date]
                Else
                    cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.DateTime).Value = txtDateCompleted.Text
                    Response.Write("Not A Proper Date Format..!!!")
                End If

            End If

Open in new window

0
Lokesh B RDeveloperCommented:
Hi,

If the date is not proper then add DateTime.Now/DbNull.Value as default value.

If String.IsNullOrEmpty(txtDateCompleted.Text) Then
                cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.DateTime).Value = DBNull.Value
            Else
                Dim dateValue As DateTime
                If DateTime.TryParse(txtDateCompleted.Text, dateValue) Then
                    cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.DateTime).Value = dateValue.[Date]
                Else
                    cmd.Parameters.Add("@DATECOMPLETED", SqlDbType.DateTime).Value = DateTime.Now
                    Response.Write("Not A Proper Date Format..!!!")
                End If

            End If

Open in new window

0
Franck GaspozSoftware Architect, Technical ExpertCommented:
Could you post the exact sql query text, because if it contains a sql date to string conversion, a null datetime parameter might be not valid.
0
EdAuthor Commented:
create PROCEDURE [dbo].[INSERT_IQATest2]

@IVRECORDID int output,
		 @TRAINEEID varchar(16),
           @POT int,
           @QUALSCHEMEID int,
           @IVTYPEID int,
           @STAFFID int,
           @DATEPLANNED datetime,
           @DATEACTUAL datetime,
           @DATECOMPLETED datetime,
           @IVOUTCOMEID int,
           @IVSITEID int,
           @LANGUAGEUSED int,
           @ASSESSORID varchar(16)
AS

Begin
INSERT INTO I_TRAINEE_IV_RECORDS
           ([TRAINEEID]
           ,[POT]
           ,[QUALSCHEMEID]
           ,[IVTYPEID]
           ,[STAFFID]
           ,[DATEPLANNED]
           ,[DATEACTUAL]
           ,[DATECOMPLETED]
           ,[IVOUTCOMEID]
           ,[IVSITEID]
           ,[LANGUAGEUSED]
           ,[ASSESSORID])
     VALUES
           (@TRAINEEID
           ,@POT
           ,@QUALSCHEMEID
           ,@IVTYPEID
           ,@STAFFID
           ,@DATEPLANNED
           ,@DATEACTUAL
           ,@DATECOMPLETED
           ,@IVOUTCOMEID
           ,@IVSITEID
           ,@LANGUAGEUSED
           ,@ASSESSORID)
           
SET @IVRECORDID=SCOPE_IDENTITY()

     RETURN  @IVRECORDID
     
     END 
     

Open in new window

0
EdAuthor Commented:
excellent thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

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.