locked
Delete a row from a Oracle Server table RRS feed

  • Question

  • User-1790113996 posted

    'm trying to simply delete a full row from my Oracle Server database table using a c#.

    So far none of my attempts have succeeded. This is what I'm trying to do

    What's wrong with my code

    I want automatic delete with zero value  each row

     public void DeleteZeroRow(ItemModel Item)
            {
                Conn = ORCONN.con;
                if (Conn.State != ConnectionState.Open)
                {
                    Conn.Open();
                }
    
                try
                {
                    cmd.Connection = Conn;
    
                    var query = "DELETE FROM SS.INVENTORY WHERE ONHANDQTY = 0" ;
    
    
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    Conn.Close();
                }
    
            }

    Tuesday, April 25, 2017 8:13 AM

All replies

  • User753101303 posted

    Hi,

    And what happens? Don't you have an error message? If the value returned by ExecuteNonQuery is 0, you have no rows matching this criteria.

    Please never post some code that "doesn't work" without telling explicitely which behavior you see. Or maybe a 0 vs NULL confusion ?

    Tuesday, April 25, 2017 8:20 AM
  • User-1838255255 posted

    Hi Cloudfiers,

    According to your description, as far as I know, you want to delete row with null values. But form your code, I think you want to delete the row code when ONHANDQTY=0.

    You could try like this:

    DELETE FROM table WHERE ONHANDQTY IS NULL;

    Here is a complete code to operate delete command in ADO.NET Oracle, please check: 

    Do a delete command to Oracle database :

    http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/DoadeletecommandtoOracledatabase.htm 

    Also I hope you could add a break point to check what error you meet or what happened of your code.

    Best Regards,

    Eric Du

    Wednesday, April 26, 2017 3:22 AM
  • User-1790113996 posted
    What wrong with this code?,

    I cannot have delete with value on Zero quantity

    public
      void DeleteRow()       {           OracleConnection Conn = new OracleConnection();           string connString = "Data Source=PH;User Id=PH01;Password=PH1234;";           if (Conn.State != ConnectionState.Open)           {               try               {                   Conn.ConnectionString = connString;                   Conn.Open();                                 }               catch (Exception ex)               {                                  }           }           if (Conn.State == ConnectionState.Open)           {               string sqlDelete = "DELETE FROM PH.INVENTORY WHERE ONHANDQTY = 0";               OracleCommand cmdDelete = new OracleCommand();               cmdDelete.CommandText = sqlDelete;               cmdDelete.Connection = Conn;               OracleParameter pPlayerNum = new OracleParameter();               pPlayerNum.DbType = DbType.Decimal;               pPlayerNum.Value = 0;               pPlayerNum.ParameterName = "0";               cmdDelete.Parameters.Add(pPlayerNum);               cmdDelete.ExecuteNonQuery();               cmdDelete.Dispose();           }       }
    Wednesday, April 26, 2017 6:11 AM
  • User269602965 posted

    best to use words for bind variables and not numbers

    notice use of COLON symbol in the WHERE clause just before the bindvariable name  

               string sqlDelete = "DELETE FROM PH.INVENTORY WHERE ONHANDQTY = :bindNumber";
                  OracleCommand cmdDelete = new OracleCommand();
                  cmdDelete.CommandText = sqlDelete;
                  cmdDelete.Connection = Conn;

                  OracleParameter pPlayerNum = new OracleParameter();
                  pPlayerNum.DbType = DbType.Decimal;
                  pPlayerNum.Value = 0;
                  pPlayerNum.ParameterName = "bindNumber";

    Friday, May 12, 2017 8:48 PM