Cannot insert the value NULL into column 'PostCategoryID', table 'Blog_MySite_com.dbo.be_PostCategory'; column does not allow nulls. INSERT fails.

Feb 12, 2009 at 11:47 AM
Edited Feb 12, 2009 at 11:52 AM
I *was* getting the "Oops page could not be found" for a while but after I disabled customErrors in the web.config here's is really happening (error dump below). I'd really appreciate some help as we went into production with the blog yesterday.
BTW - we're using [BlogEngine.NET 1.4.5.14] freshly upgraded from [1.4.5] - using SQL 2008 database on Windows 2008/IIS7.

Full Error Dump:
=============================================
Description: An unhandled exception occurred during the execution of the current web request. Please
review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'PostCategoryID', table 'Blog_MySite_com.dbo.be_PostCategory'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Source Error:

Line 235:		}
Line 236:
Line 237: post.Save();
Line 238:
Line 239: Session.Remove("content");

Source File: c:\blog\admin\Pages\Add_entry.aspx.cs    Line: 237

Stack Trace:

[SqlException (0x80131904): Cannot insert the value NULL into column 'PostCategoryID', 
table 'Blog_MySite_com.dbo.be_PostCategory'; column does not allow nulls. INSERT fails.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
+1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean
breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler,
SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
+2392
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior,
String resetOptionsString) +204
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior
runBehavior, Boolean returnStream, Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior
runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String
methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
BlogEngine.Core.Providers.DbBlogProvider.UpdateCategories(Post post, DbConnection conn,
DbProviderFactory provider) in C:\Projects\blogengine_source\BlogEngine\DotNetSlave.BusinessLogic\
Providers\DbBlogProvider.cs:1477
BlogEngine.Core.Providers.DbBlogProvider.UpdatePost(Post post) in
C:\Projects\blogengine_source\BlogEngine\DotNetSlave.BusinessLogic\Providers\DbBlogProvider.cs:438
BlogEngine.Core.Providers.BlogService.UpdatePost(Post post) in C:\Projects\blogengine_source\
BlogEngine\DotNetSlave.BusinessLogic\Providers\BlogService.cs:113
BlogEngine.Core.Post.DataUpdate() in C:\Projects\blogengine_source\BlogEngine\
DotNetSlave.BusinessLogic\Post.cs:773
BlogEngine.Core.BusinessBase`2.Update() in C:\Projects\blogengine_source\BlogEngine\
DotNetSlave.BusinessLogic\BusinessBase.cs:352
BlogEngine.Core.BusinessBase`2.Save(String userName, String password) in
C:\Projects\blogengine_source\BlogEngine\DotNetSlave.BusinessLogic\BusinessBase.cs:313
BlogEngine.Core.BusinessBase`2.Save() in C:\Projects\blogengine_source\BlogEngine\
DotNetSlave.BusinessLogic\BusinessBase.cs:288
admin_entry.btnSave_Click(Object sender, EventArgs e) in
c:\blog\admin\Pages\Add_entry.aspx.cs:237
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String
eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean
includeStagesAfterAsyncPoint) +1565

=============================================
Feb 12, 2009 at 12:00 PM
FYI - I've DROPPED/RECREATED the dbo.be_PostCategory table with NULLs allowed on all three columns and it looks like I can get by w/out an error now -
but obviously I'm scared that will break something else or more importantly put me out of sync w/the latest version of BlogEngine so please someone help if you can!
Feb 12, 2009 at 12:08 PM
Edited Feb 12, 2009 at 12:09 PM
Argh - same error popping up on Profiles page now - guess I'll have to rescript/recreate that table too until someone helps me fix the core issue.

Cannot insert the value NULL into column 'ProfileID', table 'Blog_MySite_com.dbo.be_Profiles';
column does not allow nulls. INSERT fails. The statement has been terminated.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ProfileID', table 'Blog_MySite_com.dbo.be_Profiles';
column does not allow nulls. INSERT fails.
The statement has been terminated.

Source Error:

Line 146:		pc.AboutMe = tbAboutMe.Text;
Line 147:
Line 148: pc.Save();
Line 149: }
Line 150:

Source File: c:\blog\admin\Pages\Profiles.aspx.cs    Line: 148

Stack Trace:

[SqlException (0x80131904): Cannot insert the value NULL into column 'ProfileID', table 'Blog_MySite_com.dbo.be_Profiles'; column does not 
allow nulls. INSERT fails.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream,
Boolean async) +954
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream,
String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
BlogEngine.Core.Providers.DbBlogProvider.UpdateProfile(AuthorProfile profile) in C:\Projects\blogengine_source\BlogEngine\
DotNetSlave.BusinessLogic\Providers\DbBlogProvider.cs:1772
BlogEngine.Core.Providers.DbBlogProvider.InsertProfile(AuthorProfile profile) in C:\Projects\blogengine_source\BlogEngine\
DotNetSlave.BusinessLogic\Providers\DbBlogProvider.cs:1688
BlogEngine.Core.Providers.BlogService.InsertProfile(AuthorProfile profile) in C:\Projects\blogengine_source\BlogEngine\
DotNetSlave.BusinessLogic\Providers\BlogService.cs:204
BlogEngine.Core.AuthorProfile.DataInsert() in C:\Projects\blogengine_source\BlogEngine\DotNetSlave.BusinessLogic\AuthorProfile.cs:316
BlogEngine.Core.BusinessBase`2.Update() in C:\Projects\blogengine_source\BlogEngine\DotNetSlave.BusinessLogic\BusinessBase.cs:345
BlogEngine.Core.BusinessBase`2.Save(String userName, String password) in C:\Projects\blogengine_source\BlogEngine\
DotNetSlave.BusinessLogic\BusinessBase.cs:313
BlogEngine.Core.BusinessBase`2.Save() in C:\Projects\blogengine_source\BlogEngine\DotNetSlave.BusinessLogic\
BusinessBase.cs:288
admin_profiles.lbSaveProfile_Click(Object sender, EventArgs e) in c:\blog\admin\Pages\Profiles.aspx.cs:148
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Coordinator
Feb 12, 2009 at 4:25 PM
You upgraded BE 1.4.5 to 1.4.5.14.  Did you copy over all the 1.4.5.14 files into your 1.4.5 existing blog?

