C#库存系统更新按钮出现故障

本文关键字:故障 按钮 更新 系统 | 更新日期: 2023-09-27 18:28:39

我的库存系统存在问题。当涉及到一个文本框时,它运行得很好。但当涉及2个或多个文本框时,它会特别显示错误:截断了不正确的DOUBLE值:"Knooks and Cranies"。(Knooks and Cranies是我输入的供应商的一个例子。)

我真的不知道怎么了,因为这是我第一次遇到这个错误。

这是我的代码:

namespace SupplyRequestAndInventoryManagementSystem
{
    public partial class DI_Assets : Form
    {
        connection con = new connection();
        MySqlCommand cmd;
        MySqlDataReader reader;
        public DI_Assets()
        {
            InitializeComponent();
        }
        //load data from database
        private void DI_Assets_Load(object sender, EventArgs e)
        {
            loadDepartment();
        }
        //checker
        private void ListDelivery_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ListDelivery.SelectedItems.Count > 0)
            {
                ListViewItem itm = ListDelivery.SelectedItems[0];
                lblAssetIDChecker.Text = itm.SubItems[4].Text;
            }
        }
        //load data from supplier
        private void btnSearch_Click(object sender, EventArgs e)
        {
            loadtbl();
        }
        //add button
        private void btnAdd_Click(object sender, EventArgs e)
        {
            DialogResult dg = MessageBox.Show("Are you sure you want to add new Delivery Information?", "Message", MessageBoxButtons.YesNo);
            if (dg == DialogResult.Yes)
            {
                if (SuppNameCombo.Text == "" || txtProdName.Text == "" || txtProdBrand.Text == "" || txtQty.Text == "" || DTPReceived.Text == "")
                {
                    MessageBox.Show("Don't leave blanks!");
                }
                else
                {
                    con.Close();
                    cmd = new MySqlCommand("Select * from deliver_mat where Del_MSupplier ='" + SuppNameCombo.Text + "' and Del_MName='" + txtProdName.Text + "' and Del_MBrand ='" + txtProdBrand.Text + "' and  Del_MQty= '" + txtQty.Text + "' and Del_MReceived='" + DTPReceived.Text + "'", con.con);
                    con.Open();
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        MessageBox.Show("Delivery Information already exist, sepcify a new one!");
                    }
                    else
                    {
                        addSection();
                        MessageBox.Show("Delivery Information successfully added!");
                        loadtbl();
                        txtProdName.Text = "";
                        txtProdBrand.Text = "";
                        txtQty.Text = "";
                        DTPReceived.Text = "";
                    }
                }
            }
            else
            {
                MessageBox.Show("Adding new Delivery Information has been cancelled!");
            }
        }
        //update button
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            DialogResult dg = MessageBox.Show("Are you sure you want to update the section?", "Message", MessageBoxButtons.YesNo);
            if (dg == DialogResult.Yes)
            {
                if (SuppNameCombo.Text == "" && txtProdName.Text == "" && txtProdBrand.Text == "" && txtQty.Text == "" && DTPReceived.Text == "")
                {
                    MessageBox.Show("Please choose section to be updated!");
                }
                else
                {
                    updateSection();
                    MessageBox.Show("Section has been successfully updated!");
                    loadtbl();
                    SuppNameCombo.Text = "";
                    txtProdName.Text = "";
                    txtProdBrand.Text = "";
                    txtQty.Text = "";
                    DTPReceived.Text = "";
                }
            }
            else
            {
                MessageBox.Show("Updating section has been cancelled!");
            }
        }
        //----------------------------------------------------------------------------------------------

        //Retrieving Data from DB to listview
        void loadtbl()
        {
            con.Close();
            DataTable table = new DataTable();
            cmd = new MySqlCommand("Select * from deliver_assets where Del_ASupplier = '" + SuppNameCombo.Text + "'", con.con);
            con.Open();
            reader = cmd.ExecuteReader();
            ListDelivery.Items.Clear();
            while (reader.Read())
            {
                ListViewItem item = new ListViewItem(reader["Del_AName"].ToString());
                item.SubItems.Add(reader["Del_ABrand"].ToString());
                item.SubItems.Add(reader["Del_AReceived"].ToString());
                item.SubItems.Add(reader["Del_AQty"].ToString());
                item.SubItems.Add(reader["Del_aID"].ToString());
                item.SubItems.Add(reader["Del_ASupplier"].ToString());
                ListDelivery.Items.Add(item);
            }
        }
        //Load Data to combo box
        void loadDepartment()
        {
            con.Close();
            DataTable table5 = new DataTable();
            cmd = new MySqlCommand("Select Supp_Name from supply", con.con);
            con.Open();
            reader = cmd.ExecuteReader();
            table5.Load(reader);
            foreach (DataRow row in table5.Rows)
            {
                SuppNameCombo.Items.Add(row["Supp_Name"]);
            }
        }
        //add function
        void addSection()
        {
            con.Close();
            cmd = new MySqlCommand("insert into deliver_assets(Del_AName, Del_ABrand, Del_AReceived, Del_AQty, Del_Asupplier) values('" + txtProdName.Text + "', '" + txtProdBrand.Text + "', '" + DTPReceived.Text + "', '" + txtQty.Text + "', '" + SuppNameCombo.Text + "')", con.con);
            con.Open();
            reader = cmd.ExecuteReader();
        }
        //update function
        void updateSection()
        {
            con.Close();
            cmd = new MySqlCommand("update deliver_assets set Del_ASupplier ='" + SuppNameCombo.Text + "' and  Del_AName ='" + txtProdName.Text + "' and Del_ABrand ='" + txtProdBrand.Text + "' and  Del_AQty ='" + txtQty.Text + "' and  Del_AReceived ='" + DTPReceived.Text + "'  where Del_aID ='" + lblAssetIDChecker.Text + "'", con.con);
            con.Open();
            reader = cmd.ExecuteReader();
        }




    }
}

