Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
Hi Everyone,

I am generating a rdlc report in my project. Here dataset not reading values from database. But when I execute Sql query values are coming.P
lease help me to fix this issue.
 
Here is My Code:
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.Reporting.WebForms;
using System.Data.SqlClient;
using Microsoft.VisualBasic;
 
public partial class InvRep : System.Web.UI.Page
{
    EMS ems = new EMS();
    string con = ConfigurationManager.ConnectionStrings["constr"].ToString();
 

    protected void Page_Load(object sender, EventArgs e)
    {
 
        try
        {
            if (!IsPostBack)
            {
 
                SqlCommand cmd = new SqlCommand("spGettblInvoiceReport");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@invno", SqlDbType.Int).Value = Convert.ToInt16(Request.QueryString["invno"]);
                cmd.Parameters.Add("@companyid", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"].ToString());
                DataSet dsInv = ems.Make_Ds(cmd, con);
 
                SqlCommand cmd1 = new SqlCommand("spGetTblInvoicedetailsReport");
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.Parameters.Add("@invno", SqlDbType.Int).Value = Convert.ToInt16(Request.QueryString["invno"]);
                cmd1.Parameters.Add("@companyId", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"].ToString());
 

                DataSet dsPRD = ems.Make_Ds(cmd1, con);
 
                DataSet dsPRD1 = new DataSet();
                dsPRD1.Tables.Add("dtinvoicedetails");
 
                dsPRD1.Tables[0].Columns.Add("catalogno");
                dsPRD1.Tables[0].Columns.Add("itemcode");
                dsPRD1.Tables[0].Columns.Add("unit");
                dsPRD1.Tables[0].Columns.Add("qty");
                dsPRD1.Tables[0].Columns.Add("rate");
                dsPRD1.Tables[0].Columns.Add("value");
                dsPRD1.Tables[0].Columns.Add("datacolumn1");
                dsPRD1.Tables[0].Columns.Add("serialnos");
                dsPRD1.Tables[0].Columns.Add("partno");
 
                dsPRD1.Tables[0].Columns.Add("itemno");
 

                SqlCommand cmd2 = new SqlCommand("spGetTblInvoiceTaxReport");
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.Parameters.Add("@invno", SqlDbType.Int).Value = Convert.ToInt16(Request.QueryString["invno"]);
                cmd2.Parameters.Add("@companyid", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"].ToString());
                DataSet dsTax = ems.Make_Ds(cmd2, con);
 

                DataSet dsmm = new DataSet();
                dsmm.Tables.Add(dsInv.Tables[0].Copy());
                dsmm.Tables[0].TableName = "tblInvoiceReport";
 

 
                for (int z = 0; z < dsPRD.Tables[0].Rows.Count; z++)
                {
                    SqlCommand prdcmd = new SqlCommand("spGetTblProductDetCatlog");
                    prdcmd.CommandType = CommandType.StoredProcedure;
                    prdcmd.Parameters.Add("@catalogno", SqlDbType.VarChar).Value = dsPRD.Tables[0].Rows[z]["catalogno"].ToString();
                    DataSet ds = ems.Make_Ds(prdcmd, con);
                    string catalogItems = "";
                    string serialnos = "";
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        string fldId = ds.Tables[0].Rows[0]["featureid"].ToString();
                        string lvlname = ems.ShowLvlName(fldId, con);
                        string getsql = "select " + lvlname.Replace(" ", "") + "make,originalprice from classtransactionnew where productdesc='" + dsPRD.Tables[0].Rows[z]["catalogno"].ToString() + "'";
                        string[] items = lvlname.Split(',');
                        int i;
                        DataSet getds = new DataSet();
                        for (i = 0; i < Information.UBound(items, 1); i++)
                        {
 
                            //lbl1.Text = items[i].ToString();

 
                            SqlConnection conn = ems.Open_Connection(con);
                            SqlDataAdapter da = new SqlDataAdapter(getsql, conn);
 
                            da.Fill(getds);
 
                            SqlCommand cmdtab = new SqlCommand("spGetTableNameDescr");
                            cmdtab.CommandType = CommandType.StoredProcedure;
                            cmdtab.Parameters.Add("@tname", SqlDbType.VarChar).Value = items[i].Replace(" ", "").ToString();
                            cmdtab.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt16(getds.Tables[0].Rows[0][items[i].Replace(" ", "")]);
                            string descr = Convert.ToString(ems.Get_SqlValue(cmdtab, con));
                            //lbl2.Text = descr;

                            if (items[i].Replace(" ", "").ToUpper().ToString() == "MODELNUMBER")
                            {
                                catalogItems += descr + Environment.NewLine;
                            }
                            else
                            {
 
                                catalogItems += items[i].ToString() + ":" + descr + ",";
                            }
                        }
 
                        DataRow dr1 = dsPRD1.Tables[0].NewRow();
                        dr1[0] = dsPRD.Tables[0].Rows[z]["catalogno"].ToString();
                        dr1[1] = dsPRD.Tables[0].Rows[z]["itemcode"].ToString();
                        dr1[2] = dsPRD.Tables[0].Rows[z]["unit"].ToString();
                        dr1[3] = dsPRD.Tables[0].Rows[z]["qty"].ToString();
                        dr1[4] = dsPRD.Tables[0].Rows[z]["rate"].ToString();
                        dr1[5] = dsPRD.Tables[0].Rows[z]["value"].ToString();
                        //if(dsPRD.Tables[0].Rows[z]["serialnos"].ToString()!="")
                        // serialnos="Valve Sl No. :"+dsPRD.Tables[0].Rows[z]["serialnos"].ToString();
                        dr1[6] = "Model No:" + dsPRD.Tables[0].Rows[z]["catalogno"].ToString() + Environment.NewLine + catalogItems.Substring(0, catalogItems.Length - 1) + Environment.NewLine + serialnos;
                        dr1[7] = dsPRD.Tables[0].Rows[z]["serialnos"].ToString();
                        dr1[8] = dsPRD.Tables[0].Rows[z]["partno"].ToString();
                        dr1[9] = ++z;
                        dsPRD1.Tables[0].Rows.Add(dr1);
 

                    }
                }
 
                dsmm.Tables.Add(dsPRD1.Tables[0].Copy());
 
                dsmm.Tables[1].TableName = "tblInvoiceDetails";
 
                dsmm.Tables.Add(dsTax.Tables[0].Copy());
                dsmm.Tables[2].TableName = "tblInvoiceTax";
 

                dsmm.WriteXmlSchema("InvRep.xsd"); // proper dataset xsd file namePurchaseRequest_TblPr
                //ReportDataSource datasource = new ReportDataSource("PurchaseRequest_tblpr", dsmm.Tables[0]);
                ReportDataSource datasource = new ReportDataSource("InvRep_tblInvoiceReport", dsmm.Tables[0]);//proper datasetname
                ReportDataSource datasource1 = new ReportDataSource("InvRep_tblInvoiceDetails", dsmm.Tables[1]);//proper datasetname
                ReportDataSource datasource2 = new ReportDataSource("InvRep_tblInvoiceTax", dsmm.Tables[2]);
                ReportViewer.LocalReport.DataSources.Clear();
                ReportViewer.LocalReport.DataSources.Add(datasource);
                ReportViewer.LocalReport.DataSources.Add(datasource1);
                ReportViewer.LocalReport.DataSources.Add(datasource2);
                ReportViewer.LocalReport.ReportPath = Server.MapPath("Reports//InvRep1.rdlc");
                if (dsmm.Tables[0].Rows.Count == 0)
                {
                    Response.Write("Sorry, no Data under this category!");
                }
 
                
                ReportViewer.LocalReport.Refresh();
            }
 
        }
        catch (SqlException sqlex)
        {
 
            Logger.Log("Error while Binding data " + sqlex.Message + " Stack Trace: " + sqlex.StackTrace, LogLevel.ERROR);
        }
    }
    protected void lnkback_Click(object sender, EventArgs e)
    {
        if (Request.QueryString["dash"] != null)
        {
            Response.Redirect("Customerrep.aspx");
        }
        else if (Request.QueryString["dash"] == null)
        {
            Response.Redirect("Invoice.aspx");
        }
    }
    protected void logout_Click(object sender, EventArgs e)
    {
        Session.Abandon();
        Response.Redirect("index.aspx");
    }
 }
Posted 20-Nov-12 15:58pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Please note that you are not opening connection in your program. Update the below code
 
SqlConnection conn = new SqlConnection(con);
conn.Open();
SqlCommand cmd = new SqlCommand("spGettblInvoiceReport");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@invno", SqlDbType.Int).Value = Convert.ToInt16(Request.QueryString["invno"]);
cmd.Parameters.Add("@companyid", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"].ToString());
DataSet dsInv = ems.Make_Ds(cmd, conn);

SqlCommand cmd1 = new SqlCommand("spGetTblInvoicedetailsReport");
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.Add("@invno", SqlDbType.Int).Value = Convert.ToInt16(Request.QueryString["invno"]);
cmd1.Parameters.Add("@companyId", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"].ToString());
DataSet dsPRD = ems.Make_Ds(cmd1, conn);
 
And your coading is very bad. You are using values from query string and session but even not checking for null values.
Your function should not be this big.
 
Follow the below link for coading standard.

http://www.tiobe.com/content/paperinfo/gemrcsharpcs.pdf[^]
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I am opening connection using this code.(AppCode)
EMS ems = new EMS();
string con = ConfigurationManager.ConnectionStrings["constr"].ToString();
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 540
1 Sergey Alexandrovich Kryukov 407
2 Yogesh Kumar Tyagi 384
3 Prakriti Goyal 280
4 Maciej Los 205
0 OriginalGriff 6,632
1 Sergey Alexandrovich Kryukov 5,429
2 Maciej Los 3,474
3 Peter Leow 3,309
4 DamithSL 2,495


Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 21 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100