CODEWITHSUNDEEP
c#sqlasp.netms-access
MePengusta
MePengusta

Reputation: 27

Update command dont work ms access c#

I got a gridview which is connected to a datasource getting values from there.I created a selectedindexchanged function to work when select is clicked.it shows ID , orderID,From,To and Price values and opens panel which has 4 textbox's and a dropdownlist if user wants to change those values.Everything is fine until here.When user changes some values and clicks submit nothing changes in database.I got the values using id ; " string id = orderGrid.SelectedRow.Cells[1].Text; "

here is my submit button code ;

protected void submitButton_Click(object sender, EventArgs e)
    {      
        string id = orderGrid.SelectedRow.Cells[1].Text;
        OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("db.mdb") + ";Persist Security Info=False");
        string query = "update ordersTable set orderID=@testID,fromLocation=@from,toLocation=@to,price=@price WHERE ID = @id ";
        OleDbCommand cmd = new OleDbCommand(query, con);
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@testID", orderBox.Text);
        cmd.Parameters.AddWithValue("@from", fromText.Text);
        cmd.Parameters.AddWithValue("@to", toList.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@price", priceBox.Text);
        try
        {        
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            Response.Write("Edit Complete !");

        }
        catch (Exception ex)
        {
            Response.Write("Error : " + ex);
        }
        orderGrid.DataBind();
    }

id string works perfectly fine in my selectedindexchanged function.

Upvotes: 0

Views: 35

Answers (1)

Steve
Steve

Reputation: 216243

In OleDb parameters are recognized by their position not by their name.
Your parameter placeholder @ID is the last one in the query, but you add it as the first one in the collection.
This result in your WHERE condition to be totally wrong
(you search for a record whose ID is equal to the content of the priceBox)

Just move the insert of the ID as last parameter

cmd.Parameters.AddWithValue("@testID", orderBox.Text);
cmd.Parameters.AddWithValue("@from", fromText.Text);
cmd.Parameters.AddWithValue("@to", toList.SelectedItem.Text);
cmd.Parameters.AddWithValue("@price", priceBox.Text);
cmd.Parameters.AddWithValue("@id", id);

This is the primary problem, but I can see another one caused by your use of AddWithValue. This is an handy shortcut, but sometime it make you pay for it.
In your case, you pass to the @price parameter a string and, if your price field is a decimal (as it should be) then the database engine will attempt a conversion from a string to a decimal and if the decimal separator is not the same you end with a wrong value in the database. Better check the value in the priceBox and convert it yourself to a decimal.

See Can we stop to use AddWithValue already?

Upvotes: 1

Related Questions