Re: Dates, Java, and Access Databases


"dave" <b1codemaster@xxxxxxxxx> wrote in message
news:1144339297.044527.235960@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a form created via NetBeans. I have an Access Database. I am
correctly connected and can write to the database. However, I have
three Date fields that I need some help with.

First, I need to have formatted input on the Form Fields. Then I need
to save the date to the database in the correct date/time (it's set up
that way) format. My problem other than the formatted input is that
there are times that one or more of the date Fields will be without
data. I can't seem to get a blank field to write to the database
properly.

I get a data mismatch error whenever I try to send the blank field to
the DB.

Any help or suggestions would be appreciated.

I have very little experience with Access but it wouldn't surprise me at all
if it doesn't like a blank value for a date field: since blanks are not
representations of valid dates, it only makes sense that they should be
rejected. That's certainly the approach that DB2 and MySQL use.

I'm not sure if Access supports the NOT NULL WITH DEFAULT clause in its SQL
but, if it does, that is one answer to your problem: if the new row doesn't
have a date associated with it, make sure that you don't try to insert any
value in the date column and then let the "NOT NULL WITH DEFAULT" clause in
the column definition create a default date value in the new row for you.
You'll need logic like this:

if (date-from-form-is-blank) {
insert into mytable (id, name) values(id-value-from-form,
name-value-from-form);
} else {
insert into mytable (id, name, hiredate) values (id-value-from-form,
name-value-from-form, hiredate-value-from-form);
}

Another approach you could take would be turn the blank into a valid date
within your code. Then you wouldn't need the NOT NULL WITH DEFAULT clause.
Something like this:

if (date-from-form-is-blank) {
dateVariable = 2000-01-01; //some arbitrary date
} else {
dateVariable = date-from-form; //the value for the date from the form
}

insert into mytable (id, name, hiredate) values (id-value-from-form,
name-value-from-form, dateVariable);

As for your need to have formatted input on the form fields, there are
multiple ways to accomplish that. For example, with respect to dates, you
could capture the year, month and day from separate form fields and edit
each one separately, then combine the parts of the date to make sure the
combination is valid. After all, the months are not uniform in length so you
need to make sure that a date like February 30 is never accepted regardless
of what the year is and February 29 should only be accepted if it is a leap
year.

Or you could create a single field and then apply various edits to make sure
that the values in each part of the date are valid and the combination of
the date parts is also valid, for the same reasons I mentioned in the
previous paragraph. You will probably also have to do something about the
separators between the date parts, e.g. 2006-04-05 or 04/05/2006: probably,
you will just ignore them but you may want to force the user to enter only
the appropriate separators and not mix them. For example, you may not want
to tolerate 2006-04/05 since it mixes the separators.

The exact code for either approach will vary with how detailed you want your
error messages to be. If every mistake in a date is going to produce the
same message, something like "Your date is wrong.", then your error checking
will be coded differently than if you want to produce distinct error
messages for each possible condition, such as "The year portion of the date
contains a non-digit." or "The day of the month is greater than 28 for a
February in a non-leap year."

In any case, you have to give the user a field (or fields) for each date,
let them enter what they like, then verify that they have given valid
information for each field and that the date as a whole makes sense; if all
of the dates and the non-dates on the input form make sense, you can pass
the date to your Insert statement. If any of the data doesn't pass the edits
you should produce an error message and let them try fixing the data until
it no longer produces an error message; then it can be inserted to the
database.

If you need examples of specific code and can specify what approach you are
taking and whether this is a servlet, applet, or application, I may be able
to help you.

--
Rhino



.

Relevant Pages

  • Database editor error message
    ... Source: Microsoft JET Database Engine ... One or more form fields were empty. ... >Database Results Wizard Error ...
    (microsoft.public.frontpage.client)
  • Re: Data Base Interface Wizard update problem
    ... It seems the update won't update empty database ... Opening the database in Access(double click the database in the fpdb folder. ... > When i'm trying to update my database i get the following error message: ... > all form fields that are used in the query. ...
    (microsoft.public.frontpage.client)
  • Re: DBD::Informix 2003.04 AIX4.3.3 SELECTIVE TEST ERRORS
    ... one which you could fix by removing the database). ... The bug report is below. ... > Informix Server Entries in sqlhosts file ... > # Error Message: ...
    (perl.dbi.users)
  • Re: Module Not Found
    ... Close the database. ... Still in the code window, choose Compile from the Debug menu. ... The error message could be related to something else that is going on, ... VB and press the cancel button I receive the error message ...
    (microsoft.public.access.formscoding)
  • Re: Cannot open my own Databases
    ... Windows Administrator and loads each of these service packs. ... If I have guessed correctly about the error message you see, ... See http://www.QBuilt.com for all your database needs. ... it's possible that the correct workgroup information file ...
    (microsoft.public.access.security)