[RESOLVED] Error in Inserting the Date

[sonia.sardana]

I want to insert the Date into SQL that is entered in textbox, When i enter 18/07/2011 it is successfully converted to DateTime object ( WORKING CODE - BELOW) , but when I enter 18/7/2011 it is not converted to DateTime object. What should I do?? if the user enters the date in the format 18/7/2011??

Imports System.Globalization
Imports System.Data.SqlClient
Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page
    Dim con As New SqlConnection("Data source = SONIA-PC;Initial Catalog=master;Integrated Security=true")
    Dim cmd As New SqlCommand
    Dim ds As DataSet
    Dim da As SqlDataAdapter
    Dim query As String


    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            query = "Insert into Practise2 values(@DOB)"
            cmd = New SqlCommand(query, con)

            Dim dt As DateTime
            If DateTime.TryParseExact(txtDateofBirth.Text, "dd/MM/yyyy", CultureInfo.InstalledUICulture, DateTimeStyles.None, dt) Then
                MsgBox(dt.ToString)
                MsgBox(dt.ToString(" yyyy-MM-dd"))
                cmd.Parameters.AddWithValue("@DOB", dt.ToString((" yyyy-MM-dd")))
            End If
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try

    End Sub

    
End Class

[dunfiddlin]

Don't use a textbox, use a datetime picker. It's to avoid possible errors of this sort that it was created.

[marniel647]

Yes i agree with dun. It is better to use the DT picker. But Dt picker returns long date. so f you want to get only the yyyy-mm-dd you can use the .format method in your string.

[jmcilhinney]

Yes i agree with dun. It is better to use the DT picker. But Dt picker returns long date. so f you want to get only the yyyy-mm-dd you can use the .format method in your string.

That's not correct. The DateTimePicker can display any format you want but the format that it displays is irrelevant when it comes to using the data. The most important property of the DateTimePicker is Value, which is type DateTime. You should almost never be using the Text property. Because the Value property is a DateTime, format is completely irrelevant. A DateTime is a binary representation of a date and time, not text. Format is only relevant when converting to or from text. If there's no text then there's no format.

In the original code, the String from the TextBox is being converted to a DateTime first and then that is being converted back to a String. That's wring on two counts. You should be using a DateTimePicker as suggested and getting the DateTimne from it and inserting that. DO NOT convert anything that is not text into text.

[marniel647]

That's not correct. The DateTimePicker can display any format you want but the format that it displays is irrelevant when it comes to using the data. The most important property of the DateTimePicker is Value, which is type DateTime. You should almost never be using the Text property. Because the Value property is a DateTime, format is completely irrelevant. A DateTime is a binary representation of a date and time, not text. Format is only relevant when converting to or from text. If there's no text then there's no format.

In the original code, the String from the TextBox is being converted to a DateTime first and then that is being converted back to a String. That's wring on two counts. You should be using a DateTimePicker as suggested and getting the DateTimne from it and inserting that. DO NOT convert anything that is not text into text.

Thanks for the clarification Jm. I always get a long date value when i'm using date time picker so i think it will always return long date. But thanks again for that information.

[jmcilhinney]

Thanks for the clarification Jm. I always get a long date value when i'm using date time picker so i think it will always return long date. But thanks again for that information.

Then that would suggest that you are using the Text property without having changed the Format and CustomFormat properties from their default values. The Format and CustomFormat properties can be used to display the date and/or time in pretty much any format you want but, when using the data in code, you should pretty much exclusively be using the Value property.

[marniel647]

Then that would suggest that you are using the Text property without having changed the Format and CustomFormat properties from their default values. The Format and CustomFormat properties can be used to display the date and/or time in pretty much any format you want but, when using the data in code, you should pretty much exclusively be using the Value property.

yes i'm always using the text property of the DTPicker rather then value property. So i will now use the value property of the DTPicker and also set the format properties that i want to display thanks again.

[sonia.sardana]

I know, I have to use Calendar. But anyways my Question is DONE!!!!!

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            query = "Insert into Practise2 values(@DOB)"
            cmd = New SqlCommand(query, con)


            Dim formats As String() = {"dd/MM/yyyy", "d/M/yyyy"}


            Dim dt As DateTime
            If DateTime.TryParseExact(txtDateofBirth.Text, formats, CultureInfo.InstalledUICulture, DateTimeStyles.None, dt) Then
                MsgBox(dt.ToString)
                MsgBox(dt.ToString(" yyyy-MM-dd"))
                cmd.Parameters.AddWithValue("@DOB", dt.ToString((" yyyy-MM-dd")))
            End If
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub
End Class