如何在下订单时减去和更新股票表 [英] How to subtract and update stocks table when an order has been made

问题描述

我有两个表股票表和销售表

我想用Stock中的数量表减去库存中的数量。

减法后股票表应该更新使用新值





我创建的函数不起作用，它给出了两条错误消息;

1.块1：SQL逻辑错误不完整输入

2. SQL逻辑错误输入不完整



我尝试过：

I have two tables Stocks table and Sales table
I want to subtract the quantity in Stock with the Quantity in Sales table.
After subtraction Stocks table should be Updated with the new value


The Function I created is not working, it is giving two error messages;
1. Block 1: SQL logic error incomplete input
2. SQL logic error incomplete input

What I have tried:

private void updateStock()
         {
            int newAmount = 0;
             try
             {
                SetConnection();
                sql_con.Open();
                sql_cmd = sql_con.CreateCommand();
                
                string CommandText = "SELECT total_in_stock FROM Stocks_record WHERE item_code = " + txtProCode.Text;
                DB5 = new SQLiteDataAdapter(CommandText, sql_con);
                //DS5.Reset();

                try
                {
                    DB5.Fill(DT5);
                    foreach (DataRow row in DT5.Rows)
                    {
                        newAmount = int.Parse(row["total_in_stock"].ToString()) - int.Parse(txtQuantity.Text);
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show("Block 1: " + e.Message);
                }

                string query = "UPDATE Stocks_record SET total_in_stock = @i WHERE item_code = " + txtProCode.Text;
                SQLiteCommand cmd = new SQLiteCommand(query, sql_con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@i", newAmount);
                cmd.ExecuteNonQuery();
                DB5 = new SQLiteDataAdapter(CommandText, sql_con);
                DB5.Fill(DT5);

                //DT5 = DS5.Tables[0];
                dataGridView2.Refresh();
                dataGridView2.DataSource = DT5;
                sql_con.Close();
            }

             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }
         }

推荐答案

错误本身清除了混乱， SQL逻辑错误不完整输入的。您是否尝试引用内容中的变量？据我所知，SQL要求你在数据周围使用字符串引用，例如，

The error itself clears the confusion, SQL logic error incomplete input. Did you try to quote the variables in your content? As far as I know, SQL requires that you use string quotation around the data, like,

... WHERE data = 'something'

任何SQL专家都可以纠正我我错了。因此，可能是您的查询不完整并导致此问题。



最后，不要在SQL查询中使用连接，因为这会将查询公开给SQL注入，总是考虑使用参数将输入传递给查询。您已在UPDATE查询中执行此操作，但不完整。



SQL注入 - 维基百科 [ ^ ]

Any SQL guru can correct me if I am wrong here. So, it might be that your query is incomplete and causing this issue.

Lastly, do not use concatenation in SQL queries as this exposes the queries to SQL Injection, always consider using parameters to pass the input to queries. You are already doing that in the UPDATE query, but incompletely.

SQL injection - Wikipedia[^]

这篇关于如何在下订单时减去和更新股票表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！