How to subtract 1 item from a list of inventory when a purchase is made.

[shyy]

Hi Guys,

I am trying to create an update SQL where if a particular employee name is selected, clicking the update button will substract 1 value from its current value in the database. This above example logically doesn't make sense but I need to apply this concept to a bigger project. What I have so far

Ideally, In this particular case the employee Chew should have an InvQuant of 4 when the user clicks the update button.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;


namespace test
{
    public partial class Form1 : Form
    {
        OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=H:\\VB Projects\\test\\test\\bin\\Debug\\employee.accdb;Persist Security Info=False");
        OleDbDataAdapter ad = new OleDbDataAdapter();
        DataSet ds = new DataSet();


        public Form1()
        {
            InitializeComponent();
        }


        private void Save_btn_Click(object sender, EventArgs e)
        {
            try
            {
                ad.InsertCommand = new OleDbCommand("insert into person values(@EmpName, @InvQuant)", con);
                ad.InsertCommand.Parameters.Add("@EmpName", OleDbType.VarChar).Value = textBox1.Text.ToString();
                ad.InsertCommand.Parameters.Add("@InvQuant", OleDbType.Integer).Value = textBox2.Text.ToString();


                con.Open();
                ad.InsertCommand.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void Show_btn_Click(object sender, EventArgs e)
        {
            try
            {
                ad.SelectCommand = new OleDbCommand("select * from person", con);


                ds.Clear();
                ad.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];


                con.Open();
                ad.SelectCommand.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void Update_btn_Click(object sender, EventArgs e)
        {
            try
            {
                ad.UpdateCommand = new OleDbCommand("update person set InvQuant = '" + [COLOR=#ff0000](InvQuant -1)[/COLOR] + "' where (EmpName = '" + textBox1.Text + "' and InvQuant - 1 + )", con);
               
                con.Open();
                ad.UpdateCommand.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void Delete_btn_Click(object sender, EventArgs e)
        {
            try
            {
                ad.DeleteCommand = new OleDbCommand("delete from person where (EmpName = '" + textBox1.Text + "')", con);


                con.Open();
                ad.DeleteCommand.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }  
    }
}

 

[jmcilhinney]

This is the way your SQL should look:

UPDATE MyTable SET MyColumn = MyColumn - 1 WHERE PK = @PK

Make sure that you use parameters to insert values into SQL code and the only value you are inserting in this case is the PK. To learn how to use parameters properly, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

Also, you are obviously confused about how data access works, given that you're calling ExecuteNonQuery on a command that specifically contains a query. I suggest that you read this thread to learn what types and members to use in what scenarios:

Retrieving and Saving Data in Databases

 

[shyy]

Thanks got it to work