C#库存系统更新按钮出现故障

您的代码包含许多在处理数据库任务时应该避免的错误:

  • 一次性对象使用全局变量(特别是MySqlConnection)
  • 不使用参数
  • Update语句的语法不正确
  • 使用不正确的方法执行插入/更新查询
  • 尝试使用"全能"方法建立连接(相关至第一)
  • 认为具有特定数据类型的列会愉快地接受字符串作为其值

举个例子,我将尝试重写更新代码

    //update function
    void updateSection()
    {
        string cmdText = @"update deliver_assets 
            set Del_ASupplier =@sup.
                Del_AName = @name,
                Del_ABrand = @brand
                Del_AQty = @qty
                Del_AReceived = @recv
             where Del_aID = @id";
        using(MySqlConnection con = new MySqlConnection(.....))
        using(MySqlCommand cmd = new MySqlCommand(cmdText, con))
        {
           cmd.Parameters.Add("@sup", MySqlDbType.VarChar).Value = SuppNameCombo.Text;
           cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = txtProdName.Text;
           cmd.Parameters.Add("@brand", MySqlDbType.VarChar).Value =  txtProdBrand.Text; 
           cmd.Parameters.Add("@qty", MySqlDbType.VarChar).Value = Convert.ToDouble(txtQty.Text);
           cmd.Parameters.Add("@recv", MySqlDbType.VarChar).Value = DTPReceived.Text;
           cmd.Parameters.Add("@id", MySqlDbType.Int32).Value =  Convert.ToInt32(lblAssetIDChecker.Text);
           con.Open();
           int rowsUpdated = cmd.ExecuteNonQuery();
           if(rowUpdated > 0)
               MessageBox.Show("Record updated");
      }
   }

请注意,我不能确定列的正确数据类型。您应该创建与您的列兼容的DataType参数,以更改上面示例中显示的MySqlDbType值。