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

    Join Date
    Sep 2003
    Posts
    109
    Rep Power
    12

    Error '80004005'


    I have an ASP page that is used heavily for a few months each year then sits unused. It's that time of year again and suddenly the page that has worked for years is throwing an error.

    INSERT INTO bills([SessionYear], [Purpose], Chamber, [Section], [Article], RepVol, Supp, [By], updated, ClubName, ClubName2, PrimName, SecName, Title, BillCode, Comm, Page1, Email, [user], [pw], [Public], [ZAP]) VALUES('2011', 'testing', 'Senate Bill', '2', '2', '222', '222', 'enacting', '2/7/2011 1:02:52 PM', 'HARFORD', '', 'Barry Martin', '', 'testing', '', 'Health and Welfare', 'testing', email, email, 'testing', 'No', 'No')

    Microsoft JET Database Engine error '80004005'

    Index or primary key cannot contain a Null value.

    /billwiz/BillForm Create.asp, line 127


    My code hasn't changed, has some standard somewhere changed?

    Code:
    set commUpdateBill = Server.CreateObject("ADODB.Command") 
    commUpdateBill.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../../fpdb/dbname.mdb") & ";Persist Security Info=False"
    Line 127: IF cmdtxt = "insert" THEN
    	commUpdateBill.CommandText = "INSERT INTO bills([SessionYear], [Purpose], Chamber, [Section], [Article], RepVol, Supp, [By], updated, ClubName, ClubName2, PrimName, SecName, Title, BillCode, Comm, Page1, Email, [user], [pw], [Public], [ZAP]) VALUES('"& SessYr & "', '" & Purpose & "', '" & Chamber & "', '" & Section & "', '" & Article & "', '" & RepVol & "', '" & Supp & "', '" & By & "', '" & FTGupdated & "', '" & FTGClubName & "', '" & FTGClubName2 & "', '" & FTGPrimName & "', '" & FTGSecName & "', '" & FTGTitle & "', '" & FTGBillCode & "', '" & FTGComm & "', '" & FTGPage1 & "', '" & FTGEmail & "', '" & FTGUser & "', '" & FTGPW & "', '" &Pub &"', '" &Zap &"')"                           
    ELSE
    	commUpdateBill.CommandText = "UPDATE bills SET bills.[updated] = now(), Email='" & ftgEmail & "', [PW]='" & FTGpw & "', [Zap]='" & Zap & "', [Purpose]='" & Purpose & "', Chamber='" & Chamber & "', [Section]='" & Section & "', [Article]='" & Article & "', RepVol='" & RepVol & "', Supp='" & Supp & "', [By]='" & By & "', ClubName='" &FTGClubName &"', ClubName2='" &FTGClubName2 &"', PrimName='" & FTGPrimName & "', SecName='" & FTGSecName & "', Title='" & FTGTitle & "', BillCode='" & FTGBillCode & "', Comm='" & FTGComm & "', [Public]='" &Pub & "', Page1='" & FTGPage1 & "'  WHERE [Key] = " & BK & " "
    END IF
    response.write commUpdateBill.CommandText
    commUpdateBill.CommandType = 1
    commUpdateBill.CommandTimeout = 0
    commUpdateBill.Prepared = true
    commUpdateBill.Execute()
    
    Set commUpdateBill = Nothing
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Did you make database changes to that table?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    109
    Rep Power
    12
    Originally Posted by Vomster
    Did you make database changes to that table?
    No, haven't even looked at that site since this time last year.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    You doing an update or insert when this happens?

    If it's an update, see what the value of [Key]/BK in the code is when you attempt to update.

    If it's an insert, try to do an insert directly into the table itself and see what happens.

    Basically it's telling you that the primary key, Key, is null.
    Last edited by Vomster; February 7th, 2011 at 01:30 PM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    109
    Rep Power
    12
    Originally Posted by Vomster
    You doing an update or insert when this happens?

    If it's an update, see what the value of [Key]/BK in the code is when you attempt to update.

    If it's an insert, try to do an insert directly into the table itself and see what happens.

    Basically it's telling you that the primary key, Key, is null.
    Yes, it is definitely attempting to execute the "INSERT" code. I know i would have to pass the key value if I was doing an update, but an insert should index the record automatically.
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    So did you try to insert a record manually? Did it work?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    109
    Rep Power
    12
    Originally Posted by Vomster
    So did you try to insert a record manually? Did it work?
    I am guessing at this point the DB was either changed or corrupted. The Primary Key was changed from an autonumber to a number. So when the INSERT command tried to index the new record it was receiving a null value.

    Now my problem is re-establishing the key as an autonumber without jumbling the record order in the db.
  14. #8
  15. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    Good luck
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    109
    Rep Power
    12
    Originally Posted by Vomster
    Good luck
    It wasn't too bad, I had to export the data from Access into Excel, copy the old DB structure to a new table, then import the data back into Access. Each record has a PDF linked to it based on the Key so keeping the records in order was critical. That is probably bad DB structure design but it's legacy now. In the future I should create a unique identifier for each record and link the pdfs to that number instead of the key. Live and learn.

    Comments on this post

    • Vomster agrees : Great Job!

IMN logo majestic logo threadwatch logo seochat tools logo