c# - Trying to use a session variable to export a gridview to Excel -
i have report generated passing few variables via sql string. problem is, when try export resulting gridview excel, gridview blanks out. i'm trying use session variables store dataset don't understand them well. can help?
this code runs on page load:
protected void page_load(object sender, eventargs e) { if (!ispostback) { sqlconnection sqlconnectionstatus = new sqlconnection(str); string ddl_value = convert.tostring(request.querystring["ddl_val"]); string val_value = convert.tostring(request.querystring["val_val"]); string trk_value = convert.tostring(request.querystring["trk_val"]); string acct_value = convert.tostring(request.querystring["acct_val"]); //use classtesting class determine if dates real, , fill in today's date if they're blank string stdt_value = classtesting.checkfields(request.form["txtstartdate"], "date"); string endt_value = classtesting.checkfields(request.form["txtenddate"], "date"); //string stdt_value = convert.tostring(request.querystring["stdt_val"]); //string endt_value = convert.tostring(request.querystring["endt_val"]); string btn_value; // because date stored int, have request string , // convert int string stdt_vals = request.querystring["stdt_val"].tostring(); string endt_vals = request.querystring["endt_val"].tostring(); string sqlquery; if (string.isnullorempty(acct_value)) { if (string.isnullorempty(ddl_value)) { btn_value = "2"; } else { btn_value = "1"; } } else { btn_value = "3"; } // check see if specific agent being requested if (btn_value == "1") { // int stdt_value = convert.toint32(stdt_vals); // int endt_value = convert.toint32(endt_vals); sqlquery = "select distinct pl.proc_nm agent_name, ccm.unique_claim_id issue_number, ccm.claim_id claim_number, "; sqlquery = sqlquery + "ccm.socsec employee_last_digit, convert(varchar(10), ccm.date_imported, 101) import_date, convert(varchar(10), ccm.orig_open_date, 101) original_review_date, "; sqlquery = sqlquery + "agl.acct_grp account_name, al.acct_num account_number, ccm.cdbben benefit_option, bt1.statustext bentype1, "; sqlquery = sqlquery + "bt2.statustext bentype2, bt3.statustext bentype3, ccm.cmplt review_validated, ccm.vldtn_cmmnts validation_comments, "; sqlquery = sqlquery + "ccm.gtkpr_cmmnts gatekeeper_comments, ts.statustext tracking_status "; sqlquery = sqlquery + "from closedclaims_merge ccm "; sqlquery = sqlquery + "left join proc_list pl on ccm.spare = pl.login "; sqlquery = sqlquery + "left join acct_list al on al.acct_num = ccm.cdbacc "; sqlquery = sqlquery + "left join acct_grp_list agl on agl.acct_grp_pk = al.acct_grp_fk "; sqlquery = sqlquery + "left join trackingstatus ts on ts.statuscode = ccm.trackingstatus "; sqlquery = sqlquery + "left join benefittype_v3 bt1 on bt1.statuscode = ccm.benefit_type1 "; sqlquery = sqlquery + "left join benefittype_v3 bt2 on bt2.statuscode = ccm.benefit_type2 "; sqlquery = sqlquery + "left join benefittype_v3 bt3 on bt3.statuscode = ccm.benefit_type3 "; sqlquery = sqlquery + "where ccm.spare " + (ddl_value == "" ? "is null" : "like '" + ddl_value + "'") + " , ccm.cmplt " + (val_value == "" ? "is null" : "like '" + val_value + "'") + " , ccm.trackingstatus in (" + trk_value + ") , ccm.sparefinished >= '" + stdt_value + "' , ccm.sparefinished <= '" + endt_value + "'"; } else { if (btn_value == "2") { sqlquery = "select distinct pl.proc_nm agent_name, ccm.unique_claim_id issue_number, ccm.claim_id claim_number, "; sqlquery = sqlquery + "ccm.socsec employee_last_digit, convert(varchar(10), ccm.date_imported, 101) import_date, convert(varchar(10), ccm.orig_open_date, 101) original_review_date, "; sqlquery = sqlquery + "agl.acct_grp account_name, al.acct_num account_number, ccm.cdbben benefit_option, bt1.statustext bentype1, "; sqlquery = sqlquery + "bt2.statustext bentype2, bt3.statustext bentype3, ccm.cmplt review_validated, ccm.vldtn_cmmnts validation_comments, "; sqlquery = sqlquery + "ccm.gtkpr_cmmnts gatekeeper_comments, ts.statustext tracking_status "; sqlquery = sqlquery + "from closedclaims_merge ccm "; sqlquery = sqlquery + "left join proc_list pl on ccm.spare = pl.login "; sqlquery = sqlquery + "left join acct_list al on al.acct_num = ccm.cdbacc "; sqlquery = sqlquery + "left join acct_grp_list agl on agl.acct_grp_pk = al.acct_grp_fk "; sqlquery = sqlquery + "left join trackingstatus ts on ts.statuscode = ccm.trackingstatus "; sqlquery = sqlquery + "left join benefittype_v3 bt1 on bt1.statuscode = ccm.benefit_type1 "; sqlquery = sqlquery + "left join benefittype_v3 bt2 on bt2.statuscode = ccm.benefit_type2 "; sqlquery = sqlquery + "left join benefittype_v3 bt3 on bt3.statuscode = ccm.benefit_type3 "; sqlquery = sqlquery + "where ccm.cmplt " + (val_value == "" ? "is null" : "like '" + val_value + "'") + " , ccm.trackingstatus in (" + trk_value + ") , ccm.sparefinished >= '" + stdt_value + "' , ccm.sparefinished <= '" + endt_value + "'"; } else { sqlquery = "select distinct pl.proc_nm agent_name, ccm.unique_claim_id issue_number, ccm.claim_id claim_number, "; sqlquery = sqlquery + "ccm.socsec employee_last_digit, convert(varchar(10), ccm.date_imported, 101) import_date, convert(varchar(10), ccm.orig_open_date, 101) original_review_date, "; sqlquery = sqlquery + "agl.acct_grp account_name, al.acct_num account_number, ccm.cdbben benefit_option, bt1.statustext bentype1, "; sqlquery = sqlquery + "bt2.statustext bentype2, bt3.statustext bentype3, ccm.cmplt review_validated, ccm.vldtn_cmmnts validation_comments, "; sqlquery = sqlquery + "ccm.gtkpr_cmmnts gatekeeper_comments, ts.statustext tracking_status "; sqlquery = sqlquery + "from closedclaims_merge ccm "; sqlquery = sqlquery + "left join proc_list pl on ccm.spare = pl.login "; sqlquery = sqlquery + "left join acct_list al on al.acct_num = ccm.cdbacc "; sqlquery = sqlquery + "left join acct_grp_list agl on agl.acct_grp_pk = al.acct_grp_fk "; sqlquery = sqlquery + "left join trackingstatus ts on ts.statuscode = ccm.trackingstatus "; sqlquery = sqlquery + "left join benefittype_v3 bt1 on bt1.statuscode = ccm.benefit_type1 "; sqlquery = sqlquery + "left join benefittype_v3 bt2 on bt2.statuscode = ccm.benefit_type2 "; sqlquery = sqlquery + "left join benefittype_v3 bt3 on bt3.statuscode = ccm.benefit_type3 "; sqlquery = sqlquery + "where al.acct_grp_fk " + (acct_value == "" ? "is null" : "like '" + acct_value + "'") + " , ccm.cmplt " + (val_value == "" ? "is null" : "like '" + val_value + "'") + " , ccm.trackingstatus in (" + trk_value + ") , ccm.sparefinished >= '" + stdt_value + "' , ccm.sparefinished <= '" + endt_value + "'"; } } sqlconnection con = new sqlconnection(str); sqlcommand cmd = new sqlcommand(sqlquery, con); sqldataadapter adapter = new sqldataadapter(cmd); // fill dataset. dataset ds = new dataset(); adapter.fill(ds, "dailyview"); // store dataset in session variable session["ssqualofsub"] = ds; // perform binding. gvqualofsub.datasource = ds; gvqualofsub.databind(); } } and here code i'm using export:
private void exportgridview() { // exports data in gridview excel // first call session variable refill gridview datatable griddatasource = (datatable)session["ssqualofsub"]; gvqualofsub.visible = true; gvqualofsub.datasource = griddatasource; gvqualofsub.databind(); string attachment = "attachment; filename=qual_of_subs.xls"; response.clearcontent(); response.addheader("content-disposition", attachment); response.contenttype = "application/ms-excel"; stringwriter sw = new stringwriter(); htmltextwriter htw = new htmltextwriter(sw); gvqualofsub.rendercontrol(htw); response.write(sw.tostring()); response.end(); } i'm getting error when try export.
unable cast object of type 'system.data.dataset' type 'system.data.datatable'.
my apologies, i'm new c#. appreciated.
you storing dataset in session , later trying access datatable.
you should do:
dataset griddatasource = (dataset)session["ssqualofsub"]; instead of
datatable griddatasource = (datatable)session["ssqualofsub"]; i not sure particular scenario, not idea store large amount of data in session, maintained per user on server.
apart current error, should use sqlparameter sqlcommand, concatenating queries , prone sql injection
Comments
Post a Comment