asked on

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

ASKER CERTIFIED SOLUTION

Lokesh B R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ChloesDad

You might want to use IsNullorWhiteSpace rather than isnullorEmpty as this will also catch a string that is just spaces rather than empty.

ASKER

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.

ASKER

I've also tried IsNullorWhiteSpace and am still getting the error when I try to submit with the field blank.

Franck Gaspoz

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

ChloesDad

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?

ASKER

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

Lokesh B R

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

Franck Gaspoz

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.

ASKER

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

ASKER

excellent thanks