#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    40
    Rep Power
    11

    Yet another Access / SQL-Server question


    Hello,

    as you can see, I am not good at this, and I cannot get my hands on any resources to solve the problem myself, so I appreciate another answer:

    I migrated an Access DB to SQL Server (or rather somebody did for me). Now I have the problem that when I do INSERT commands from my ASP-pages:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'Table_Type_ID', table 'DB_Name.dbo.Table_Type'; column does not allow nulls. INSERT fails.

    The column in question is a primary key in Access, and it is not imported correctly in SQL Server.

    Can you help me out? Thank you!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    if the column in question was a primary key in access, it most likely is a primary key in sql server too

    for a table called dbo.Table_Type, a column called Table_Type_ID does indeed sound like a typical primary key

    primary keys do not allow nulls

    from the error message, i would say it's your asp script that has the problem, not your database

    try posting in the asp forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    40
    Rep Power
    11
    Thank you.
    Before I try the ASp forum, I will give you another clarification:
    The value for the primary key is entered automatically by Access, but apparently SQL Server does nos do it. Of course, the SQL INSERT statement that I use does not contain anything concerning the primary key because it is entered by Access. Do I need to change that, and if how? Thank you for your help.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    aha!

    yes, the primary key must have been an autonumber in access, and should be an identity column in sql server

    sorry, you were right, it was a database question

    show the sql server table layout, and i'll see if i can whip you up some sql to change it

    also, how many rows are in it at the moment?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    40
    Rep Power
    11
    Hi,

    I will talk to the person that did the conversion and get back to you.
    Thanks for your help already.

    Helge
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    40
    Rep Power
    11
    Rudy,

    in a joined effort we figured it out! Thanks a lot for your help.

    Helge.

IMN logo majestic logo threadwatch logo seochat tools logo