date insert problem

hi...

my form has a text box which displays system date.

i am inserting date into MS SQL Server from this date textbox.

but it displays me error..

String was not recognized as a valid DateTime.

Line 154: myCommand1.ExecuteNonQuery()

i have written code as

myCommand1.Parameters.Add(New SqlParameter("@.date", SqlDbType.DateTime, 8))

myCommand1.Parameters("@.date").Value = FormatDateTime(datetxt.Text, DateFormat.GeneralDate)

and also tried to change date format with many other ways.

how should i solve this problem?

i also want to take time form a user with the help of web form and want to store it in other field called 'timein' and 'timeout'....

FormatDateTime returns a string. Use DateTime.Parse instead to create a valid datetime, and assign the datetime object parsed. Alternatively, you can use Convert.ToDateTime, but you get an exception for invalid dates. Tie your textbox in to a CompareValidator to ensure valid dates from the browser. I recommend turning off client validation for date validation as it does not validate medium date (e.g. 22-Apr-2007) correctly|||

Heh, who the hell ever types 22-Apr-2007? Personally, I wouldn't consider it valid input to begin with.

Since you have already stated that your sqldbtype is a datetime, just shove the string into the parameter.

EX:

cmd.parameter("@.MyDateTime).value=MyDateString

That of course assumes that you have already validated it as a valid date/time string format. Just make sure that you validate it both client and server side. Too many people I know drop the validators down on the client, then forget to wrap their "save" in a If page.IsValid(), and it works... Until a hacker comes along and posts some invalid data to the server.

Oh, and I would drop off the size parameter on the sqlparameter. Not sure if 8 is even the correct size for a datetime, but it's ignored anyway since datetime is a fixed size.

|||

The reason I suggest the use of medium date format is this. I've workd for a number of world-wide companies over many years, often on large projects. Hundreds of thousands of pounds have been wasted by developers and databases engineers, simply because of a US/UK date confusion, or forgetting to set the date locale to UK, then, some months later, finding a database full of data that is invalid, then having to fix it, and having systems off-line.

Medium date also still works in the local language, if you set the locale of a web site (e.g. Norway).

Developers should also be listening to business, who increasing, and in my view, rightly, want medium format. The whole of the rest of the world does not live in the US, that is why our web sites should be locale aware

Software development is also a team effort. It isn't down to the developer alone to ensure a web-site isn't hacked. Architects, Managers and Testers are all there to make it happen (safely).

As always, these are my opinions and suggestions, soI don't expect everyone to like or agree with them.

Happy coding all...

|||

That's all well and good, and I agree that applications should be built with globalization in mind, but that really has nothing to do with medium dates. We don't use them in the US, and they aren't used anywhere else in the world except for geeky tech documents. If you want to use a truly international standard, then use the ISO format (YYYY-MM-DD). It's the same format in every culture.

As for having incorrect dates in your database, that's why you should always use the datetime datatype. The value is the value no matter what culture you are in, infact I normally store all date/times in a database based on UTC. That way no matter where you are in the world, I can tell you what time and date a specific event occurred localized in your specific date format, and give you the time relative to your timezone.

Now, it may be in your company, that they have decided that medium format is best, but I can say that is an oddity, and not a rule. There is a standard date format, it's the ISO format that was approved by the international standards organization, and when people need a format that they don't want translated to their native culture's format, that is the one that should be used. After all, 01-Apr-2006 isn't correct for any other place but the US. They don't have "April" let alone "Apr" in other countries.

|||

More business documents in the UK are using Medium and even Long Dates than previously. You don't see YYYY-MM-DD used in the UK as most people would find it geeky (whatever that means). In my software I generally let the user override their locale anyway, and set a date format for the web site based on ISO8601, ISO, UK/US, etc.

Medium and Long dates do correctly translate into the local language. E.g. I just tried today's (long format - since Med format displays the same result) date on a simple web page, which returned 12-febrero-2006 when I changed the culture from UK English to international Spanish (es-ES).

BTW., It wasn't my database or even my company that had the incorrect dates. I spent 15 years contracting around the UK, working for various major companies. What I observed (and sometimes got asked for advice on) were issues where UK formatted dates (parsed as text using dd/mm/yy) were stored in a database that was assuming US format since the default language had never been changed. Eventually the systems broke, which gave rise to many issues and fixing. What I advised is this. If they had used medium format from the start, the issue would not have arose, because the text would have been corretcly converted into the underlying datetime type.

I am sure we can disagree long into the night about date formats. However, it's a minor issue, and you are correct to raise the point that storing dates in the underlying format is the correct way to go.