检查发票表中的发票号是否存在 [英] Check if the invoice number in invoices table is exists
问题描述
我想检查发票表中的发票编号是否存在，然后在invoicedetails表中添加新的发票编号和填写发票详细信息，否则如果发票表中存在，只是我想更新总计字段，以防万一发票有多个项目;
i want to check if the invoice number in invoices table if not exists then add new invoice number an fill invoice details in invoicedetails table, else if it is exists in invoices table just i want to update the Total field in case if the invoice has more than one item;
StockClass stk = new StockClass();
stk.Quantity = txtQuantity.Text;
stk.StockID = txtStockID.Text;
stk.QtyUpdate();
MessageBox.Show("Stock record has been Successfully updated ");
InvoiceClass invclass = new InvoiceClass();
try
{
OleDbConnection myConnection = default(OleDbConnection);
myConnection = new OleDbConnection(cs);
OleDbCommand myCommand = default(OleDbCommand);
myCommand = new OleDbCommand("SELECT InvoiceNo FROM Invoices WHERE InvoiceNo = @InvoiceNo", myConnection);
OleDbParameter invono = new OleDbParameter("@username", OleDbType.VarChar);
invono.Value = txtInvoiceNo.Text;
myCommand.Parameters.Add(invono);
myCommand.Connection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (myReader.Read() == true)
{
invclass.InvoiceNo = txtInvoiceNo.Text;
invclass.Total = txtGrandTotal.Text;
invclass.Date = InvDate.Text;
invclass.updateinvoNumber();
}
else
{
invclass.InvoiceNo = txtInvoiceNo.Text;
invclass.Total = txtGrandTotal.Text;
invclass.Date = InvDate.Text;
invclass.AddNewinvoNumber();
invclass.InvoiceID = txtInvoiceNo.Text;
invclass.ProductID = txtProdID.Text;
invclass.ProName = txtProdName.Text;
invclass.ProType = txtProdType.Text;
invclass.ProSize = txtProdSize.Text;
invclass.Quantity = textQty.Text;
invclass.UnitPrice = txtPrice.Text;
invclass.Total = textTotal.Text;
invclass.Date = InvDate.Text;
invclass.CustName = txtCustName.Text;
invclass.EmpName = txtEmpName.Text;
invclass.AddNew();
}
if (myConnection.State == ConnectionState.Open)
{
myConnection.Dispose();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
OleDbDataAdapter ad = new OleDbDataAdapter("Select ProName, ProType, ProSize, Quantity, UnitPrice, Total, CustName, EmpName, date From InvoiceDetails WHERE [InvoiceID] = ?", cs);
ad.SelectCommand.Parameters.Add("@InvoiceID", OleDbType.VarChar);
ad.SelectCommand.Parameters["@InvoiceID"].Value = txtInvoiceNo.Text;
DataSet ds = new DataSet();
ad.Fill(ds, "Invo");
DGV1.DataSource = ds.Tables["Invo"];
DGV1.DataSource = ds.Tables[0];
}
当它不存在时效果很好，但是当它存在时我面临一个错误
/////错误// ///
ExecuteNonQuery需要一个开放的可用连接。连接的当前状态已关闭
/////////
When it is not exist it is working good, but when its exist i am facing an error
/////the erroe /////
ExecuteNonQuery requires an open available connection. The connection’s current state is closed
/////////
推荐答案
这是一些看起来很混乱的代码！
That is some confused looking code!
myCommand = new OleDbCommand("SELECT InvoiceNo FROM Invoices WHERE InvoiceNo = @InvoiceNo", myConnection);
OleDbParameter invono = new OleDbParameter("@username", OleDbType.VarChar);
invono.Value = txtInvoiceNo.Text;
myCommand.Parameters.Add(invono);
myCommand.Connection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (myReader.Read() == true)
{
如果数据库与您提供的发票编号相匹配，您可以从数据库中选择发票编号 - 因此您将返回您已知的数据...希望你不要把它发送到实际的发票号码，而是传递Username参数，这在你的陈述或数据库中是不匹配的...
你关闭读者的连接而创建应该使用连接的阅读器...
奇怪。
这是我将如何做到的：
You select the invoice number from the DB if it matches the invoice number you give it - so you are returning data you already know...expect you don;t hand it the invoice number in reality, you pass the Username parameter instead, which doesn't match in your statement or DB...
And you close the connection on a reader while creating the reader that is supposed to use the connection...
Strange.
Here's how I would do it:
using (OleDbConnection con = new OleDbConnection(strConnect))
{
con.Open();
using (OleDbCommand cmd = new OleDbCommand("SELECT COUNT(InvoiceNo) FROM Invoices WHERE InvoiceNo=@InvoiceNo", con))
{
cmd.Parameters.AddWithValue(txtInvoiceNo.Text);
int count = (int) cmd.ExecuteScalar();
if (count > 0
{
...
}
else
{
...
}
}
}
请查看您的数据类型：如果是发票编号，它应该是数字，可能是 - 应该是Total。永远不会将日期存储为字符串 - 这很容易做到，但它会导致以后出现重大困难！数据库中的C＃和DATETIME中的DateTime，或者当您稍后开始对数据执行任何有用的操作时，您将会遇到问题。
最后一件事：验证您的输入！用户犯错误：所以使用TryParse检查它们并将它们转换为适当的数据类型：int，DateTime，double。报告任何问题到用户并且不要继续。此时此操作很简单，但如果你不这样做，那么无效的数据就会得到进入你的数据库，以后修复是一个噩梦，因为你不知道用户在几个月的时间内注意到用户的实际意图！
And please look at your datatypes: If it's an Invoice number, it should be numeric, probably - as should Total. And never, ever store dates as strings - it's easy to do, but it causes major difficulties later! DateTime in C# and DATETIME in the DB or you will really get problems when you start to do anything useful with your data later.
And one final thing: validate your inputs! Users make mistakes: so use TryParse to check them and convert them to the appropriate datatype: int, DateTime, double. Report any problems to the user and don't continue. It's simple to do at this point, but if you don;t do it and invalid data gets into your DB, it's a nightmare to fix later as you have no idea what the user actually meant to enter when it's noticed in a months time!
这篇关于检查发票表中的发票号是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
