Object cannot be cast from DBNull to other types.

Question

public void gridCustInfoBinds(int pageIndex)

{

try

{

//open the db connection if it is closed...

if (connection.State == ConnectionState.Closed)

connection.Open();

string ColumnName = ddlColums.SelectedValue;

string ddlValue = ddlColumValue.SelectedValue;

string txtValue = txtColumValue.Text;

string status = ddlStatus.SelectedValue;

command = new SqlCommand();

command.CommandText = "sp_Get_CustInfoSerach2";

command.CommandType = CommandType.StoredProcedure;

command.Connection = connection;

command.Parameters.AddWithValue("@ColumnName", ColumnName);

command.Parameters.AddWithValue("@ddlValue", ddlValue);

command.Parameters.AddWithValue("@txtValue", txtValue);

command.Parameters.AddWithValue("@status", status);

command.Parameters.AddWithValue("@PageIndex", pageIndex);

command.Parameters.AddWithValue("@PageSize", int.Parse(ddlPaging.SelectedValue));

command.Parameters.Add("@RecordCount", SqlDbType.Int, 4);

 

                  //Note: Here throwing an error :Object cannot be cast from DBNull to other types.

 

command.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

IDataReader idr = command.ExecuteReader();

gridCustomer.DataSource = idr;

gridCustomer.DataBind();

idr.Close();

connection.Close();

int recordCount = Convert.ToInt32(command.Parameters["@RecordCount"].Value);

this.PopulatePager(recordCount, pageIndex);

}

catch (Exception ex)

{

lblMessageCustSerach.Text = ex.Message;

lblMessageCustSerach.Visible = true;

}

finally //Close db Connection if it is open....

{

if (connection.State == ConnectionState.Open)

connection.Close();

}

}

 

//My Store procedure is

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*/

SET @cmd ='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

)

Insert into #result

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 +

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

SELECT * FROM #results

WHERE rownumber BETWEEN'+ cast(( cast(@PageIndex as int) - 1) * cast(@PageSize as int) + 1 as varchar(50))+' AND'+cast(((( @PageIndex - 1 ) * @PageSize+ 1 ) + @PageSize ) - 1 as varchar(50))

+ 'DROP TABLE #results'

EXEC(@cmd)

END