Object cannot be cast from DBNull to other types.

How to Resolve the error Object cannot be cast from DBNull to other types.

Reason for the error:

In an object-oriented programming language, null means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column. Source:MSDN

Actual Code which I faced error:

Before changed the code:

if( ds.Tables[0].Rows[0][0] == null ) // Which is not working

{

seqno = 1;

}

else

{

seqno = Convert.ToInt16(ds.Tables[0].Rows[0][0]) + 1;

}

After changed the code:

if( ds.Tables[0].Rows[0][0] == DBNull.Value ) //which is working properly

{

seqno = 1;

}

else

{

seqno = Convert.ToInt16(ds.Tables[0].Rows[0][0]) + 1;

}

Conclusion: when the database value return the null value, we recommend to use the DBNull class instead of just specifying as a null like in C# language.

Please let me know your feedback

Thanks

YES
but you override the value of parameter @Value

SET @Value = '''' + @txtValue + ''''

hope this will help you
(If this post is useful then mark it as "Accepted Answer")

Check the value of @status, @ColumnName and @Value... any one of them is null or blank

I advice you first print @cmd query before execute it and verify the query....

print @cmd
insert into #result
EXEC(@cmd)

hope this will help you.

Hi,

Here @RecordCount is out of scope from dynamic query.
why are you use use here dynamic query?

Intead of do create temp table out of dynamic query...

try following stored procedure

ALTER PROCEDURE Sp_get_custinfoserach2 (@PageIndex INT = 1,
@PageSize INT = 10,
@RecordCount INT output,
@ColumnName VARCHAR(50)=NULL,
@Value VARCHAR(50)=NULL,
@ddlValue VARCHAR(50)=NULL,
@txtValue VARCHAR(50)=NULL,
@status VARCHAR(30))
AS
BEGIN
SET nocount ON;
DECLARE @cmd AS NVARCHAR(max)
IF @txtValue IS NULL
BEGIN
SET @Value = '''' + @ddlValue + ''''
END
ELSE IF @ddlValue IS NULL
BEGIN
SET @Value = '''' + @txtValue + ''''
END
/*create a temp as same structure of your dynamic query select statement*/

CREATE TABLE #result
(
rownum INT,
userid INT,
NAME VARCHAR(100),
username VARCHAR(100),
status VARCHAR(15),
packageperiod VARCHAR(15),
packagename VARCHAR(100),
activationdate DATETIME,
oldexpirydate DATETIME,
balance NUMERIC(22, 4),
pyingamount NUMERIC(22, 4),
lastpaiddate DATETIME,
lastupdatetime DATETIME,
areaname VARCHAR(100),
mobno INT,
empname VARCHAR(100),
address VARCHAR(5000),
createddate DATETIME
)

SET @cmd ='
SELECT ROW_NUMBER() OVER (ORDER BY C_Register.UserId desc )AS RowNumber,
C_Register.UserId, C_Register.Name, C_Register.UserName,
C_Register.Status, Packages.PackagePeriod, Packages.PackageName,
C_Register.ActivationDate,Receive_Payment.OldExpiryDate,
Receive_Payment.Balance, Receive_Payment.PyingAmount,
Receive_Payment.LastPaidDate, C_Register.LastUpdateTime,
Area.AreaName, C_Register.MobNo, Employee.EmpName,
C_Register.Address,C_Register.CreatedDate
FROM C_Register INNER JOIN Receive_Payment ON C_Register.UserId = Receive_Payment.UserId
INNER JOIN Area ON C_Register.AreaId = Area.AreaId
INNER JOIN Employee ON Receive_Payment.EmpId = Employee.EmpId
INNER JOIN Packages ON Receive_Payment.PackageId = Packages.PackageId
where C_Register.AccountExpiry= Receive_Payment.OldExpiryDate And C_Register.Status = ' + @status + ' And ' + @ColumnName + ' = ' + @Value

insert into #result
EXEC(@cmd)

SELECT @RecordCount = COUNT(*) FROM #result

SELECT * FROM #result
WHERE rownum BETWEEN ((@PageIndex -1) * @PageSize) + 1 and ((( @PageIndex - 1 ) * @PageSize+ 1 ) + @PageSize ) - 1

END

hope this will help you.

Replace following line of your stored procedure

'SELECT @RecordCount = COUNT(*) FROM #results

To

set @RecordCount = Select COUNT(*) FROM #results

then it may work.