Export to Excel from Datasource control not working

[Alex Jermy]

Hi,

I am using the Grid and trying to export to Excel from a datasource. I have implemented it in the same way as the sample advises, but I am getting this error:

Object cannot be cast from DBNull to other types.

My code looks like the code I will paste below. I have attached my code in full.

Thanks very much,

Alex Jermy

Attachment: Bands_de72c172.zip

[Alex Jermy]

DataTable dt = new DataTable("PlanningUnit");

protected void Page_Load(object sender, EventArgs e)

{

this.PlanningUnitsGrid.Model.Columns[2].Tooltip = "Code";

this.PlanningUnitsGrid.Model.Columns[3].Tooltip = "Job";

this.PlanningUnitsGrid.Model.Columns[4].Tooltip = "Name";

this.PlanningUnitsGrid.Model.Columns[5].Tooltip = "Billing";

this.PlanningUnitsGrid.Model.Columns[6].Tooltip = "Sales Budget";

this.PlanningUnitsGrid.Model.Columns[7].Tooltip = "Estimated Material";

this.PlanningUnitsGrid.Model.Columns[8].Tooltip = "Schedule A Total";

this.PlanningUnitsGrid.Model.Columns[9].Tooltip = "Actual";

this.PlanningUnitsGrid.Model.Columns[10].Tooltip = "Contract";

this.PlanningUnitsGrid.Model.Columns[11].Tooltip = "Description";

this.PlanningUnitsGrid.Model.Columns[12].Tooltip = "Status";

SqlData.Select(DataSourceSelectArguments.Empty);

PlanningUnitsGrid.DataSourceID = SqlData.ID;

PlanningUnitsGrid.DataBind();

//PlanningUnits planningUnits = new PlanningUnits();

//lblNavigateUrl.Text = "<a rel='nofollow' rel='nofollow' href='/XWeb-Dev02/customised/general/Pendennis/PSY_Workpackage/Bands/psy_create_wp_norms.aspx?pj=" + Entity.EntityID + "&user=" + UserID + " target='_blank'><i class=\"fas fa-plus fa blue\"></i><span class=\"fa - layers - text fa - inverse\" data-fa-transform=\"shrink - 11.5 rotate--30\" style=\"font=\"Helvetica\" - weight:900\"></span></a>";

//this.gridHidden.DataSource = GetPlanningUnitDetails(false);

//this.gridHidden.DataBind();

this.Session["Parameter"] = Parameter.ToString();

this.Session["Entity"] = Entity.EntityID.ToString();

this.Session["User"] = UserID.ToString();

}

　

private void BindGrid()

{

PlanningUnitsGrid.DataSource = GetPlanningUnitDetails(false);

PlanningUnitsGrid.DataBind();

}

protected void Grid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e)

{

ExcelExport exp = new ExcelExport();

DataView data = (DataView)SqlData.Select(new DataSourceSelectArguments());

IList data1 = data;

DataTable dt = data.Table;

var emp = (from DataRow row in dt.Rows

select new PlanningUnits

{

EXT_Workpackage_ID = Convert.ToInt32(row["EXT_Workpackage_ID"]),

workpackage_code = row["workpackage_code"].ToString(),

name = row["name"].ToString(),

Job = row["Job"].ToString(),

status = row["status"].ToString(),

billing = row["billing"].ToString(),

Allocated_To = row["Allocated_To"].ToString(),

createddate = row["createddate"].ToString(),

earliest_start = row["earliest_start"].ToString(),

last_update = row["last_update"].ToString(),

Duration = Convert.ToInt32(row["Duration"]),

Actual = Convert.ToInt32(row["Actual"]),

child_duration = Convert.ToInt32(row["child_duration"]),

est_mat = Convert.ToInt32(row["est_mat"]),

scheduleatotal = Convert.ToInt32(row["scheduleatotal"]),

contracttype = row["contracttype"].ToString()

//latest_completion = row["latest_completion"].ToString(),

//Entity = Convert.ToInt32(row["Entity"]),

//xm = Convert.ToInt32(row["xm"]),

//xs = Convert.ToInt32(row["xs"]),

　

}).ToList();

exp.Export(PlanningUnitsGrid.Model, (IEnumerable)emp, "Export.xlsx", ExcelVersion.Excel2010, true, true, "flat-lime");

}

[Alex Jermy]

Also, this is the stacktrace:

[Exception: Object cannot be cast from DBNull to other types.]
   Syncfusion.JavaScript.Web.CommonDataBoundControl.RaisePostBackEvent(String EventArgument) +443
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5028

[Farveen Sulthana Thameeztheen Basha]

Hi Alex, 

 

Thanks for contacting Syncfusion Support. 

 

Query#:- Object cannot be cast from DBNull to other types. 

 

We have checked your query and we suspect that the reported problem occurs if your DB table can contain nulls, when you try to cast the Null values, as nulls can't be turned into valid objects. 

To overcome this you need to check the returned value as DBNull.Value or Not.  Please ensure to check the Null value before converting to string.  

We have prepared sample with Null values which can be downloaded from the below location.  

https://www.syncfusion.com/downloads/support/directtrac/146537/ze/Sample_(2)-104598299.zip 

If possible replicate the issue in the below sample in which scenario you have faced the issue and revert us back. 

Refer to the below Links:- 

https://stackoverflow.com/questions/33740382/c-object-cannot-be-cast-from-dbnull-to-other-types 

https://www.codeproject.com/Questions/686283/Object-cannot-be-cast-from-DBNull-to-other-types-e 

Regards, 

Farveen sulthana T