SQL Update Query returning error

Question

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?

You may want to call ExecuteNonQuery on cmd1. I guess that's what @Dirk meant. — peter.petrov
– peter.petrov, Commented Oct 5, 2016 at 9:32
the code that isn't working was copied word for word from an update query that worked perfectly, so I'm assuming I have — Nic
– Nic, Commented Oct 5, 2016 at 9:35
@peter.petrov yes, and of course also put a space in the query like you said in your answer. — Dirk
– Dirk, Commented Oct 5, 2016 at 9:36

peter.petrov · Accepted Answer · 2016-10-05 09:34:08Z

2

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

Also, make sure you call ExecuteNonQuery on cmd1.

edited Oct 5, 2016 at 9:34

answered Oct 5, 2016 at 9:23

peter.petrov

39.7k24 gold badges114 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vaibhav Bhatia · Accepted Answer · 2016-10-05 09:32:39Z

0

Please try this.It should work

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

answered Oct 5, 2016 at 9:32

Vaibhav Bhatia

5361 gold badge4 silver badges13 bronze badges

Comments

Raktim Biswas · Accepted Answer · 2016-10-05 09:34:36Z

0

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

edited Oct 5, 2016 at 9:34

Raktim Biswas

4,1055 gold badges30 silver badges34 bronze badges

answered Oct 5, 2016 at 9:31

Durga Reddy

11 bronze badge

Comments

Dmitrii Bychenko · Accepted Answer · 2016-10-05 09:49:37Z

0

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");
}

edited Oct 5, 2016 at 9:49

answered Oct 5, 2016 at 9:37

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Collectives™ on Stack Overflow

SQL Update Query returning error

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related