Object cannot be cast from DBNull to other types

Im receving a runtime error for this, and I've checked other posts for similar errors, but this error is different. Does anybody have a clue?

Under Locals/ arparams/ for the first parameter "0" the more detail error reports:

"Data is Null, the method or property cannot cannot be called on null values"

Im rewriting this program in the 4.0 framework from 2.0, utilizing VisualStudio2010.

This ran fine previously without any erros in .NET 2.0.

Here is the C# which collects the parameters and calls the stored proceedure.

Here the first parameter strProjectID will be "" because this is a new project:

public static int SaveProject(string strProjectID, string strProjectName, string strProjectDesc, string strProjectNotes, string strProjectDate, string strProjectType)
        {
            // Update Project within hip_Project
            int newprojectid;
            SqlParameter[] arParms = new SqlParameter[6];


            arParms[0] = new SqlParameter("@pProjId", SqlDbType.Int, 1);
            if (strProjectID == "")
            {
                arParms[0].Value = DBNull.Value;
            }
            else
            {
                arParms[0].Value = strProjectID;
            }
            arParms[0].Direction = ParameterDirection.Input;


            arParms[1] = new SqlParameter("@pProjName", SqlDbType.VarChar, 255);
            arParms[1].Value = strProjectName;
            arParms[1].Direction = ParameterDirection.Input;


            arParms[2] = new SqlParameter("@pProjDesc", SqlDbType.VarChar, 1000);
            arParms[2].Value = strProjectDesc;
            arParms[2].Direction = ParameterDirection.Input;


            arParms[3] = new SqlParameter("@pProjNotes", SqlDbType.VarChar, 8000);
            arParms[3].Value = strProjectNotes;
            arParms[3].Direction = ParameterDirection.Input;


            arParms[4] = new SqlParameter("@pProjDate", SqlDbType.DateTime, 8);
            arParms[4].Value = strProjectDate;
            arParms[4].Direction = ParameterDirection.Input;


            arParms[5] = new SqlParameter("@pProjType", SqlDbType.Int, 1);
            if (strProjectType == "True")
            {
                arParms[5].Value = 1;
            }
            else
            {
                arParms[5].Value = 0;
            }
            arParms[5].Direction = ParameterDirection.Input;


            try
            {
                //SqlHelper.ExecuteNonQuery(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString, CommandType.StoredProcedure, "hip_InsUpdProj", arParms);
                newprojectid = Convert.ToInt32(SqlHelper.ExecuteScalar(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString, CommandType.StoredProcedure, "hip_InsUpdProj", arParms));
                return newprojectid;
            }
            catch (Exception exCallInsertOrgSP)
            {
                throw exCallInsertOrgSP;
            }
        }

Here is the original stored proceedure using SQL Server 2000. Now Im using SQL Server 2008:

CREATE proc dbo.hip_InsUpdProj
(
@pProjId	                int,
@pProjName               varchar(255),
@pProjDesc                varchar(1000),
@pProjNotes	varchar(8000),
@pProjDate	datetime,
@pProjType	int
)
as 

declare
	@RC	int,
	@ROWS	int
begin

if @pProjId = null
	begin
	insert into hip_Project (ProjectName, ProjectDesc, ProjectNotes, dtCreated)
	values (@pProjName, @pProjDesc, @pProjNotes, @pProjDate)
	end
else
	begin
	update hip_Project
	set ProjectName = @pProjName,
		ProjectDesc = @pProjDesc,
		ProjectNotes = @pProjNotes,
		dtCreated = @pProjDate,
		deleted = @pProjType
	where Project_Id = @pProjId
	end


if @pProjId = null
	SELECT SCOPE_IDENTITY() as newProjectID
else
	SELECT @pProjId as newProjectID
return 0

GO

I did find a workaround just passing in 0 instead of using DBNulls, but I would still like to find out why this problem exists?

Please let me know if you need further analysis, thank you in advance for your help!

Re: Object cannot be cast from DBNull to other types

Are you sure that your stored procedure returned a value? For instance, if table has no identity defined then SCOPE_IDENTITY() will return NULL which may not be converted to int32. Call the code with DBNull as it is listed here and see in SQL Management Studio if Project_Id has got an ID.

Re: Object cannot be cast from DBNull to other types

OK I executed this within Management Studio:

pProjID - Allow Nulls (checked)
pProjName - Project6Name
pProjDesc - Project6Desc
pProjNotes- Project6Notes
pProjDate- 12/16/2010
pProjType- 0

The NewProjectID = Null (this should contain a new projectID)
The ReturnValue = 0

