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();
}
}
}
您的代码包含许多在处理数据库任务时应该避免的错误:
- 一次性对象使用全局变量(特别是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值。