How to save date from text field in SQL Server via C # ASP.net

Question

I'm having problems saving dates in SQL DB server via C # asp.net, online

I used the asp text box and the asp cascade expander in the ASP file to get the date from the user,

<asp:TextBox runat="server" ID="txt_date"></asp:TextBox> <asp:CalendarExtender runat="server" ID="cal_date" TargetControlID="txt_date"></asp:CalendarExtender> 

code after file, suppose the connection and command are declared and initialized,

 mycom = new SqlCommand("insert into mytable(dtCol1) values('"+Convert.ToDateTime(txt_dob.Text).ToString("dd-MMM-yyyy") + "')", mycon);mycom.ExecuteNonQuery(); 

The problem is that when I select a date less than 12 of any month, it works fine, but when the date / day is more than 12 months, it gives an error,

 Exception Details: System.FormatException: String was not recognized as a valid DateTime. 

I tried all combinations .ToString ("dd-MMM-yyyy")

Please help in advance thanks

Answer 1

try it

 CultureInfo provider = CultureInfo.InvariantCulture; System.Globalization.DateTimeStyles style = DateTimeStyles.None; DateTime dt; DateTime.TryParseExact(txt_dob.Text, "md-yyyy", provider, style, out dt); mycom = new SqlCommand("insert into mytable(dtCol1) values(@datevalue)", mycon); cmd.Parameters.Add("@datevalue",SqlDbType.DateTime).Value =dt; mycom.ExecuteNonQuery(); 

Answer 2

try it

 mycom = new SqlCommand("insert into mytable(dtCol1) values(@value1)"); mycom.Parameters.AddWithValue("@value1",Convert.ToDateTime(txt_dob.Text)); mycom.ExecuteNonQuery(); 

Answer 3

The problem seems to come from here: Convert.ToDateTime(txt_dob.Text) . This type of conversion is not good because:

• It ignores the format used by the control . Convert.ToDateTime(...) expects the string to be in a specific format (s) in order to parse it correctly. It cannot handle any custom formats that txt_dob can use.

• He does not know about culture-specific formatting. Internally, Convert.ToDateTime(...) is likely to stick to CultureInfo.CurrentCulture , which is said to be unable to parse the date. In addition, some .NET front-end controls recognize and use the client culture passed by the browser (for web applications), and most likely this culture will be different from the server culture. CultureInfo.CurrentCulture will represent a server culture and formatting inconsistencies may occur.

If you know the txt_dob.Text format, you must explicitly parse it. In the example below, I assumed that the format is "MM/dd/yyyy" :

 String dateFormat = "MM/dd/yyyy";//The format that the txt_dob control uses DateTime parsedDate = DateTime.ParseExact( txt_dob.Text, dateFormat, CultureInfo.InvariantCulture); 

You can also check this related section for more information on a similar case, for example yours

Answer 4

TL; DR; You are using the wrong DateTime format.

There are many problems that will blow up the Death Stars, from left to right and in the center.

First, you create an SQL query on the fly - this is probably the largest no-no in the last 10 years, and everyone stopped doing it. In other words, please, please do not start doing (very outdated) ADO.NET programming to transfer data from a website to a database. Modern replacements like Entity Framework, NHibernate, or even Linq2Sql if you are desperate.

Good - that says, let's try to answer your question and we won’t.

The reason is that you are passing values ​​in the wrong format. First you make the dates. Your sql server probably wants it to be MONTHS first. Because it probably was set to style 121 (<is a real set of SQL Server compatibility options.)

But do not try to fight and guess.

Let's use a more universal and initial line format: yyyy-mm-dd hh: mm: ss.ms

eg.

 SELECT CAST('2013-02-08 09:53:56.223' as DateTime) 

And you can see it in action on SQLFIDDLE .

fooobar.com/questions/325842 / ... , which explains what the default DateTime format is, etc.

Answer 5

Use this:

 mycom = new SqlCommand("insert into mytable (dtCol1) values ('" + Convert.ToDateTime(txt).ToString("MMM-dd-yyyy") + "')", mycon); 

thanks

Answer 6

User DateTime.ParseExact () instead of the Convert.ToDateTime () method.

 dateString = "12-31-2012"; format = "MM-dd-yyyy"; try { DateTime result = DateTime.ParseExact(dateString, format, CultureInfo.CurrentCulture); } catch (FormatException) { } 

Answer 7

[Date_Of_Birth]='" + Convert.ToDateTime(TxtDate_Of_Birth.Text).ToString("MM-dd-yyyy") + "'

It helps the cause of the error that sqlserver accepts datetime in the format MM/dd/yyyy , but not in dd/MM/yyyy .