Simple 'NULL' / Databiding prob got me stumped..

Ok, I'm new at this and learning as I go, but under extreme pressure
from work.. So many bugs and little tricky obsticals have got me very
far behind, and this is the latest..

I made a simple databinding form, but not using the wizard, because it
doesn't use the SQL driver.

I'm keeping it simple right now with a very normal table with a
primary key 'SIN' (social insurance number). Well I'm following
along in the book to do the whole goto next record, delete etc.. All
works.. But the problem is ADDING a record. Whenever I try, it simply
says "column 'SIN' does not allow nulls". Well no $#1T. I have a
value in the field, but it simply erases it right away and gives me
that message. I've been searching for DAYS on this, and nobody has
seemed to have it figured out in this scenario. It seems to me like
an extremly common issue..So why all the headache? None of the books
examples seem to deal with this....

btw, I've tried:
setting a default value on the SQL table itself.
changing the xml schema to not allow nulls.
changing the xml schema to assign a default value.

Couple of tips and solutions:

1. Avoid user-entered primary keys like the plague. Think of primary keys as
part of the plumbing code. Surrogate keys (ones created by the system) are
far superior in handling structural changes.

2. If you are dead set on using integer primary keys and you were using SQL
Server or Oracle then another solution is to use a server side stored
procedure to do inserts. This procedure would essentially add the value and
return the newly created key.

3. If you aren't using SQL Server or Oracle and are still dead set on using
primary keys, then another solution is to create a "primary key" table. This
table has a single row and field which stores the next primary key value
available. When you want to do an insert, you have to transactionally read
and lock the "primary key" table, increment the existing value, unlock the
table and then use originally read value for the primary key for your new
record.

4. Use GUIDS. GUIDS can be created uniquely on the client so they require no
inital server interaction.


HTH,



Thomas


"James R" <radmelick@gmail.com> wrote in message
news:1dfc6403.0409091314.6f2dfe64@posting.google.com...
> Ok, I'm new at this and learning as I go, but under extreme pressure
> from work.. So many bugs and little tricky obsticals have got me very
> far behind, and this is the latest..
>
> I made a simple databinding form, but not using the wizard, because it
> doesn't use the SQL driver.
>
> I'm keeping it simple right now with a very normal table with a
> primary key 'SIN' (social insurance number). Well I'm following
> along in the book to do the whole goto next record, delete etc.. All
> works.. But the problem is ADDING a record. Whenever I try, it simply
> says "column 'SIN' does not allow nulls". Well no $#1T. I have a
> value in the field, but it simply erases it right away and gives me
> that message. I've been searching for DAYS on this, and nobody has
> seemed to have it figured out in this scenario. It seems to me like
> an extremly common issue..So why all the headache? None of the books
> examples seem to deal with this....
>
> btw, I've tried:
> setting a default value on the SQL table itself.
> changing the xml schema to not allow nulls.
> changing the xml schema to assign a default value.