the Project_ID column isIdentity with its seed and increment set.

Re: Object cannot be cast from DBNull to other types

NinjaSk8ter

pProjID - Allow Nulls (checked)

Huh? That's the problem. It should not allow nulls, it should be an automatically incremented ID.

Re: Object cannot be cast from DBNull to other types

Yes but isnt Null is whats coming in from the application (DBNull)?

This is and always was an auto incremented ID, I changed nothing.

Like I mentioned this worked perfectly before with .NET 2.0.

Is there something in the Stored Proceedure I should change?

Re: Object cannot be cast from DBNull to other types

NinjaSk8ter

...isnt Null is whats coming in from the application (DBNull)?

No. You sent NULL to the stored procedure (as @pProjId). In the SP you have

if @pProjId = null
    begin
    insert into hip_Project (ProjectName, ProjectDesc, ProjectNotes, dtCreated)
    values (@pProjName, @pProjDesc, @pProjNotes, @pProjDate)
    end
else
...

which means that if @pProjId is NULL then SP will insert new entry into the hip_Project table.

As we can see, there is no value specified for the Project_Id column, which means that

either this column should accept NULL
or it should be an identity column that is made up of values generated by the database.

Now, see the code below

if @pProjId = null  
    SELECT SCOPE_IDENTITY() as newProjectID  
else  
...

This tells me that it is expected to get a value from the SCOPE_IDENTITY function which returns the last identity value inserted into an identity column in the same scope. As there is only one INSERT statement - it means that the the Project_Id column should be an identity column and not NULL.

Re: Object cannot be cast from DBNull to other types

The Project_ID column should be, and is, an identity column which will create a new Project_ID if @pProjID = null.

The code snippet that you had listed IS part of my SP. I want to re-print this here:

If this SP worked perfectly in SQL Server 2000, why does this not return the ID value in SQL Server 2008?

CREATE proc dbo.hip_InsUpdProj
(
@pProjId         int,
@pProjName varchar(255),
@pProjDesc varchar(1000),
@pProjNotes varchar(8000),
@pProjDate datetime,
@pProjType int
)
as


declare
	@RC int,
	@ROWS int
begin


if @pProjId = null
	begin
	insert into hip_Project (ProjectName, ProjectDesc, ProjectNotes, dtCreated)
	values (@pProjName, @pProjDesc, @pProjNotes, @pProjDate)
	end
else
	begin
	update hip_Project
	set ProjectName = @pProjName,
		ProjectDesc = @pProjDesc,
		ProjectNotes = @pProjNotes,
		dtCreated = @pProjDate,
		deleted = @pProjType
	where Project_Id = @pProjId
	end


select @RC = @@error
if @RC <> 0
	return @RC
end


--print SCOPE_IDENTITY()


if @pProjId = null
	SELECT SCOPE_IDENTITY() as newProjectID
else
	SELECT @pProjId as newProjectID
return 0


GO

Is there something new in SQLServer 2008 that I need to account for in this SP?

Re: Object cannot be cast from DBNull to other types

Ok, it's my fault: I asked you about table structure and you sent me

pProjID - Allow Nulls (checked)

I thought it is a name of your column, that's why I said it's not correct.

Well, can you send me a script to create the table? So, I can test it myself.

Thanks,

Re: Object cannot be cast from DBNull to other types

Here is the structure for that table:

Column Name	Data Type	Allow Nulls
Project_Id	int	No Allow Nulls (IsIdentity w/ identityseed=200 w/ increment=1)
ProjectName	nvarchar(50)	No Allow Nulls
ProjectDesc	nvarchar(50)	Allow Nulls
deleted	int	No Allow Nulls
ProjectNotes	nvarchar(50)	Allow Nulls
dtCreated>	datetime	Allow Nulls

Here is the script to create the table

SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[hip_Project](
	[Project_Id] [int] IDENTITY(200,1) NOT NULL,
	[ProjectName] [nvarchar](50) NOT NULL,
	[ProjectDesc] [nvarchar](50) NULL,
	[deleted] [int] NOT NULL,
	[ProjectNotes] [nvarchar](50) NULL,
	[dtCreated] [datetime] NULL
) ON [PRIMARY]


GO


ALTER TABLE [dbo].[hip_Project] ADD  CONSTRAINT [DF_hip_Project_dtCreated]  DEFAULT (getdate()) FOR [dtCreated]
GO

Re: Here is the script to create the table

I discovered that there was a bug in the StoredProceedure:

if @pProjId = null

this is correct:

if @pProjID is null