3 Replies Latest reply on Dec 12, 2007 3:24 PM by 24208

    ORA-01722 running sql in C# 2.0, but works in Apex

    FunGeek
      Hi,
      I am using VS2005 with C# and .NET 2.0 and have an Oracle 10g XE database.

      When I run the following query in Apex then it works:
      SELECT PIT.TYPE_VALUE AS "Entity Type", PIT.PILE_ITEM_TYPE_ID, RAWTOHEX(PI.PILE_ITEM_VALUE) AS "Entity Value", COUNT(PI.PILE_ITEM_VALUE) AS "# Occurrences", COUNT(DISTINCT XF.IMAGE_ID) AS "# Images" FROM PILE_ITEM_TYPE PIT, PILE_ITEM PI, XFILE XF
      WHERE PIT.PILE_ITEM_TYPE_ID = PI.PILE_ITEM_TYPE_ID AND PI.XFILE_ID = XF.XFILE_ID AND (PI.PILE_ITEM_TYPE_ID IN (1,3))
      GROUP BY PIT.TYPE_VALUE, PIT.PILE_ITEM_TYPE_ID, RAWTOHEX(PI.PILE_ITEM_VALUE) ORDER BY "Entity Type"

      I try and run the same query in my C# app by replacing PI.PILE_ITEM_TYPE_ID IN (1,3) with PI.PILE_ITEM_TYPE_ID IN (:selectedPileTypes), where selectedPileTypes is a string. If selectedPileTypes contains one number, then no problem. Otherwise I get the ORA-01722: invalid number error.

      I had this working in a DataSet, but we had to remove it so we could bind and rebind our datagridviews at run-time. I tried to replicate the settings of the Fill method (parameter.DbType = AnsiString ), but it doesn't help. Below is the current code I'm running:

      SummaryDataGridView.DataSource = currDB.GetHitSummariesData(selectedPileTypes);

      public DataTable GetHitSummariesData(string selectedPileTypes)
      {
      DataTable table = new DataTable();
      table.Locale = System.Globalization.CultureInfo.InvariantCulture;
      OracleDataAdapter adapter = new OracleDataAdapter();
      OracleParameter param1 = new OracleParameter();
      param1.ParameterName = ":selectedPilesString";
      param1.DbType = DbType.AnsiString;
      param1.Value = selectedPileTypes;
      param1.Direction = ParameterDirection.Input;


      string commmandStr = "SELECT PIT.TYPE_VALUE AS \"Entity Type\", PIT.PILE_ITEM_TYPE_ID,"
      + " RAWTOHEX(PI.PILE_ITEM_VALUE) AS \"Entity Value\", COUNT(PI.PILE_ITEM_VALUE)"
      + " AS \"# Occurrences\", COUNT(DISTINCT XF.IMAGE_ID) AS \"# Images\""
      + " FROM PILE_ITEM_TYPE PIT, PILE_ITEM PI, XFILE XF"
      + " WHERE PIT.PILE_ITEM_TYPE_ID = PI.PILE_ITEM_TYPE_ID AND PI.XFILE_ID = XF.XFILE_ID AND"
      + " (PI.PILE_ITEM_TYPE_ID IN (:selectedPilesString))"
      + " GROUP BY PIT.TYPE_VALUE, PIT.PILE_ITEM_TYPE_ID, RAWTOHEX(PI.PILE_ITEM_VALUE)"
      + " ORDER BY \"Entity Type\"";
      adapter.SelectCommand = new OracleCommand(commmandStr, currDBConn);

      try
      {
      adapter.SelectCommand.Parameters.Add(param1);
      adapter.Fill(table);
      .... }}

      Has anybody run across a similar situation? Or can someone suggest something to try?

      TIA,
      Theresa