insert empty date field into an access database

Problem:

in a form a user inputs a date in the danish format (ddmmyy)
After requesting the field I convert into format: #mm-dd-yy" and insert the
field together with other fields into an Access table.
But since the field isn't required it will sometimes be left empty.
After I have requested the empty field how can I convert/transform this
field into something so Access won't generate errors.
Inserting a variable containing an empty string into a date field would
probably/obviously generate errors

Re: insert empty date field into an access database

please-answer-here wrote:
> Problem:
>
> in a form a user inputs a date in the danish format (ddmmyy)
> After requesting the field I convert into format: #mm-dd-yy" and

You should use #yyyy-mm-dd# format:
http://www.aspfaq.com/show.asp?id=2040

> insert the field together with other fields into an Access table.
> But since the field isn't required it will sometimes be left empty.
> After I have requested the empty field how can I convert/transform
> this field into something so Access won't generate errors.
> Inserting a variable containing an empty string into a date field
> would probably/obviously generate errors

Are you using dynamic sql? If so, use the Null keyword. The statement you
send to Access to be executed should look like this:

UPDATE ... Set datefield=Null ....

like this:
function YourFomatFunction(pInput)
if len(pInput) = 0 then
YourFomatFunction = "Null"
else
YourFomatFunction ="#20" & right(pInput,2) & _
"-" & left(pInput,2) & mid(pInput,3,2) & "#"
end if
end function

datestring = YourFomatFunction(request.form("datefield"))


sSQL="UPDATE ... Set Datefield=" & datestring


Actually, due to the dangers of sql injection:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
I advise against using dynamic sql. You should use parameters instead,
either via saved queries:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

or via ODBC parameter markers and an explicit Command object:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

In either case, instead of passing a string containing the word "Null", you
would pass Null if the date input was empty.

Bob Barrows
--
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.

Re: insert empty date field into an access database

You can insert NULL into the date/time field if it's not required. Or just
ignore the field and let the default value be inserted.

insert into table1 (somethingElse) values ('')
insert into table1 (optionalDate,somethingElse) values (NULL,'')

As far as your date formats, I strongly suggest you read this.
http://www.aspfaq.com/show.asp?id=2260

Ray at work


"please-answer-here" wrote in message
news:eBoWrKZdFHA.2984@TK2MSFTNGP15.phx.gbl...
> Problem:
>
> in a form a user inputs a date in the danish format (ddmmyy)
> After requesting the field I convert into format: #mm-dd-yy" and insert
the
> field together with other fields into an Access table.
> But since the field isn't required it will sometimes be left empty.
> After I have requested the empty field how can I convert/transform this
> field into something so Access won't generate errors.
> Inserting a variable containing an empty string into a date field would
> probably/obviously generate errors
>
>

Re: insert empty date field into an access database

And of course be sure the database field's Required property is set to
No.

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***