Menu

Search

InstantASP Support

Help & Support


Cannot insert null exception after upgrading from InstantForum 4.1.4 to 2010 or above


Known Issues
After upgrading your InstantForum 4.1.4 database to the InstantForum 2010 schema by running the "4.1.4_to_2010.sql" upgrade script provided with InstantForum 2010 you may see the following exception message when attempting to access your InstantForum 2010 installation through a web browser…

Exception Message: Cannot insert the value NULL into column 'LastPosterUsername', table 'BDUK2012.dbo.InstantForum_Topics'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'PostID', table 'BDUK2012.dbo.InstantForum_Messages'; column does not allow nulls. INSERT fails.
The statement has been terminated

Before I explain further I would like to point out we have taken great care to ensure this will not happen again in further InstantForum upgrades. This only effected the 4.1.4 to 2010 upgrade. All other database upgrade scripts should work correctly.

Cause

This error is caused as the "4.1.4_to_2010.sql" upgrade script will drop all constraints or default values from your existing InstantForum 4.1.4 tables. Unfortunately before InstantForum 2010 we didn't explicitly name our column constraints which caused SQL Server to generate a random name for each constraint. As we didn't know the name of our constraints ahead of time we opted to drop all existing constraints during the upgrade process and add these back in with an explicit name.

The code below is responsible for dropping all constrains during the 4.1.4 to 2010 upgrade process...

BEGIN
DECLARE @tname nvarchar(100)
DECLARE @name nvarchar(100)

DECLARE MSGCURSOR CURSOR FOR 
SELECT tname=object_name(parent_obj),name FROM sysobjects 
WHERE OBJECTPROPERTY(id,N'IsDefaultCnst') <> 0 AND name LIKE '%InstantFo%'

OPEN MSGCURSOR
FETCH NEXT FROM MSGCURSOR INTO @tname,@name
WHILE @@fetch_status=0 BEGIN
EXECUTE('alter table ' + @tname + ' drop constraint ' + @name)
FETCH NEXT FROM MSGCURSOR INTO @tname,@name
END
CLOSE MSGCURSOR
DEALLOCATE MSGCURSOR
END
GO

Unfortunately there is a bug in the upgrade script that may cause some default constraints / values not to be added to specific tables. The default values are required otherwise you will see the error message shown at the top of this article.

Resolution

To resolve this problem please follow the steps below.

After you've ran the "4.1.4_to_2010.sql" upgrade script against your database please open each of the following tables via SQL Server Management Studio in design view (so you can modify the tables) and check that each column marked a NOT NULL has a default value defined. The three key tables to check are...

  • InstantForum_Topics
  • InstantForum_Folders
  • InstantForum_Users

The key fields to ensure have a default value are specified below...

  • InstantForum_Toics.TotalRatings - should have a default value of 0
  • InstantForum_Toics.ParentID - should have a default value of 0
  • InstantForum_Toics.EditDateStamp - should have a default value of GetDate()
  • InstantForum_Toics.MessageIconName - should have a default value of ''
  • InstantForum_ForumSubscriptions.DateStamp - should have a default value of GetDate()
  • InstantForum_TopicSubscriptions.DateStamp - should have a default value of GetDate()
  • InstantForum_Polls.Closed - should have a default value of 0

Number / Int Fields

All int, tinyint fields that do not allow nulls should have a default value of 0.

DateTime Fields

All date time fields fields that do not allow nulls should have a default values of GetDate(). This is the SQL Server function to return the current date & time. For example... (GetDate()

String Fields

All nvarchar(255), nvarchar(max) fields should have an empty string for the default value. This is 2 apostrophes. For example... ('').

That's It!

Once you've added the default values to fields that do not allow nulls you should not see this exception at run-time again. If the problem continues for you of course please don't hesitate to contact us or open a support ticket.


Optionally provide private feedback to help us improve this article...

Thank you for your feedback!


Comments

No Photo
0
andyf n posted 3 Years Ago
New Member with 3 recognition pointsNew Member with 3 recognition pointsNew Member with 3 recognition pointsNew Member with 3 recognition pointsNew Member with 3 recognition pointsNew Member with 3 recognition pointsNew Member with 3 recognition pointsNew Member with 3 recognition points
This has fixed the problem I was having with my upgrade - thanks good article!

Comments require login or registration.

Details

Product: InstantForum
Version: 4.1
Type: ERRMSG
Level: Novice
Article not rated yet.
Article has been viewed 6K times.
Last Modified: Last Year
Last Modified By: Ryan Healey

Options

Similar Articles