4

I am having trouble while storing date in sql server DB through C# asp.net, online

I have used asp Text box and asp calender extender in ASP file to get date from user,

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

code behind file is, assume 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();

Problem is, when I select date less than 12 of any month it works perfect, but when date/day is greater than 12 of any month it gives error,

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

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

Please Help thanks in advance

Nils
  • 45
  • 1
  • 3
  • 9
  • what format are you using in writting datetime value in `txt_dob` – Rajeev Kumar May 13 '13 at 10:34
  • If you have problem for days over 12th it means you have wrong date format and storing month as a day and the other way around. Check what exactly format you have to send to SQL. Preferably use parameters not glued quries - it's bad habit you risk SQL injection. –  May 13 '13 at 10:37
  • thanks @Rajeev Kumar - it is taking default time format of calender extender which is , mm-dd-yyyy – Nils May 13 '13 at 10:37
  • 1
    I can't believe anyone hasn't picked up on the fact that he/she is writing Sql on the fly using a friggin (laser) SqlCommand!! <-- Which is BAD(tm). – Pure.Krome May 13 '13 at 10:46

7 Answers7

3

try this

mycom = new SqlCommand("insert into mytable(dtCol1) values(@value1)");
mycom.Parameters.AddWithValue("@value1",Convert.ToDateTime(txt_dob.Text));
mycom.ExecuteNonQuery();
Feras Salim
  • 438
  • 7
  • 33
2

Try this

CultureInfo provider = CultureInfo.InvariantCulture;
System.Globalization.DateTimeStyles style = DateTimeStyles.None;
DateTime dt;
DateTime.TryParseExact(txt_dob.Text, "m-d-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();
Rajeev Kumar
  • 4,901
  • 8
  • 48
  • 83
2

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

  • It disregards the format that the control uses. Convert.ToDateTime(...) expects the string to be in a certain format(s) in order to parse it correctly. This cannot handle any custom formats that the txt_dob could use.

  • It is ignorant of culture-specific formatting. Internally, Convert.ToDateTime(...) will probably stick to CultureInfo.CurrentCulture, which is not said to be capable of parsing the date. Also, some front-end controls in .NET recognize and use the client culture passed by the browser (for web apps), and it is likely for that culture to be different than the server culture. CultureInfo.CurrentCulture will represent the server-side culture and formatting discrepancies are possible to occur

If you know the format of the txt_dob.Text you have to explicitly parse it. In the example below I have assumed 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 topic with some additional information on a similar case like yours

Community
  • 1
  • 1
Ivaylo Slavov
  • 8,839
  • 12
  • 65
  • 108
1

TLDR; You're using the wrong DateTime format.

There's plenty of issues here that will blow up Death Stars, left right and center.

Firstly, you're creating the sql query on the fly - that's probably the biggest no-no in the last 10 years and everyone has stopped doing that. In other words, please please please don't start doing (the very out of date) ADO.NET programming to pass data from the website to the database. Modern replacements like Entity Framework, NHibernate or even Linq2Sql if you're desperate.

Ok - that said, lets try and answer your question none-the-less.

The reason is because u're passing in the values in the wrong format. You're doing dates first. Your sql server is probably wanting it to be MONTHS first. Because it's probably been setup to be style 121 (<-- that's a real technical sql server setting thing for Compatibility crap..)

But don't try and fight and guess.

Lets use a more universal and start string format: yyyy-mm-dd hh:mm:ss.ms

eg.

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

And you can see this in action over at SQLFIDDLE.

This is a really good StackOverflow question that explains what the default DateTime format is, etc.

Community
  • 1
  • 1
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
0

Use this:

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

Thanks

Altaf Sami
  • 846
  • 5
  • 10
  • 21
0

User DateTime.ParseExact() in place of Convert.ToDateTime() method.

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

}
Sain Pradeep
  • 3,119
  • 1
  • 22
  • 31
0

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

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

Aage
  • 5,932
  • 2
  • 32
  • 57
Hazem Elkady
  • 1