You would need to compile the BE 1.4.5.14 core files to produce a DLL that goes in your BIN directory, and a number of the non-core files have also changed between 1.4.5 and 1.4.5.14.  This includes, but is definitely not limited to, files in the App_Code folder, admin folder, etc.  There's also one new DB column in 1.4.5.14 since 1.4.5, ParentCommentID, for the be_PostComment table.

ALTER TABLE [dbo].[be_PostComment]
ADD [ParentCommentID] [uniqueidentifier] NOT NULL
DEFAULT ('00000000-0000-0000-0000-000000000000');
Feb 12, 2009 at 4:26 PM
You know I compiled both successfully 1st but I did not copy of the CORE files into the blog's bin/etc! I will try that!
Feb 12, 2009 at 4:43 PM
Thank you for replying btw!

Well I may have rejoiced too soon...
  • I did copy all the new source files to the blog directory
  • And... the [/bin/BlogEngine.Core.*] files appear to be the versions that were created when I compiled the two projects this morning.
Once I compile the two projects [BlogEngine.NET] and [DotNetSlave.BusinessLogic] do I actually need to do anything special other than copy all files from [BlogEngine.NET] to my blog's web root?

PS: fyi - I have already run that SQL update script to add that new column - thanks for asking though.
Coordinator
Feb 12, 2009 at 4:55 PM
Something's a little strange here.  I just looked at the SQL script for 1.4.5 and 1.5 (basically the same thing), and the PostCategoryID column of the be_PostCategory table and the ProfileID column of the be_Profiles table are both identity columns that should automatically get populated with an incrementing number by SQL Server.  So BE doesn't even insert a value into these columns since SQL Server does it automatically and you wouldn't even be allowed to insert a number into an identity column.  The columns should never get a NULL value.

So, check your SQL script to make sure you're running the right script.  In the script, the CREATE statement for the be_Profiles table (as an example) should be the following.  Also, maybe make sure the DB connection string in your web.config file is pointing to the correct server/DB (if you happen to have more than one server or DB).

