SQL Server 2005-Exceptions when using .Net Application

1) Invalid cast Exception


Error Description : System.InvalidCastException: Object cannot be cast from DBNull to other types.
at System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider)
at System.Convert.ToInt32(Object value)
at DAC.callincidentinfo.GetCallerCount()
Error Source : mscorlib
Errorno : 1
Class Name : callincidentinfo
Method Name : GetCallerCount()


RootCause: This exception occurs when converting null value into integer.


Suppose consider the following stored procedure which throws an exception “Invalid cast Exception”


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CALLERCOUNT] @CALLERPHONE VARCHAR(20),@CALLERCOUNT INT OUTPUT
AS

BEGIN


SET @CALLERCOUNT= ( SELECT TOP 1 ISNULL([COUNT],0)
FROM [dbo].[CALLERPHONES]
WHERE [PHONENO]=@CALLERPHONE
ORDER BY [COUNT]DESC)

INSERT INTO [dbo].[CALLERPHONES]
([PHONENO]
,[COUNT]
,[CURDATE])
VALUES
(@CALLERPHONE,
ISNULL(@CALLERCOUNT+1,1),
GETDATE())

END

The above stored Procedure:

Caller count was selected top 1 from dbo.callercount.
This exception occurs when converting null value into integer.
If there was no data for that particular phone number ,Because
The data was not available from the table. First we are selecting the count then inserting the data. So this exception occurs.

How to overcome

To overcome the above exception put If condition. If it was null then set it to zero then this exception overcomes.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[GET_CALLERCOUNT] @CALLERPHONE VARCHAR(20),@CALLERCOUNT INT OUTPUT
AS


BEGIN

SET @CALLERCOUNT= ( SELECT TOP 1 ISNULL([COUNT],0)
FROM [dbo].[CALLERPHONES]
WHERE [PHONENO]=@CALLERPHONE
ORDER BY [COUNT]DESC)

IF @CALLERCOUNT IS NULL
SET @CALLERCOUNT = 0

INSERT INTO [dbo].[CALLERPHONES]
([PHONENO]
,[COUNT]
,[CURDATE])
VALUES
(@CALLERPHONE,
ISNULL(@CALLERCOUNT+1,1),
GETDATE())


END






2)Arithmetic overflow error converting int to data type numeric:

This type Exception occurs when the size of the data type Exceeds.

The exception was as shown below


Error Description: System.Data.SqlClient.SqlException: Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
At System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DAC.SQLHelper.ExecuteNonQuery(SqlCommand cmd, CommandType cmdType, String cmdText)

Suppose if assign userid to tiny int
Then it accept only upto 999.if this number exceeds i.e if we insert more than 999
i.e. 1000 onwards it shows this exception.

Solution is change the data type small int into int or big int then it accepts and it overcomes this exception.