I have a report that is generated by passing a few variables via a SQL string. The problem is, when I try to export the resulting gridview to Excel, the gridview blanks itself out. I'm trying to use session variables to store the dataset but I don't understand them too well. Can someone help?
This is the code that 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 the ClassTesting class to determine if the dates are real, and 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 the date is stored as an INT, you have to request the string and then
// convert it to an 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 to see if a specific Agent is 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 as Agent_Name, CCM.UNIQUE_CLAIM_ID as Issue_Number, CCM.CLAIM_ID as Claim_Number, ";
sqlquery = sqlquery + "CCM.SOCSEC as Employee_Last_Digit, CONVERT(VARCHAR(10), CCM.DATE_IMPORTED, 101) AS Import_Date, CONVERT(VARCHAR(10), CCM.Orig_Open_Date, 101) as Original_Review_Date, ";
sqlquery = sqlquery + "AGL.ACCT_GRP as Account_Name, AL.ACCT_NUM as Account_Number, CCM.CDBBEN as Benefit_Option, BT1.StatusText as BenType1, ";
sqlquery = sqlquery + "BT2.StatusText as BenType2, BT3.StatusText as BenType3, CCM.Cmplt as Review_Validated, CCM.Vldtn_Cmmnts as Validation_Comments, ";
sqlquery = sqlquery + "CCM.Gtkpr_Cmmnts as Gatekeeper_Comments, TS.StatusText as 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 + "'") + " AND CCM.Cmplt " + (Val_Value == "" ? "IS NULL" : "LIKE '" + Val_Value + "'") + " AND CCM.TrackingStatus IN (" + Trk_Value + ") AND CCM.SpareFinished >= '" + StDt_Value + "' AND CCM.SpareFinished <= '" + EnDt_Value + "'";
}
else
{
if (BTN_Value == "2")
{
sqlquery = "Select DISTINCT PL.PROC_NM as Agent_Name, CCM.UNIQUE_CLAIM_ID as Issue_Number, CCM.CLAIM_ID as Claim_Number, ";
sqlquery = sqlquery + "CCM.SOCSEC as Employee_Last_Digit, CONVERT(VARCHAR(10), CCM.DATE_IMPORTED, 101) AS Import_Date, CONVERT(VARCHAR(10), CCM.Orig_Open_Date, 101) AS Original_Review_Date, ";
sqlquery = sqlquery + "AGL.ACCT_GRP as Account_Name, AL.ACCT_NUM as Account_Number, CCM.CDBBEN as Benefit_Option, BT1.StatusText as BenType1, ";
sqlquery = sqlquery + "BT2.StatusText as BenType2, BT3.StatusText as BenType3, CCM.Cmplt as Review_Validated, CCM.Vldtn_Cmmnts as Validation_Comments, ";
sqlquery = sqlquery + "CCM.Gtkpr_Cmmnts as Gatekeeper_Comments, TS.StatusText as 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 + "'") + " AND CCM.TrackingStatus IN (" + Trk_Value + ") AND CCM.SpareFinished >= '" + StDt_Value + "' AND CCM.SpareFinished <= '" + EnDt_Value + "'";
}
else
{
sqlquery = "Select DISTINCT PL.PROC_NM as Agent_Name, CCM.UNIQUE_CLAIM_ID as Issue_Number, CCM.CLAIM_ID as Claim_Number, ";
sqlquery = sqlquery + "CCM.SOCSEC as Employee_Last_Digit, CONVERT(VARCHAR(10), CCM.DATE_IMPORTED, 101) AS Import_Date, CONVERT(VARCHAR(10), CCM.Orig_Open_Date, 101) AS Original_Review_Date, ";
sqlquery = sqlquery + "AGL.ACCT_GRP as Account_Name, AL.ACCT_NUM as Account_Number, CCM.CDBBEN as Benefit_Option, BT1.StatusText as BenType1, ";
sqlquery = sqlquery + "BT2.StatusText as BenType2, BT3.StatusText as BenType3, CCM.Cmplt as Review_Validated, CCM.Vldtn_Cmmnts as Validation_Comments, ";
sqlquery = sqlquery + "CCM.Gtkpr_Cmmnts as Gatekeeper_Comments, TS.StatusText as 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 + "'") + " AND CCM.Cmplt " + (Val_Value == "" ? "IS NULL" : "LIKE '" + Val_Value + "'") + " AND CCM.TrackingStatus IN (" + Trk_Value + ") AND CCM.SpareFinished >= '" + StDt_Value + "' AND CCM.SpareFinished <= '" + EnDt_Value + "'";
}
}
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand(sqlquery, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
// Fill the DataSet.
DataSet ds = new DataSet();
adapter.Fill(ds, "dailyview");
// Store the dataset in a session variable
Session["SSQualOfSub"] = ds;
// Perform the binding.
GVQualOfSub.DataSource = ds;
GVQualOfSub.DataBind();
}
}
And here is the code I'm using to export:
private void ExportGridView()
{
// Exports the data in the GridView to Excel
// First call the session variable to refill the 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 an error when I try to export.
Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.DataTable'.
My apologies, I'm somewhat new to C#. Any help is appreciated.
You are storing a DataSet
in Session and later you are trying to access it as DataTable
.
You should do:
DataSet gridDataSource = (DataSet)Session["SSQualOfSub"];
instead of
DataTable gridDataSource = (DataTable)Session["SSQualOfSub"];
I am not sure about your particular scenario, but usually it is not a good idea to store large amount of data in Session
, as it is maintained per user on the server.
Apart from the current error, you should use SqlParameter
with your SqlCommand
, you are concatenating queries and prone to SQL Injection