The table is called PH.INVENTORY, the column name is ONHANDQUANTITY
I want to run a query which deletes all rows in PH.INVENTORY table in which the ONHANDQUANTITYvalue is 0 (All rows contain a 0 value for the column ONHANDQUANTITY)
how can I add in my delete query?
separate thanks..
public void Delete(ItemModel Item){
Conn = ORCONN.con;if(Conn.State != ConnectionState.Open){
Conn.Open();}
try
{
cmd.Connection = Conn;
var query ="DELETE FROM PH.INVENTORY WHERE INVNO LIKE '"+ Item.INVNO.Trim()+"%'";
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();}
finally
{
Conn.Close();}}
It's best to tell what happens. Do you have an error message (for example could it be that you try to use LIKE on INVNO which might be a numeric column, or it could be that ONHANDQUANTITY is actually NULL rarther than 0 and so it doesn't delete the row you
think should be deleted etc...)
You perhaps want WHERE etc... AND (ONHANQUANTITY=0 OR ONHANDQUANTITY IS NULL)
It's best to tell what happens. Do you have an error message (for example could it be that you try to use LIKE on INVNO which might be a numeric column, or it could be that ONHANDQUANTITY is actually NULL rarther than 0 and so it doesn't delete the row you
think should be deleted etc...)
You perhaps want WHERE etc... AND (ONHANQUANTITY=0 OR ONHANDQUANTITY IS NULL)
This is the right method? deletion together of INVO AND ONHANDQTY
var query ="DELETE FROM PH.INVENTORY WHERE INVNO LIKE '"+ Item.INVNO.Trim() +"%' AND (ONHANQUANTITY=0 OR ONHANDQUANTITY IS NULL)";
cmd.CommandText = query;
cmd.CommandType =CommandType.Text;
cmd.ExecuteNonQuery();
It seems it should work. If not, once again please tell what happens. Do you have an error message? Also ExecuteNonQuery returns the number of affected rows so if 0 it is definitively that the WHERE clause doesn't match any row.
Not directly related but using parameters is preffered over string concatenation (for example your code will fail if the user enters ' and is subject to
https://en.wikipedia.org/wiki/SQL_injection)
Member
36 Points
256 Posts
How can perform to delete all rows within a table in a oracle database, which value = 0 for one o...
Feb 22, 2017 05:09 AM|Cloudfiers|LINK
basically, it's like this:
The table is called
PH.INVENTORY, the column name isONHANDQUANTITYI want to run a query which deletes all rows in
PH.INVENTORYtable in which theONHANDQUANTITYvalue is 0 (All rows contain a 0 value for the columnONHANDQUANTITY)how can I add in my delete query?
separate thanks..
Star
8111 Points
2102 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 22, 2017 02:22 PM|chandrashekar|LINK
Hi
Is "ONHANDQUANTITY" column data type is string?
Thanks.
Please try the answer for the post and finally Don't forget to click “Mark as Answer” on the post that helped you.
Member
36 Points
256 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 23, 2017 12:46 AM|Cloudfiers|LINK
Hello
it's just decimal
Member
36 Points
256 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 23, 2017 12:58 AM|Cloudfiers|LINK
I tried this method but didn't not work , any idea sir?
Star
8111 Points
2102 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 23, 2017 01:44 PM|chandrashekar|LINK
Hi,
What I am asking is in database table, what is the data type of the column "ONHANDQUANTITY"? Is it Integer/nvarchar?
Please try the answer for the post and finally Don't forget to click “Mark as Answer” on the post that helped you.
All-Star
48490 Points
18068 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 23, 2017 02:25 PM|PatriceSc|LINK
Hi,
It's best to tell what happens. Do you have an error message (for example could it be that you try to use LIKE on INVNO which might be a numeric column, or it could be that ONHANDQUANTITY is actually NULL rarther than 0 and so it doesn't delete the row you think should be deleted etc...)
You perhaps want WHERE etc... AND (ONHANQUANTITY=0 OR ONHANDQUANTITY IS NULL)
Member
36 Points
256 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 24, 2017 12:39 AM|Cloudfiers|LINK
This is a number , am i asking how can i perform separation deletion on ONHANDQTY ? or other method of deletion
regard
Cloudfiers
thanks..
Member
36 Points
256 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 24, 2017 12:45 AM|Cloudfiers|LINK
All-Star
48490 Points
18068 Posts
Re: How can perform to delete all rows within a table in a oracle database, which value = 0 for o...
Feb 24, 2017 03:07 PM|PatriceSc|LINK
It seems it should work. If not, once again please tell what happens. Do you have an error message? Also ExecuteNonQuery returns the number of affected rows so if 0 it is definitively that the WHERE clause doesn't match any row.
Not directly related but using parameters is preffered over string concatenation (for example your code will fail if the user enters ' and is subject to https://en.wikipedia.org/wiki/SQL_injection)