DataSet Not Reading values

Question

1.00/5 (1 vote)

See more:

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:

C#

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

Member 9345484

Updated 20-Nov-12 20:03pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v3

Add a Solution

2 solutions

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[^]

Posted 20-Nov-12 20:17pm

Mohd. Mukhtar

Updated 20-Nov-12 20:18pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 9345484 · Accepted Answer · 2012-11-20T21:58:00

Solution 2

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

Posted 20-Nov-12 21:58pm

Member 9345484