CODEWITHSUNDEEP
c#winformsms-accessupdatesaddition
Zulfakar Zukri
Zulfakar Zukri

Reputation: 173

MS Access Update with addition

I'm working with MS Access and would like to how to update the data correctly..here is the code i use on my button click event with no luck

OleDbCommand cmd = new OleDbCommand("SELECT * FROM ItemTemp WHERE ITEM='" + txtItemname.Text + "'", GetConnection());
OleDbDataReader reader = cmd.ExecuteReader();

//check if this item exist on the table ItemTemp
if (reader.HasRows == true)
{
    // item exists, do below action
    OleDbCommand cmde = new OleDbCommand("UPDATE ItemTemp SET QUANTITY=QUANTITY + @QUANTITY, PRICE=PRICE + @PRICE WHERE ITEM='" + txtItemname.Text + "'", GetConnection());
    cmde.Parameters.AddWithValue("@QUANTITY", txtItemquantity.Value); //numericupdown control
    cmde.Parameters.AddWithValue("@PRICE", txtItemprice.Text); //textbox control
    cmde.ExecuteNonQuery();
}

data on database BEFORE updating:

ID    |    ITEM    |    QUANTITY    |    PRICE
1     |    ITEM1   |        1       |    400

data on database AFTER updating:

ID    |    ITEM    |    QUANTITY    |    PRICE
1     |    ITEM1   |       11       |    400400

data on database which i want it to be AFTER updating:

ID    |    ITEM    |    QUANTITY    |    PRICE
1     |    ITEM1   |        2       |    800

i do believe my command is correct if not my bad..there's no other than this code on my button click event. any ideas?

Upvotes: 1

Views: 1050

Answers (2)

Steve
Steve

Reputation: 216313

First you should be sure that the database fields Quantity and Price are oo numeric type. If they are Text then your query concatenate the string values passed as parameters with the string present in the database column.

Second, when you pass the parameters with AddWithValue the framework infers the datatype for you looking at the datatype of the passed value. In your case you pass a string and and the database engine sees a string that will be concatenated to the existing value. You need to convert before passing to an appropriate datatype

Finally, use allways parametrized query for every thing you pass to the database engine, especially if the value passed comes from an input textbox.

 OleDbCommand cmd = new OleDbCommand("SELECT * FROM ItemTemp WHERE ITEM=@item", GetConnection());
 cmd.Parameters.AddWithValue("@item", txtItemName.Text);
 OleDbDataReader reader = cmd.ExecuteReader();

 //check if this item exist on the table ItemTemp
 if (reader.HasRows == true)
 {
     OleDbCommand cmde = new OleDbCommand("UPDATE ItemTemp SET QUANTITY=QUANTITY + @QUANTITY," + 
     "PRICE=PRICE + @PRICE WHERE ITEM=@item, GetConnection());
     cmde.Parameters.AddWithValue("@QUANTITY", Convert.ToInt32(txtItemquantity.Value)); 
     cmde.Parameters.AddWithValue("@PRICE", Convert.ToDecimal(txtItemprice.Text)); 
     cmde.Parameters.AddWithValue("@item", txtItemName.Text);
     cmde.ExecuteNonQuery();
 }

Upvotes: 1

Zulfakar Zukri
Zulfakar Zukri

Reputation: 173

ok, its seem to be done now. i just change

cmde.Parameters.AddWithValue("@QUANTITY", txtItemquantity.Value); //numericupdown control
cmde.Parameters.AddWithValue("@PRICE", txtItemprice.Text); //textbox control

to this

cmde.Parameters.AddWithValue("@QUANTITY", Convert.ToInt32(txtItemquantity.Value));
cmde.Parameters.AddWithValue("@PRICE", Convert.ToInt32(txtItemprice.Text));

Upvotes: 1

Related Questions