2 Replies Latest reply: May 8, 2009 1:45 PM by markr3 RSS

    Object reference error on ExecuteReader()

    markr3
      I have been trying to get the following code to work but keep getting a object reference error on the ExecuteReader command. From what I can tell, it has to do with the parameterized query not properly adding the value of the parameter to the OracleCommand. Any ideas on how to get this to work? The goal is that if I pass in a value > 0, it only runs the query for that one record but if the value = 0, it runs for all records.

      Object reference not set to an instance of an object.
      Oracle.DataAccess.Client.OracleDataReader ExecuteReader(Boolean, Boolean, System.Data.CommandBehavior)
      System.Collections.ListDictionaryInternal

      at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
      at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
      at UGA.Parking.DataLoad.CustFldUpdt.Class1.UpdateFlexCustomFields(Int32 entUID)
      Oracle.DataAccess

      =========================

      Here's the code:

      public static void UpdateFlexCustomFields(int entUID)
      +{+

      string errorMsg = DateTime.Now.ToString() " -- Version 1.0\r\n\r\n";+
      int updtCount = 0;
      int apiErrors = 0;
      int updtErrors = 0;
      string lineError = "0";

      string OutputFile = @"D:\UGADataXfer\LogFiles\ps_Cust_Load_Summary.lst";
      try
      +{+
      +// Get a connection to the db+
      +// context connection is used in a stored procedure+
      OracleConnection con = new OracleConnection();
      con.ConnectionString = "context connection=true";
      if (OracleConnection.IsAvailable)
      +{+
      errorMsg = "Connected to Oracle...\r\n";+
      con.Open();

      +#region Loop through customers and update as needed+

      +// Create command and parameter objects+
      using (OracleCommand cmd = con.CreateCommand())
      +{+
      cmd.CommandType = CommandType.Text;
      if (entUID > 0)
      +{+
      cmd.CommandText = "select pcc_ent_uid from ugaps.pa_cust_sel_ctl where pcc_ent_uid=:ENT_UID";
      cmd.Parameters.Add(":ENT_UID", OracleDbType.Int32, entUID, ParameterDirection.Input);
      +}+
      else
      +{+
      cmd.CommandText = "select pcc_ent_uid from ugaps.pa_cust_sel_ctl";
      +}+


      +// get a data reader+
      OracleDataReader rdr = cmd.ExecuteReader();

      +// get the country name from the data reader+
      while (rdr.Read())
      +{+
      +//Do stuff+
      +}+
      +}+


      Thanks in advance!
        • 1. Re: Object reference error on ExecuteReader()
          markr3
          It appears that .NET refuses to process parameterized queries no matter how I write them. I can hard code a value and it works but if I put a parameterized value in, it doesn't.

          Here's some additional code that I've been working on:

          OracleCommand updtCmd = con.CreateCommand();
          updtCmd.CommandType = CommandType.Text;

          Does not work:
          ====================
          updtCmd.CommandText = "select nvl(VCSP_CURR_SPECIALPARKING,'_') VCSP_CURR_SPECIALPARKING, nvl(VCSP_NEXT_SPECIALPARKING,'_') VCSP_NEXT_SPECIALPARKING from UGAPS.VPS_CMPR_SPECIALPARKING where VCSP_ENT_UID = :VCSP_ENT_UID";

          OracleParameter op = new OracleParameter("VCSP_ENT_UID", OracleDbType.Decimal);
          op.Value = "36813";
          updtCmd.Parameters.Add(op);

          or

          updtCmd.Parameters.Add(":VCSP_ENT_UID", OracleDbType.Int32, "36813", ParameterDirection.Input);

          or

          updtCmd.Parameters.Add(":VCSP_ENT_UID", OracleDbType.Int32, 36813, ParameterDirection.Input);

          or

          updtCmd.Parameters.Add(":VCSP_ENT_UID", OracleDbType.Decimal, "36813", ParameterDirection.Input);

          or

          updtCmd.Parameters.Add(":VCSP_ENT_UID", OracleDbType.Decimal, "36813", ParameterDirection.Input);

          ====================





          Does work:
          ====================
          updtCmd.CommandText = "select nvl(VCSP_CURR_SPECIALPARKING,'_') VCSP_CURR_SPECIALPARKING, nvl(VCSP_NEXT_SPECIALPARKING,'_') VCSP_NEXT_SPECIALPARKING from UGAPS.VPS_CMPR_SPECIALPARKING where VCSP_ENT_UID =36813";
          ====================

          OracleDataReader updtRdr = updtCmd.ExecuteReader();
          Hashtable updtHash = new Hashtable();
          updtRdr.Read();
          if (updtRdr.HasRows)
          {
          //if (updtRdr.GetString(0).ToUpper().Trim() != updtRdr.GetString(1).ToUpper().Trim())
          //{
          // updtHash.Add("SPECIALPARKING", updtRdr.GetString(0).ToUpper().Trim());
          //}
          errorMsg += "Read...";
          }
          else
          {
          errorMsg += "No read...";
          }
          • 2. Re: Object reference error on ExecuteReader()
            markr3
            Apparently this was a bad install of the ODAC. I uninstalled and reinstalled the ODAC and the problem was resolved.