locked
All Dataset return 0 records after adding datatable to DAtaSet.xsd in VS2012 RRS feed

  • Question

  • User989035446 posted

    Hi All,

    I encounter a very very weird error which i do not face in Visual Studio 2010.

    All the SQL result return in dataset format has 0 row even if it should return record after adding table to DataSet.xsd.

    Below is my code which should return me a row but after adding a datable to Dataset.xsd it returns me 0 records.

    protected void Page_Load(object sender, EventArgs e)
        {
    
            DataSet dsUser = new DataSet();
            Int32  icount;
    
            dsUser = SearchUser("ELIM", "", "", "", "ALL", "ALL");
            icount = dsUser.Tables[0].Rows.Count;
    
    
        }
    
     private DataSet SearchUser(String vstrNTAcc, String vstrEmpNo, String vstrName, String vstrRole, String vstrStatus, String vstrCertApprv)
        {
            DataSet dsUser = new DataSet();
            String oradb = "";
    
            oradb = ConfigurationManager.AppSettings["BARWEBConnString"].ToString();
            OracleConnection oconn = new OracleConnection(oradb);
    
            try
            {
                vstrNTAcc = vstrNTAcc.ToUpper();
                vstrEmpNo = vstrEmpNo.ToUpper();
                vstrName = vstrName.ToUpper();
                vstrRole = vstrRole.ToUpper();
                vstrStatus = vstrStatus.ToUpper();
    
                if (vstrNTAcc == "")
                {
                    vstrNTAcc = "%";
                }
    
    
                if (vstrEmpNo == "")
                {
                    vstrEmpNo = "%";
                }
    
                if (vstrName == "")
                {
                    vstrName = "%";
                }
    
                if (vstrRole == "ALL")
                {
                    vstrRole = "%";
                }
                else
                {
                    vstrRole = "%" + vstrRole + '%';
                }
    
                if (vstrStatus == "ALL")
                {
                    vstrStatus = "%";
                }
    
                if (vstrCertApprv == "ALL")
                {
                    vstrCertApprv = "%";
                }
    
                OracleCommand ocmd = new OracleCommand();
                oconn.Open();
                ocmd.Connection = oconn;
                ocmd.CommandType = CommandType.StoredProcedure;
                ocmd.CommandText = "BAR_WEB.S_COC_GET_USER";
    
                OracleParameter OPNTAcc = new OracleParameter();
                OPNTAcc.Direction = ParameterDirection.Input;
                OPNTAcc.Value = vstrNTAcc;
                ocmd.Parameters.Add(OPNTAcc);
    
                OracleParameter OPEmpNo = new OracleParameter();
                OPEmpNo.Direction = ParameterDirection.Input;
                OPEmpNo.Value = vstrEmpNo;
                ocmd.Parameters.Add(OPEmpNo);
    
                OracleParameter OPName = new OracleParameter();
                OPName.Direction = ParameterDirection.Input;
                OPName.Value = vstrName;
                ocmd.Parameters.Add(OPName);
    
    
                OracleParameter OPRole = new OracleParameter();
                OPRole.Direction = ParameterDirection.Input;
                OPRole.Value = vstrRole;
                ocmd.Parameters.Add(OPRole);
    
                OracleParameter OPCertApprv = new OracleParameter();
                OPCertApprv.Direction = ParameterDirection.Input;
                OPCertApprv.Value = vstrCertApprv;
                ocmd.Parameters.Add(OPCertApprv);
    
                OracleParameter OPStatus = new OracleParameter();
                OPStatus.Direction = ParameterDirection.Input;
                OPStatus.Value = vstrStatus;
                ocmd.Parameters.Add(OPStatus);
    
                OracleParameter OPRefCursor = new OracleParameter("OPRefCursor", OracleDbType.RefCursor);
                OPRefCursor.Direction = ParameterDirection.Output;
                ocmd.Parameters.Add(OPRefCursor);
    
                OracleDataAdapter da = new OracleDataAdapter(ocmd);
                da.Fill(dsUser);
    
                return dsUser;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                oconn.Close();
            }
    
        }

    Below is how i add the databale to the DAtaSet.xsd.This datatable is a very plain that i add only column to it without having any connection.

    Is there any special configuration required for VS2012? I need the datatable inside Dataset.xsd because i want to use it in reportviewer.

    Help Please!!

    Saturday, January 18, 2014 10:15 AM

