Simple update date question

I'm tryting to update 2 date fields in an access database.

Thecode I'm using is:

strSQL = "UPDATE TblAbsence SET [StartDate] = #" & strStartDate & "# ,
[EndDate] = #" & strEndDate & "# WHERE (id = " & iRecordId & ");"

where strStartDate and strEndDate are passed in the format dd/mm/YYYY
This format is forced on the user, and is not able to be changed.

The database table is set up to expect the format as above

The database has enough permissions on it to allow the update (EVERYONE
has read/write permissions at the moment)

There is no error being produced, but the field is not updating. any
suggestions?

Many thanks
Kev

Re: Simple update date question

I've found the error, the update process wasn't being called correctly
so the code wasn't running.

Apologies.
Kev

Re: Simple update date question

kevinfairs@googlemail.com wrote:
> I'm tryting to update 2 date fields in an access database.
>
> Thecode I'm using is:
>
> strSQL = "UPDATE TblAbsence SET [StartDate] = #" & strStartDate & "#
> , [EndDate] = #" & strEndDate & "# WHERE (id = " & iRecordId & ");"
>
> where strStartDate and strEndDate are passed in the format dd/mm/YYYY
> This format is forced on the user, and is not able to be changed.
>
> The database table is set up to expect the format as above

No. The database engine will not recognize that format. Jet stores dates as
Doubles, with th whole number portion representing the number of days since
a seed date, and the decimal representing the time of day (.5 = noon). The
Jet engine will only recognize dates supplied as Doubles or strings using
the US format (mm/dd./yyyy) or the ISO format (yyyy-mm-dd). Since the latter
is more universal and therefore less ambiguous, developers should strive to
use that format.

Better yet is to avoid this and the other problems inherent in dynamic sql
and use parameters instead:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

It's more code, but it's safer
(http://mvp.unixwiz.net/techtips/sql-injection.html) and less prone to
error. In your case, I would do something like this:

strSQL = "UPDATE TblAbsence SET [StartDate] = ?," & _
"[EndDate] = ? WHERE (id = ?);"
dim arParms
arParms = Array(CDate(strStartDate), _
CDate(strEndDate), iRecordId )
const adExecuteNoRecords=128
set cmd=createobject("adodb.command")
with cmd
.CommandText=sSQL
.CommandType=adCmdText
Set .ActiveConnection=CN
on error resume next
.Execute(,arParms, adExecuteNoRecords)
if err<>0 then
Response.Write("Your record has been added to the database")
else
'handle the error
end if
end with
>
> The database has enough permissions on it to allow the update
> (EVERYONE has read/write permissions at the moment)
>

If this was not true, you WOULD be getting an error statement to that
effect.


> There is no error being produced, but the field is not updating. any
> suggestions?
>
No error?? Does your code have "on error resume next" masking your errors?

If not, then this is not likely to involve an error with your dates. Do a
response.write of your original strSQL variable to verify that it contains
what you expect it to contain.

--
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: Simple update date question

kevinfairs@googlemail.com wrote:
> I've found the error, the update process wasn't being called correctly
> so the code wasn't running.
>
Good. But I hope you read what I had to say anyways. It took me a while to
write it.

--
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: Simple update date question

I've had a read of that. As the stuff I'm doing is for a proof of
concept being created on a very tight timescale for a demo, it's a
little rough round the edges (I'm doing the demo so I know what data to
feed in). if I get the gig, I'll convert to your method of passing in
the date.

thanks

Bob Barrows [MVP] wrote:
> kevinfairs@googlemail.com wrote:
> > I've found the error, the update process wasn't being called correctly
> > so the code wasn't running.
> >
> Good. But I hope you read what I had to say anyways. It took me a while to
> write it.
>
> --
> 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"