CREATE TABLE [dbo].[be_Profiles](
    [ProfileID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](100) NULL,
    [SettingName] [nvarchar](200) NULL,
    [SettingValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_be_Profiles] PRIMARY KEY CLUSTERED
(
    [ProfileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Is this what yours looks like too?
Feb 12, 2009 at 5:04 PM
I was wondering if the [PostCategoryID] and [ProfileID] columns were intended to be identity columns or if they were supposed to be populated  with actual Category and Profile "ID" values. I actually tried DROPPING/CREATING one of those tables earlier to get past the NULL error, but once I discovered that the NULL error was occurring in more than one place I figured I shouldn't keep doing that.

FYI - my SQL script has same as yours above - but I'm trying to salvage an existing blog so I just used the upgrade SQL steps from [/setup/SQLite/SQLiteUpgradeFrom1.4.5.0To1.4.0.0.txt]. Should I just start over? Our blog only has 8 users and 4 posts so far... so I guess I could recreate it all relatively quick - IF you think starting from scratch with SQL has a chance of working.
Coordinator
Feb 12, 2009 at 5:08 PM
SQLite isn't the same as SQL Server -- I think.  I've never used SQLite.  If you're using SQL Server, you should be using the scripts in the /setup/SQLServer folder.

Also, I thought you were upgrading from 1.4.5 to 1.4.5.14.  So, why are you using the upgrade script from 1.4 to 1.4.5 ?
Feb 12, 2009 at 5:09 PM
Edited Feb 12, 2009 at 6:58 PM
OMG I'm going to turn beet red of embarrassment if I used the wrong upgrade SQL script....
Feb 12, 2009 at 7:22 PM
Well - I did appear to use the SQLLite upgrade script instead of the SQLServer one.  /blush, /grovel
The contents of both upgrade scripts didn't look that different but I'm sure it didn't help matters.... anyhow.

I'm now in process of rebuilding our DEV environment from scratch (SQL and /blog files) - hopefully things will work this time - I'll report back here when I'm done.

Thank you again for your help - I appreciate it!
Feb 13, 2009 at 1:26 PM
Edited Feb 13, 2009 at 1:26 PM
Thank you again for your help yesterday.

Once I rebuilt our DEV environment from scratch (SQL and blog source files) things seemed to be running well.

I did encounter a frustrating issue when I published to our PROD environment though. I don't know if this is SQL 2008's fault
or what but when I exported (DTS) the DEV SQL BLOG database to the PROD SQL BLOG database, a few columns
(PostCategory, PostTag etc) that I think are supposed to be IDENTITY columns  were not created as such
(fyi the
PROD SQL BLOG database was empty before the DEV export). When these columns are NOT setup at IDENTITY columns,
I get errors not unlike the first few in this thread again (Can't insert NULL values bla bla bla).

The frustrating thing is that with SQL 2008 I can't just go into table design mode and fix this - I have to DROP AND RECREATE
(making sure scripts force appropriate IDENTITY columsn) and THEN re DTS the data from DEV... again!

My question at this point I guess is - why/how are these columns not being setup at IDENTITY columns in the first place?
I see that they're supposedly specifying themselves as IDENTITY in the MSSQLSetup1.5.0.0.sql script - but somehow they were
never created that way on DEV - how could this be?
Coordinator
Feb 13, 2009 at 5:12 PM
Since these are identity columns are created as identity columns in the SQL script, they definitely should be created that way.  If I run the CREATE TABLE script below (from MSSQLSetup1.5.0.0.sql) in SQL 2008 Management Studio, it creates the be_PostCategory table with the correct identity column.

If you run this script below and are not getting an identity column, then something's obviously not right.

With SQL Management Studio, you should also be able to make a column an identity column in the table designer.  In the designer, you select the column, then at the bottom of the screen in the Column Properties pane, there's the 'Identity Specification' property you can set to 'Yes'.  I just tested this, and I can make a column an identity column, then turn it off, turn it back on, etc.  SQL Management Studio does have to drop and recreate the table to do this, but it does it behind the scenes for you when you save changes.

I'm not sure what happens when you get DTS involved.  It may not doing something correct, but can't say for sure.

/****** Object:  Table [dbo].[be_PostCategory]    Script Date: 12/22/2007 14:17:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_PostCategory](
    [PostCategoryID] [int] IDENTITY(1,1) NOT NULL,
    [PostID] [uniqueidentifier] NOT NULL,
    [CategoryID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_be_PostCategory] PRIMARY KEY CLUSTERED
(
    [PostCategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
Mar 5, 2009 at 10:06 PM
I current have the same problem while working on my website at http://www.swmerchant.com/merchant-services-news-research/ I can't even save post, delete post, or doing anything with it.
I came up with the solution though. Here what you can do:
* log in at admin
*click on Edit the post
* Remember don't delete or save the post yet
*Look on the bottom for "Slug(optional): "
*click on "Extract From Title"
*The title of the blog should be on the empty space
* Click Save Post.
*Everything should be normal now
- I still invest sometime to find out why it happen. Will post back to you guy more If i find out.