Dec 13th, 2011, 11:18 PM
Date Format Problem
Question: Vb.net 2010 always refer the date format from computer regional date format setting?
example: inside regional date format setting i set "Short date format dd/MMM/yyyy" and inside vb.net program i set "strSQL = strSQL & ",'" & CDate(Date.Now.ToString("dd/MM/yyyy")) & "'" ----> working fine. if user changed the regional date format setting to other format like "mm/dd/yy" facing problem during saving record into sql table. error date conversion. It's possible to avoid using computer regional date format from vb.net? or have any other way...
Dec 13th, 2011, 11:45 PM
Re: Date Format Problem
What would be even better would be to treat the data as a Date, set the column type in the database as Date (or equivalent) and pass a Date as a Parameter to the query.
Dates are not strings!
Dec 14th, 2011, 08:39 AM
Re: Date Format Problem
Welcome to VBForums
To expand on what has been said already... VB (of any variety) does not refer to any date format at all unless you tell it to, which you are doing at least three times in your code - two of which you ask for the Regional Settings format to be used.
Like many computer systems (including most databases/programming languages/etc), VB does not store dates the way we see them, it stores them as a particular style of number (eg: 34526.35) to ensure accuracy, and only converts that to a formatted string if needed - such as when you show it in a textbox.
Converting between the Date and String data types is not a safe thing to do, particularly when it is going from a String to a Date.
In order to convert from a Date to a String, using .ToString with a format string is reliable - because you know that it will always use the specified format.
It is generally best to avoid CDate because it is often unsafe (because it uses Regional Settings rather than a specified format), and is very unsafe the way you have it (because you have created a String in a particular format, which will usually not be the same as Regional Settings).
Just before the CDate you have the & character to append the value to a String... which again does a conversion from Date to a String, this time using Regional Settings. If you remove the CDate you would instead be using the value you originally got from .ToString , which is a String therefore does not need to be converted, so would always give the same format no matter what Regional Settings are.
However, even if you remove those two unsafe conversions there is an implied 4th conversion going on - inside a database system, converting from String to Date. As far as most database systems are concerned, the format "dd/MM/yyyy" is always wrong (no matter what Regional Settings or database settings are used), so that part will usually create problems too.
If you use Parameters as suggested above, you completely avoid Strings and keep everything as Dates all the way thru the process, and thus completely avoid all formatting issues. For info, see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)
(2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015) . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums
Classic VB FAQs (updated Oct 2010) ...Database Development FAQs/Tutorials (updated May 2011)
(includes fixing common VB errors) .......... (includes fixing common DB related errors, and [Classic VB] ADO tutorial /further steps, and [VB.Net] ADO.Net Tutorial).
Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker
Connection strings .•. MDAC/Jet/ACE downloads .•. SQL Server downloads .•. MZTools (free upgrade for the VB6/VBA Editor)
Click Here to Expand Forum to Full Width
Survey posted by VBForums.