Subtracting a textbox from a cell in the database

Question

I have a button which adds products to the invoice, I want it to delete products off the database as well, how can I edit this query so it deletes from the database?

I think my error is because of the way I am converting cmbQuantity.Text, can someone help me with a fix?

SqlCommand inventorycontrol = new SqlCommand("Update Product SET quantityAvailable=quantityAvailabe - '" + Convert.ToInt32(cmbQuantity.Text) + "' WHERE productName='" + cmbProdName.Text + "'", con);

What is the problem with this code? An exception? No updates? What platform, what is your table layout? How and when do you execute this command? — Patrick Hofman, Jan 25 '16 at 15:56
Oh my goodness, SQL injection. I'm assuming cmbProdName is a textbox. I'll wipe your DB now: `1' -- DROP TABLE Product;` Always sanitize your inputs! — Joshua Grosso Reinstate CMs, Jan 25 '16 at 15:57

score 0 · Accepted Answer · edited May 23 '17 at 12:15

0

Without the error message, it's hard to guess.

But at first sight, you have a typo here : quantityAvailable=quantityAvailabe - should be quantityAvailable=quantityAvailab**l**e -.

Moreover, you must not quote the integer part, so '" + quantityToRemove + "' becomes " + quantityToRemove + ". But the best is to use parametrization, which will simplify your code. See Why do we always prefer using parameters in SQL statements?

Try to separate access to your UI and building your SQL:

int quantityToRemove = Convert.ToInt32(cmbQuantity.Text);
string productName = cmbProdName.Text;

string sqlUpdate = @"UPDATE Product 
                     SET quantityAvailable = quantityAvailable - @quantityToRemove
                     WHERE productName= @productName";
SqlCommand inventorycontrol = new SqlCommand(sqlUpdate, con);
inventorycontrol .Parameters.AddWithValue("quantityToRemove", quantityToRemove);
inventorycontrol .Parameters.AddWithValue("productName", productName);

edited May 23 '17 at 12:15

Community

1
1

answered Jan 25 '16 at 15:57

Cyril Gandon

16,830
14
78
122

Why not `int.TryParse()` – sujith karivelil Jan 25 '16 at 16:09
I just cut and paste the given code. Using parametrize query is the number one problem in this code. Handling error case on input comes after, but I think OP needs one advice at a time :) – Cyril Gandon Jan 25 '16 at 16:11
Thanks alot :) it works, I agree there is a long way from this code being ever used, but i just started c#, so I wasn't looking into much of the issues like injections, Thanks for the feedback as well – Kingofspades24 Jan 25 '16 at 17:54
I'm genuinely confused. What does the OP's query, SQL injection, question redaction, this answer, and the question title have to do with ^*anything*? "Subtracting a textbox from a cell in the database", "I have a button which adds products to the invoice, I want it to delete products off the database as well", and then we have a query which looks like a stock movement? :-O – Jcl Jan 25 '16 at 18:14
well basically the form adds products to an invoice, I got that part working, but it needed to minus the products from inventory as soon as it is added to invoice, this query helps to do that. – Kingofspades24 Jan 25 '16 at 18:35

sujith karivelil · Answer 2 · 2016-01-25T18:08:02.520

In the question you have not specify the error, but there may be chances of getting error in your code. that i will clarify you.

When it failed to convert the cmbQuantity.Text to int, you need not to pass an integer within double quotes :- Here my suggested answer will help you to handle this error by showing error message if the quantity is invalid.

The query you are using opens a wide range to SQL Injection. I suggest you to use parameterized query to avoid injection, As a whole you can use like the following:

 int quantity;
    if (int.TryParse(cmbQuantity.Text, out quantity))
    {
        SqlCommand inventorycontrol = new SqlCommand("Update Product SET quantityAvailable=quantityAvailabe - @Quantity WHERE productName=@prodName", con);
        inventorycontrol.Parameters.AddWithValue("@Quantity",quantity);
        inventorycontrol.Parameters.AddWithValue("@prodName", cmbProdName.Text);
        //Execue command here
    }
    else
    {
        // show message invalid quantity
    }

I think you want to replace `Convert.ToInt32()` with `quantity`. — Wyatt Earp, Jan 25 '16 at 16:55

Subtracting a textbox from a cell in the database

2 Answers2