[RESOLVED] Error in Inserting the Date
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??
Code:
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
Re: Error in Inserting the Date
Don't use a textbox, use a datetime picker. It's to avoid possible errors of this sort that it was created.
Re: Error in Inserting the Date
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.
Re: Error in Inserting the Date
Quote:
Originally Posted by
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.
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.
Re: Error in Inserting the Date
Quote:
Originally Posted by
jmcilhinney
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. :D
Re: Error in Inserting the Date
Quote:
Originally Posted by
marniel647
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. :D
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.
Re: Error in Inserting the Date
Quote:
Originally Posted by
jmcilhinney
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.
Re: Error in Inserting the Date
I know, I have to use Calendar. But anyways my Question is DONE!!!!!
Code:
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