Getting “Database Logon Error” in Crystal Report W

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;
}
}

标签： c# asp.net crystal-reports dataset

1条回答

贪生不怕死

2楼-- · 2019-06-02 18:47

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

0人赞添加讨论(0) 举报

Getting “Database Logon Error” in Crystal Report W

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间