I have Developed Below Code for Crystal Report in ASP.NET and C# (Dataset), I have Multiple Pages in report i am getting 1st page Properly but while Navigating next page i am getting logon error.
public partial class WSDayBookReport : System.Web.UI.Page
{
SQLClass sqlfunc = new SQLClass();
DayBookDataSet dset = new DayBookDataSet();
ReportDocument rdoc;
}
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Page_Unload(object sender, EventArgs e)
{
try
{
rdoc.Close();
rdoc.Dispose();
rdoc = null;
}
catch (Exception ex)
{
}
}
public void OpeningStock()
{
string qry = "select i.ItemGroupID, ig.ItemGroupCode As ItemGroupCode, Sum(iss.RegularStock) As OpeningStock from ItemStock iss, Item i, ItemGroup ig where ig.ItemGroupID = i.ItemGroupID AND iss.ItemID = i.ItemId group by i.ItemGroupID,ig.ItemGroupCode";
sqlfunc.GetDataFromDT(dset.ItemGroupStock, qry, "sqlcon");
var q1 = from igs in dset.ItemGroupStock.AsEnumerable()
select igs;
foreach (var res in q1)
{
res.ConsumedStock = 0;
res.ClosingStock = 0;
}
dset.AcceptChanges();
//Response.Write(dset.ItemGroupStock.Rows.Count.ToString() + "=Opening Stock");
}
public void loadUser()
{
string qry = "select pp.PersonnelParticularID As UserID, pp.PersonnelName As UserName from WineOrder wo, WineOrderDetail wod, PersonnelParticular pp, ItemGroup ig, Item i where pp.PersonnelParticularID = wo.IssuedToPersonnelID AND ig.ItemGroupID = i.ItemGroupID AND i.ItemID = wod.ItemID AND wod.IsOrderCancelled = 0 AND wod.WineOrderId = wo.WineOrderId AND wo.WineOrderDate ='" + Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy") + "' group by pp.PersonnelParticularID,pp.PersonnelName";
sqlfunc.GetDataFromDT(dset.UserMaster, qry, "sqlcon");
qry = "select pp.PersonnelParticularID As UserID,ig.ItemGroupID, sum(wod.OrderQty) As ConsumptionQty from WineOrder wo, WineOrderDetail wod, ItemGroup ig, Item i, PersonnelParticular pp where pp.PersonnelParticularID = wo.IssuedToPersonnelID AND ig.ItemGroupID = i.ItemGroupID AND i.ItemID = wod.ItemID AND wod.IsOrderCancelled = 0 AND wod.WineOrderId = wo.WineOrderId AND wo.WineOrderDate='" + Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy") + "' group by ig.ItemGroupID, pp.PersonnelParticularID";
sqlfunc.GetDataFromDT(dset.UserConsume, qry, "sqlcon");
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
dset = new DayBookDataSet();
OpeningStock();
loadUser();
//Deducting new RU Entry after the entered date...
DataTable dtTemp = new DataTable();
string qry2 = "select Sum(rs.Quantity) As Qty,i.ItemGroupID from RegularStock rs, Item i where i.ItemID = rs.ItemID AND rs.RSDate>='" + Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy") + "' Group by i.ItemGroupID";
sqlfunc.GetDataFromDT(dtTemp, qry2, "sqlcon");
if (dtTemp.Rows.Count > 0)
{
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
var q5 = from igs in dset.ItemGroupStock.AsEnumerable()
where igs.ItemGroupID == Convert.ToInt32(dtTemp.Rows[i][1].ToString())
select igs;
foreach (var res in q5)
{
res.OpeningStock -= Convert.ToDecimal(dtTemp.Rows[i][0].ToString());
}
}
}
//Adding cosumed stock from the entered date.
dtTemp = new DataTable();
string qry = "select ig.ItemGroupID, sum(wod.OrderQty) As ConsumptionQty from WineOrder wo, WineOrderDetail wod, ItemGroup ig, Item i where ig.ItemGroupID = i.ItemGroupID AND i.ItemID = wod.ItemID AND wo.IssuedToPersonnelID is not null AND wod.IsOrderCancelled = 0 AND wod.WineOrderId = wo.WineOrderId AND wo.WineOrderDate>='10-01-2015' group by ig.ItemGroupID";
sqlfunc.GetDataFromDT(dtTemp, qry, "sqlcon");
if (dtTemp.Rows.Count > 0)
{
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
var q5 = from igs in dset.ItemGroupStock.AsEnumerable()
where igs.ItemGroupID == Convert.ToInt32(dtTemp.Rows[i][0].ToString())
select igs;
foreach (var res in q5)
{
res.OpeningStock += Convert.ToDecimal(dtTemp.Rows[i][1].ToString());
}
}
}
dset.AcceptChanges();
var qry10 = from us in dset.UserConsume.AsEnumerable()
group us by us.ItemGroupID into g
select new
{
TOTAL = g.Sum(x => x.ConsumptionQty),
IG = g.Key
};
foreach (var res in qry10)
{
//Response.Write(res.IG.ToString() +"-"+res.TOTAL.ToString()+ "----------");
var qrry = from igs in dset.ItemGroupStock.AsEnumerable()
where igs.ItemGroupID == res.IG
select igs;
foreach (var res2 in qrry)
{
res2.ClosingStock = res2.OpeningStock - res.TOTAL;
}
}
rdoc = new ReportDocument();
rdoc.Load(Server.MapPath("~/Report/WSDayBookRpt.rpt"));
rdoc.SetDataSource(dset);
CrystalReportViewer1.DataBind();
CrystalReportViewer1.ReportSource = rdoc;
CrystalReportViewer1.RefreshReport();
}
}
Please Help me out .. i have tried using session but in that i am not able to navigate after 2nd Page and if i use to write page number for navigation its working properly...
Thanks And Regards,
UPDATE
public partial class WSDayBookReport : System.Web.UI.Page
{
SQLClass sqlfunc = new SQLClass();
ReportDocument rdoc = new ReportDocument();
protected void Page_Load(object sender, EventArgs e)
{
if (Page.IsPostBack)
{
if (Session["CRpt"] != null)
{
rdoc =(ReportDocument) Session["CRpt"];
}
CrystalReportViewer1.ReportSource = rdoc;
CrystalReportViewer1.RefreshReport();
}
else
{
// 'NOT POSTBACK
}
}
protected void Page_Unload(object sender, EventArgs e)
{
if (Session["CRpt"] != null)
{
Session.Remove("CRpt");
}
Session["CRpt"]=rdoc;
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
DayBookDataSet dset = new DayBookDataSet();
//OpeningStock();
string qry = "select i.ItemGroupID, ig.ItemGroupCode As ItemGroupCode, Sum(iss.RegularStock) As OpeningStock from ItemStock iss, Item i, ItemGroup ig where ig.ItemGroupID = i.ItemGroupID AND iss.ItemID = i.ItemId group by i.ItemGroupID,ig.ItemGroupCode";
sqlfunc.GetDataFromDT(dset.ItemGroupStock, qry, "sqlcon");
var q1 = from igs in dset.ItemGroupStock.AsEnumerable()
select igs;
foreach (var res in q1)
{
res.ConsumedStock = 0;
res.ClosingStock = 0;
}
// loadUser();
qry = "select pp.PersonnelParticularID As UserID, pp.PersonnelName As UserName from WineOrder wo, WineOrderDetail wod, PersonnelParticular pp, ItemGroup ig, Item i where pp.PersonnelParticularID = wo.IssuedToPersonnelID AND ig.ItemGroupID = i.ItemGroupID AND i.ItemID = wod.ItemID AND wod.IsOrderCancelled = 0 AND wod.WineOrderId = wo.WineOrderId AND wo.WineOrderDate='" + Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy") + "' group by pp.PersonnelParticularID,pp.PersonnelName";
sqlfunc.GetDataFromDT(dset.UserMaster, qry, "sqlcon");
if (dset.UserMaster.Rows.Count == 0)
{
lblStatus.Text = "No Consumption on selected date.";
CrystalReportViewer1.ReportSource = null;
if (Session["ssnRdoc"] != null)
{
Session.Remove("ssnRdoc");
}
return;
}
else
{
lblStatus.Text = "";
}
//user consumption
qry = "select pp.PersonnelParticularID As UserID,ig.ItemGroupID, sum(wod.OrderQty) As ConsumptionQty from WineOrder wo, WineOrderDetail wod, ItemGroup ig, Item i, PersonnelParticular pp where pp.PersonnelParticularID = wo.IssuedToPersonnelID AND ig.ItemGroupID = i.ItemGroupID AND i.ItemID = wod.ItemID AND wod.IsOrderCancelled = 0 AND wod.WineOrderId = wo.WineOrderId AND wo.WineOrderDate='" + Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy") + "' group by ig.ItemGroupID, pp.PersonnelParticularID";
sqlfunc.GetDataFromDT(dset.UserConsume, qry, "sqlcon");
dset.AcceptChanges();
ViewState.Add("vsDset", dset);
//Deducting new RU Entry after the entered date...
DataTable dtTemp = new DataTable();
string qry2 = "select Sum(rs.Quantity) As Qty,i.ItemGroupID from RegularStock rs, Item i where i.ItemID = rs.ItemID AND rs.RSDate>='" + Convert.ToDateTime(txtDate.Text).ToString("MM/dd/yyyy") + "' Group by i.ItemGroupID";
sqlfunc.GetDataFromDT(dtTemp, qry2, "sqlcon");
if (dtTemp.Rows.Count > 0)
{
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
var q5 = from igs in dset.ItemGroupStock.AsEnumerable()
where igs.ItemGroupID == Convert.ToInt32(dtTemp.Rows[i][1].ToString())
select igs;
foreach (var res in q5)
{
res.OpeningStock -= Convert.ToDecimal(dtTemp.Rows[i][0].ToString());
}
}
}
//Adding cosumed stock from the entered date.
dtTemp = new DataTable();
qry = "select ig.ItemGroupID, sum(wod.OrderQty) As ConsumptionQty from WineOrder wo, WineOrderDetail wod, ItemGroup ig, Item i where ig.ItemGroupID = i.ItemGroupID AND i.ItemID = wod.ItemID AND wo.IssuedToPersonnelID is not null AND wod.IsOrderCancelled = 0 AND wod.WineOrderId = wo.WineOrderId AND wo.WineOrderDate>='" + Convert.ToDateTime(txtDate.Text.Trim()).ToString("MM/dd/yyyy") + "' group by ig.ItemGroupID";
sqlfunc.GetDataFromDT(dtTemp, qry, "sqlcon");
if (dtTemp.Rows.Count > 0)
{
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
var q5 = from igs in dset.ItemGroupStock.AsEnumerable()
where igs.ItemGroupID == Convert.ToInt32(dtTemp.Rows[i][0].ToString())
select igs;
foreach (var res in q5)
{
res.OpeningStock += Convert.ToDecimal(dtTemp.Rows[i][1].ToString());
}
}
}
dset.AcceptChanges();
var qry10 = from us in dset.UserConsume.AsEnumerable()
group us by us.ItemGroupID into g
select new
{
TOTAL = g.Sum(x => x.ConsumptionQty),
IG = g.Key
};
foreach (var res in qry10)
{
var qrry = from igs in dset.ItemGroupStock.AsEnumerable()
where igs.ItemGroupID == res.IG
select igs;
foreach (var res2 in qrry)
{
res2.ClosingStock = res2.OpeningStock - res.TOTAL;
}
}
ViewState.Add("vsDset", dset);
rdoc.Load(Server.MapPath("~/Report/WSDayBookRpt.rpt"));
rdoc.SetDataSource(dset);
CrystalReportViewer1.DataBind();
CrystalReportViewer1.ReportSource = rdoc;
CrystalReportViewer1.RefreshReport();
Session["CRpt"]= rdoc;
}
}
Your CrystalReportViewer control is probably loosing the ReportDocument on PostBack. You will have to do it as follows:
Declare your ReportDocument object at page level
ReportDocument rdoc = new ReportDocument();
In the Page_Load event load your ReportDocument from Session object
protected void Page_Load(System.Object sender, System.EventArgs e) {
if (Page.IsPostBack) {
if (Session.Item("CRpt") != null) {
rdoc = Session.Item("CRpt");
}
CrystalReportViewer1.ReportSource = rdoc;
CrystalReportViewer1.RefreshReport();
} else {
'NOT POSTBACK
if (Session.Item("CRpt") != null) {
Session.Remove("CRpt");
}
Session.Add("CRpt", rdoc);
}
}
On the btnSubmit create and add your ReportDocument to Session
protected void btnSubmit_Click(object sender, EventArgs e) {
//Populate Report
rdoc.SetDataSource(dset);
CrystalReportViewer1.ReportSource = rdoc;
Session.Add("CRpt", rdoc);
}
Besides this if you need specific help in setting LogOnInfo then you may also check this SO post Report asking for database login on setting DataTable as DataSource