SQL Server Performance

Cannot insert the value NULL into column

Discussion in 'SQL Server DTS-Related Questions' started by JL_Forum, Jul 23, 2004.

  1. JL_Forum New Member

    Hi guys, I did a DTS conversion of my Access 2K database into SQL Server. All the data went through fine as far as I can tell. I then created an ODBC connection to it and am trying to use my old Access functions. I took my old Access database, replaced all the old tables with linked ODBC tables with the same name, etc. My forms open up correctly, I can browse through them properly, etc. However, if I try to enter a record, it states that I "cannot insert the value NULL into column". This occurs if I try to create a new record. Please note that this record used Autonumber from Access 2K and was the primary key. How do I get the SQLServer to recognize it? The autonumber usually displays on the form I am using once I begin to enter information, but there is no number appearing. When I try to save the record, I get that message. Please help!!! Thanks.
    Sinceerly,
    JL
  2. satya Moderator

    If the column in question was a primary key in access, it most likely is a primary key in sql server too.

    If the column does not allow nulls, then any data you import into it MUST have data in that field. If your source data deosn't have any, then you need to write your code such that you insert a value in.

    Is there a default value onthe field? IF so it should insert. If the field should allow nulls then change the field constraint to effect.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. JL_Forum New Member

    Hi, it is a primary key. There is no default value either. As a result, the column does not allow nulls, and all imported fields that cannot be null do have data in them. I think the issue results in Access writing values in immediately. My form is composed of subforms on different "tabs". Some of these subforms write to the same table (but are separate for user interface reasons). When I try and "switch" tabs, I get the error above. It is because the autonumbering primary key field hasn't written a unique value yet. If I go straight to the table and try to enter data, I noticed that the autonumber field on the primary key has changed to Long Integer. It is still the primary key on the linked table but it isn't writing anything. Changing the primary key field Identity to 'yes' of the SQL Server table that it is linked to does not change anything. I still get the same error. What am I doing wrong? Do I need to have SQL Server create it as a key as well? I thought that Access would take care of this, being the frontend. Help! =)
    Sincerely,
    Jon
  4. JL_Forum New Member

    Hi, it is a primary key. There is no default value either. As a result, the column does not allow nulls, and all imported fields that cannot be null do have data in them. I think the issue results in Access writing values in immediately. My form is composed of subforms on different "tabs". Some of these subforms write to the same table (but are separate for user interface reasons). When I try and "switch" tabs, I get the error above. It is because the autonumbering primary key field hasn't written a unique value yet. If I go straight to the table and try to enter data, I noticed that the autonumber field on the primary key has changed to Long Integer. It is still the primary key on the linked table but it isn't writing anything. Changing the primary key field Identity to 'yes' of the SQL Server table that it is linked to does not change anything. I still get the same error. What am I doing wrong? Do I need to have SQL Server create it as a key as well? I thought that Access would take care of this, being the frontend. Help! =)
    Sincerely,
    Jon
  5. JL_Forum New Member

    Umm... I must be missing something... my previous form has a subform that contains values which revolved around a specific table. When I try to enter the person into here, I get the error above. If I create a new form using this table only, I do not have this issue. It works perfectly. Any idea if it has something to do with the subform? Thanks.
    Sincerely,
    Jon

Share This Page