InvalidOperationException: The null value cannot be assigned .... when calling SubmitChanges

Question

I have a table A with an Identity column. When I insert a row via visualStudio it failed in the SubmitChanges with the following error:

InvalidOperationException: The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type

I searched about this in google and I found some discussions about the same issue. one of them is Here. it says that it's because the procedure returns a null value. I did as is wrote there. used sql trace, copy the insert command and run it in sql server. it realy returns null but the row was inserted correctly!!! the command as it is in the sql trace:

exec sp_executesql N'INSERT INTO [dbo].[MyName_Tbl]([x], [y], [z], [c], [v], [b], [n], [m], [a], [s], [d], [f], [g], [h], [j], [k], [l], [q], [w], [e], [r])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 varchar(8000),@p1 varchar(8000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 varchar(8000),@p5 nvarchar(4000),@p6 varchar(8000),@p7 varchar(8000),@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 nvarchar(4000),@p12 nvarchar(4000),@p13 varchar(8000),@p14 varchar(8000),@p15 nvarchar(4000),@p16 varchar(8000),@p17 nvarchar(4000),@p18 nvarchar(4000),@p19 nvarchar(4000),@p20 decimal(5,2)',@p0='406',@p1='Kabala',@p2=N'01/05/2012 13:47:01',@p3=N'k406/00033',@p4='406/00033',@p5=N'xxx',@p6='127.0.0.1',@p7='10',@p8=N'yyy',@p9=N'hh hh',@p10=N'0527159080',@p11=N'',@p12=N'',@p13='4580',@p14='1',@p15=N'Visa',@p16='0115',@p17=N'10',@p18=N'0',@p19=N'0232323',@p20=0

Can you explain me what's the problem and why in sql it executed correctly and in VS I get an error?

Answer 1

The error you are receiving has nothing to do with an invalid SQL statemt which explains why it works fine when you execute it directly on SQL Server.

The error is being thrown on your app and it's simply because the SQL statement is supposed to return an int containing the value of the last id inserted in the table but instead is returning a NULL value, which makes your program choke since null cannot be assigned to an int unless you declare it as a Nullable<int> (int?)