CODEWITHSUNDEEP
c#sqlsql-serverms-access
Nic
Nic

Reputation: 21

SQL Update Query returning error

OleDbCommand cmd = new OleDbCommand("SELECT Stock FROM Products WHERE ID=" + ProductID + ";", conn); //run the database query
OleDbDataReader cusReader = cmd.ExecuteReader(); //read the result of the query
cusReader.Read();           
ProductStock = (int)cusReader.GetValue(0);
cusReader.Close();
MessageBox.Show((ProductStock).ToString()); // checks that the form is being accessed and the SELECT query works

OleDbCommand cmd1 = new OleDbCommand("UPDATE Products SET Stock=" + (ProductStock - 1) + "WHERE ID= " + ProductID +";", conn);

try
{
   if (cusReader.RecordsAffected > 0)
   {
      MessageBox.Show("no issue was experienced");
   }
   else
   {
      MessageBox.Show("An issue occured when decreasing stock");
   }
   cusReader.Close();
}
catch (Exception ex)
{
   MessageBox.Show("An error occured in query.\n" + ex.Message);
}

The update query returns the "an issue occurred" message. The message box that shows the variable Productstock does return the correct value. Can anbody explain how to fix this issue?

Upvotes: 2

Views: 302

Answers (4)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

Try update in one go (do not separate select and update: someone can modify the data after you've performed select but before you've started update), and then check rows affected. Make you sql being readable to avoid evident errors.

string sql = 
  // Parametrize your query or at least format it out: 
  $@"update Products
        set Stock = Stock - 1
      where Id = {ProductId} and
            Stock >= 1 -- added: you must have stock to decrease";

// Wrap IDisposable into using
using (OleDbCommand cmd = new OleDbCommand(sql, conn)) {
  int affected = cmd.ExecuteNonQuery();

  if (affected > 0) 
    MessageBox.Show("No issue was experienced");
  else
    MessageBox.Show("An issue occured when decreasing stock");
}

Upvotes: 0

Durga Reddy
Durga Reddy

Reputation: 1

OleDbCommand cmd1 = new OleDbCommand("UPDATE Products SET Stock=" + (ProductStock - 1) + " WHERE ID= " + ProductID +";", conn);

Please use above command.

Problem: No space between stock value and where

Upvotes: 0

peter.petrov
peter.petrov

Reputation: 39437

Put a space here right before WHERE in + "WHERE ID= " +

Also, make sure you call ExecuteNonQuery on cmd1.

Upvotes: 2

Vaibhav Bhatia
Vaibhav Bhatia

Reputation: 536

Please try this.It should work

OleDbCommand cmd1 = new OleDbCommand("UPDATE Products SET Stock=" + (ProductStock - 1) + " WHERE ID= " + ProductID, conn);

Upvotes: 0

Related Questions