Community
Showing results for 
Search instead for 
Do you mean 
Reply

Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The statement

New Member
Posts: 3
Country: USA

Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The statement

Today I started getting the below error that I have not seen before.  It occurs when I try to add a new contact: 

 

"Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails.  The statement has been terminated.

 

I am running Act! Pro Version 18.2.53.0, Update 1, on a Windows 7 Pro-64 PC.

 

Anyone have any suggestions?

Administrator
Posts: 4,024
Country: United_Kingdom

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

[ Edited ]

Hi SJL3,

Please could you try the database rebuild and repair procedures detailed in the following Knowledgebase article: http://kb.act.com/app/answers/detail/a_id/19642 and testing after the process has finished?

New Member
Posts: 3
Country: USA

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

Not resolving the issue. 

 

When I do the Tools > Database Maintenance >  Check and Repair, it runs for a while then I get the error “An error has been found.  For this to be fixed a database lock will need to be applied and all users will be locked out.  Would you like to repair the database now?”  I click yes and it runs for a while and then I get  “The database has been repaired.”  Unfortunately, the issues continue even after this repair.  I have tried the repair tool multiple times.  No change.

 

When I try the Act! Diagnostics Utility Database Repair Procedures, I get the original error “Unhandled Exception has occurred in your application.  If you click Continue, the application will ignore this error and attempt to continue.  If you click Quit, the application will close immediately.  Index was out of range.  Must be non-negative and less than the size of the collection.  Parameter name:  index” whenever I click the gray rectangle to the left of the appropriate Database name to select the database and choose any option.

 

 

Administrator
Posts: 4,024
Country: United_Kingdom

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

In this case, I would suggest giving our support team a call on 866 873 2006 for further troubleshooting. It is possible that this issue would require the Database Repair Services Team to investigate the database, as there could be some data corruption causing this issue.

You can find more detail on our Database Repair Services in the following Knowledgebase article: http://kb.act.com/app/answers/detail/a_id/24758
New Member
Posts: 10
Country: USA

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

I am having the same problem immediately after upgrading from 18.1 to 18.2.53 Update 2...

 

I am not able to add a contact nor duplicate a contact (either duplicate contact option).

 

I am able to add notes, etc... just am not able to add or duplicate contacts. It is obvious that the update caused some sort of db issue. This was not a hot-fix and I hope that SP can step up to handle this issue???

 

I am not able to create an empty copy of the DB as it gives me the same error.

 

ACT user for 19 years.

Administrator
Posts: 4,024
Country: United_Kingdom

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

Hi npicard,

Have you tried the rebuild and repair procedures which I reccomended above? http://kb.act.com/app/answers/detail/a_id/19642 

New Member
Posts: 10
Country: USA

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

Yes, I followed the instructions... can not create empty copy of db either... get the same error.

 

An ACT support person then walked through all of the options with me and still no fix. This certainly seems like the 18.2 db update caused the issue.

Administrator
Posts: 4,024
Country: United_Kingdom

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

It sounds like this could be an issue where the database schema is not being properly updated.

Please could you try the steps below to force the database schema to update manually? Make sure you have a backup of your database before proceeding.

 

  1. Ensure Act is closed
  2. Browse to your Act install directory (i.e C:\Program Files (x86)\ACT\ACT for Windows)
  3. Find the file ActUpdate.config and open it with notepad
  4. On the 8th line, between the <LatestSchemaVersion> tags, change the second number to 9. For example, 18.2.52.0 becomes 18.9.52.0. Note the original value of the number you changed.
  5. Save the file
  6. Open Act and open your database if it doesn't open automatically
  7. You will be prompted that "The database you are opening must be updated to a newer version of Act!" and will ask you to backup. If you have already taken a backup from just before starting this process, then you can safely click No
  8. You will be prompted that "Act! will now update the database". Click OK
  9. Wait until the Update is complete. You will see the prompt "The database has been updated" once it has completed. Click OK
  10. The process will now attempt to loop. You will then see the same messages from step 7 and 8 again, click No and then Cancel on the subsequent messages to stop this from repeating. Do this until there are no more prompts.
  11. Close Act
  12. Edit the ActUpdate.config file once again to restore the number you changed back to its original value. For example, 18.9.52.0 becomes 18.2.52.0.
  13. Save the file
  14. Open Act, and open your database
  15. Test if the problem has been resolved by adding a new contact
New Member
Posts: 10
Country: USA

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

that did it... thank you!
Administrator
Posts: 4,024
Country: United_Kingdom

Re: Cannot insert the value NULL into column 'AEM_OPTOUT', table 'SJL3Contacts.dbo.TBL_CONTACT'; column does not allow nulls. INSERT fails. The state

Great! You're welcome.