Date Problems

In my local computer, date has been set in this format - dd/MM/yyyy.
When I insert records in a MS-Access DB table using ASP.NET, then the
records get inserted in the Access DB table exactly in the same format
as what has been set in my local machine. For e.g. if today is 21st
February 2007 & the time is 10:45:32 AM, then this record gets
inserted in the Access DB table as

21/02/2007 10:45:32 AM

But when I try to insert the same record in the same Access DB table
using ASP, the record gets inserted as

02/21/2007 10:45:32 AM

i.e. the month (02) is displayed & then the day (21) is displayed &
this is causing problems for me since later in the application, I am
using the DataDiff function to find the number of days that have
elapsed since the record was inserted & todays date.

Can someone please suggest me a solution to rersolve this? It's
driving me crazy.

The column in the Access DB table where the date records get inserted
has the Date/Time data type. So Access isn't even allowing me insert
the date records in that column after I break-up the entire date into
day, month & year using DatePart. FOr e.g. the following code breaks
up todays date into day, month & year:

<%
only the date part is shown; the time part has been omitted
Dim strDay, strMonth, strYear, dtToday

strDay=DatePart("d",Now)
strMonth=DatePart("m",Now)
strYear=DatePart("yyyy",Now)

dtToday=strDay & "/" & strMonth & "/" & strYear

Set objRS=objConn.Execute("INSERT INTO ODate VALUES ('" & dtToday
& "')"
%>

The above produces the data type mismatch error.

Re: Date Problems

wrote in message
news:1173315036.914161.259250@h3g2000cwc.googlegroups.com...
> In my local computer, date has been set in this format - dd/MM/yyyy.
> When I insert records in a MS-Access DB table using ASP.NET, then the
> records get inserted in the Access DB table exactly in the same format
> as what has been set in my local machine. For e.g. if today is 21st
> February 2007 & the time is 10:45:32 AM, then this record gets
> inserted in the Access DB table as
>
> 21/02/2007 10:45:32 AM
>
> But when I try to insert the same record in the same Access DB table
> using ASP, the record gets inserted as
>
> 02/21/2007 10:45:32 AM
>
> i.e. the month (02) is displayed & then the day (21) is displayed &
> this is causing problems for me since later in the application, I am
> using the DataDiff function to find the number of days that have
> elapsed since the record was inserted & todays date.
>
> Can someone please suggest me a solution to rersolve this? It's
> driving me crazy.
>
> The column in the Access DB table where the date records get inserted
> has the Date/Time data type. So Access isn't even allowing me insert
> the date records in that column after I break-up the entire date into
> day, month & year using DatePart. FOr e.g. the following code breaks
> up todays date into day, month & year:
>
> <%
> only the date part is shown; the time part has been omitted
> Dim strDay, strMonth, strYear, dtToday
>
> strDay=DatePart("d",Now)
> strMonth=DatePart("m",Now)
> strYear=DatePart("yyyy",Now)
>
> dtToday=strDay & "/" & strMonth & "/" & strYear
>
> Set objRS=objConn.Execute("INSERT INTO ODate VALUES ('" & dtToday
> & "')"
> %>
>
> The above produces the data type mismatch error.
>

First off, lets be clear that date/time data has no text format when stored
in the DB.

When using SQL code such as above the date format is MM/DD/YYYY regardless
of your locale settings.

Re: Date Problems

rn5a@rediffmail.com wrote:
> In my local computer, date has been set in this format - dd/MM/yyyy.

Stop. That you consider this to be relevant tells me that you need to read
all three of these articles:
http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Date Problems

rn5a@rediffmail.com wrote:
> Set objRS=objConn.Execute("INSERT INTO ODate VALUES ('" & dtToday
> & "')"
> %>

Also:
Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.