All replies

  • User-186422134 posted

    You aslo need to read it into a reader. Like this

          oradb = ConfigurationManager.AppSettings["BARWEBConnString"].ToString();
            OracleConnection oconn = new OracleConnection(oradb);

    OracleReader Reader;
    Reader = oconn.ExecuteReader();
    if(Reader.Read())
    {
    vstrNTAcc = vstrNTAcc[0].ToUpper();
    vstrEmpNo
    = vstrEmpNo[1].ToUpper();
    vstrName
    = vstrName[2].ToUpper();
    vstrRole
    = vstrRole[3].ToUpper();
    vstrStatus
    = vstrStatus[4].ToUpper();

    }
    then you can validate
    Sunday, January 19, 2014 1:00 PM
  • User521424079 posted

    Hi johnsonlim,

    First, you need to check the database have records, and then in your image, I can only see you are add the table to dataset.xsd, but I have not found you add the record to the table.

    Maybe you are forgotten to insert records in this table.

    Regards.

    Monday, January 20, 2014 5:19 AM
  • User989035446 posted

    Hi msogun,

    I have tried your suggestion and the result is extremely weird because using Oracle Data Reader, it do return me the records,but if by using dataset, it still return me no record.

     DataSet dsUser = new DataSet();
            String oradb = "";
    
            oradb = ConfigurationManager.AppSettings["BARWEBConnString"].ToString();
            OracleConnection oconn = new OracleConnection(oradb);
    
            try
            {
    
         
    
              
    
                    vstrNTAcc = vstrNTAcc.ToUpper();
                    vstrEmpNo = vstrEmpNo.ToUpper();
                    vstrName = vstrName.ToUpper();
                    vstrRole = vstrRole.ToUpper();
                    vstrStatus = vstrStatus.ToUpper();
    
                    if (vstrNTAcc == "")
                    {
                        vstrNTAcc = "%";
                    }
    
    
                    if (vstrEmpNo == "")
                    {
                        vstrEmpNo = "%";
                    }
    
                    if (vstrName == "")
                    {
                        vstrName = "%";
                    }
    
                    if (vstrRole == "ALL")
                    {
                        vstrRole = "%";
                    }
                    else
                    {
                        vstrRole = "%" + vstrRole + '%';
                    }
    
                    if (vstrStatus == "ALL")
                    {
                        vstrStatus = "%";
                    }
    
                    if (vstrCertApprv == "ALL")
                    {
                        vstrCertApprv = "%";
                    }
    
                    OracleCommand ocmd = new OracleCommand();
                    oconn.Open();
                    ocmd.Connection = oconn;
                    ocmd.CommandType = CommandType.StoredProcedure;
                    ocmd.CommandText = "BAR_WEB.S_COC_GET_USER";
    
                    OracleParameter OPNTAcc = new OracleParameter();
                    OPNTAcc.Direction = ParameterDirection.Input;
                    OPNTAcc.Value = vstrNTAcc;
                    ocmd.Parameters.Add(OPNTAcc);
    
                    OracleParameter OPEmpNo = new OracleParameter();
                    OPEmpNo.Direction = ParameterDirection.Input;
                    OPEmpNo.Value = vstrEmpNo;
                    ocmd.Parameters.Add(OPEmpNo);
    
                    OracleParameter OPName = new OracleParameter();
                    OPName.Direction = ParameterDirection.Input;
                    OPName.Value = vstrName;
                    ocmd.Parameters.Add(OPName);
    
    
                    OracleParameter OPRole = new OracleParameter();
                    OPRole.Direction = ParameterDirection.Input;
                    OPRole.Value = vstrRole;
                    ocmd.Parameters.Add(OPRole);
    
                    OracleParameter OPCertApprv = new OracleParameter();
                    OPCertApprv.Direction = ParameterDirection.Input;
                    OPCertApprv.Value = vstrCertApprv;
                    ocmd.Parameters.Add(OPCertApprv);
    
                    OracleParameter OPStatus = new OracleParameter();
                    OPStatus.Direction = ParameterDirection.Input;
                    OPStatus.Value = vstrStatus;
                    ocmd.Parameters.Add(OPStatus);
    
                    OracleParameter OPRefCursor = new OracleParameter("OPRefCursor", OracleDbType.RefCursor);
                    OPRefCursor.Direction = ParameterDirection.Output;
                    ocmd.Parameters.Add(OPRefCursor);
    
                    OracleDataReader reader = ocmd.ExecuteReader();
                    while (reader.Read())
                    {
    
                        string a = "asdsd";
    
                    }
    
                    OracleDataAdapter da = new OracleDataAdapter(ocmd);
                    da.Fill(dsUser);
    
    
                    
    
                    
    
                    return dsUser;
                
                
    
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                oconn.Close();
            }
    
        }

    Do you have any idea on this?

    Hi All,

    Have you come over this?Do you guys have any idea?Help Please!!

    Monday, January 20, 2014 9:14 PM
  • User989035446 posted

    Hi BetsyASP,

    It is 2 diffrent issus. The datatable is added to the DataSet.xsd is because i need to use it in ReportViewer.

    But after the datatable was added, all the query to DB in my program which return in dataSet format returns me zero records regradless i use the Datatable in the Dataset.xsd or not.

    For example: the DataTable in the DataSet.xsd has thenname of DataSet1 and has column DataColumn1,DataColumn2,DataColumn3.

    But it is not used in the qurey to DB, but somehow the Dataset used in the query is affected to always return 0 records.

    I have check that there is records in the database and very sure on this because all the query to DB used in the program are in DataSet format and all returning 0 record.

    I find out that by using OracleDataReader, it is able to return me the query result.Only that when datatable was added to DAtaset.xsd. all dataset is not working.

    Below is the code.

    DataSet dsUser = new DataSet();
            String oradb = "";
    
            oradb = ConfigurationManager.AppSettings["BARWEBConnString"].ToString();
            OracleConnection oconn = new OracleConnection(oradb);
    
            try
            {
    
         
    
              
    
                    vstrNTAcc = vstrNTAcc.ToUpper();
                    vstrEmpNo = vstrEmpNo.ToUpper();
                    vstrName = vstrName.ToUpper();
                    vstrRole = vstrRole.ToUpper();
                    vstrStatus = vstrStatus.ToUpper();
    
                    if (vstrNTAcc == "")
                    {
                        vstrNTAcc = "%";
                    }
    
    
                    if (vstrEmpNo == "")
                    {
                        vstrEmpNo = "%";
                    }
    
                    if (vstrName == "")
                    {
                        vstrName = "%";
                    }
    
                    if (vstrRole == "ALL")
                    {
                        vstrRole = "%";
                    }
                    else
                    {
                        vstrRole = "%" + vstrRole + '%';
                    }
    
                    if (vstrStatus == "ALL")
                    {
                        vstrStatus = "%";
                    }
    
                    if (vstrCertApprv == "ALL")
                    {
                        vstrCertApprv = "%";
                    }
    
                    OracleCommand ocmd = new OracleCommand();
                    oconn.Open();
                    ocmd.Connection = oconn;
                    ocmd.CommandType = CommandType.StoredProcedure;
                    ocmd.CommandText = "BAR_WEB.S_COC_GET_USER";
    
                    OracleParameter OPNTAcc = new OracleParameter();
                    OPNTAcc.Direction = ParameterDirection.Input;
                    OPNTAcc.Value = vstrNTAcc;
                    ocmd.Parameters.Add(OPNTAcc);
    
                    OracleParameter OPEmpNo = new OracleParameter();
                    OPEmpNo.Direction = ParameterDirection.Input;
                    OPEmpNo.Value = vstrEmpNo;
                    ocmd.Parameters.Add(OPEmpNo);
    
                    OracleParameter OPName = new OracleParameter();
                    OPName.Direction = ParameterDirection.Input;
                    OPName.Value = vstrName;
                    ocmd.Parameters.Add(OPName);
    
    
                    OracleParameter OPRole = new OracleParameter();
                    OPRole.Direction = ParameterDirection.Input;
                    OPRole.Value = vstrRole;
                    ocmd.Parameters.Add(OPRole);
    
                    OracleParameter OPCertApprv = new OracleParameter();
                    OPCertApprv.Direction = ParameterDirection.Input;
                    OPCertApprv.Value = vstrCertApprv;
                    ocmd.Parameters.Add(OPCertApprv);
    
                    OracleParameter OPStatus = new OracleParameter();
                    OPStatus.Direction = ParameterDirection.Input;
                    OPStatus.Value = vstrStatus;
                    ocmd.Parameters.Add(OPStatus);
    
                    OracleParameter OPRefCursor = new OracleParameter("OPRefCursor", OracleDbType.RefCursor);
                    OPRefCursor.Direction = ParameterDirection.Output;
                    ocmd.Parameters.Add(OPRefCursor);
    
                    OracleDataReader reader = ocmd.ExecuteReader();
                    while (reader.Read())
                    {
    
                        string a = "asdsd";
    
                    }
    
                    OracleDataAdapter da = new OracleDataAdapter(ocmd);
                    da.Fill(dsUser);
    
    
                    
    
                    
    
                    return dsUser;
                
                
    
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                oconn.Close();
            }
    
        }

    Hi BetsyASP do you have any idea?

    Hi all , do you have any idea?

    Help Please!!

    Monday, January 20, 2014 9:50 PM