我想从这个声明中得到OLEDB异常 [英] I want OLEDB exception from this statement

问题描述

我有一个Access数据库，其中创建了一些表，如TAXINVOICE，其列包括INVOICENO，MOBILENUMBER，CUSTOMERNAME，ADDRESS1，ADDRESS2，TINNO，PONO，TRANSPORT，BILLDATE，

InvoiceType，PODATE，totalGrossAmt ，totalVatAmt，BILLAMT，TRANSPORTAMT，OTHERAMT，FINALAMT，

ROUNDOFF，GRANDTOTAL，STATUS

另一个表InvoiceItems，包含列InvoiceItemId，InvoiceNumber，ProductId，Quantity，UOM，UnitPrice，GrossAmount ，增值税，VatAmount，

总计

和一个带有列的表产品

ProductId和ProductName



我的问题是从windows应用程序（C＃.NET代码）更新和删除我们在datagridview表单中编辑的条目。我使用了OLEDB查询，我将在下面添加



我尝试了什么：

I have an Access Database in which some tables are created like TAXINVOICE with columns like INVOICENO,MOBILENUMBER,CUSTOMERNAME,ADDRESS1,ADDRESS2,TINNO,PONO,TRANSPORT,BILLDATE,
InvoiceType,PODATE,totalGrossAmt,totalVatAmt,BILLAMT,TRANSPORTAMT,OTHERAMT,FINALAMT,
ROUNDOFF,GRANDTOTAL,STATUS
another table InvoiceItems with columns InvoiceItemId,InvoiceNumber,ProductId,Quantity,UOM,UnitPrice,GrossAmount,VAT,VatAmount,
Total
and a Table Products with columns
ProductId and ProductName

My problem is to update and delete whatever the entries we have edited in the datagridview form from windows application (C#.NET code).I used OLEDB queris which are tried and I will add below

What I have tried:

This is update statement for update button click code
try
           {
               cmd = new OleDbCommand("UPDATE from InvoiceItems,Products set Description ='" + txtProductName + "',Quantity = '" + txtQty.Text + "',UOM = '" + txtUOM.Text + "',UnitPrice ='" + txtUnitPrice.Text + "',GrossAmount ='" + txtGrossAmount.Text + "',VAT = '" + txtVAT.Text + "',VatAmount ='" + txtVatAmount.Text + "',Total ='" + txtTotal.Text + "' from InvoiceItems,Products where InvoiceItems.ProductId = Products.ProductId order by InvoiceNumber='" + txtInvoice.Text.Trim() + "'", conn);
               conn.Open();
               //dataGridView1.AutoGenerateColumns = true;
               cmd.ExecuteNonQuery();
               MessageBox.Show("Success");
               conn.Close();
               DisplayData();
               ClearData();
           }
           catch (OleDbException ex)
           {
               MessageBox.Show("Empty xseries");
               throw(ex);

           }

This is delete statement for update button click code
try
           {
               cmd = new OleDbCommand("DELETE FROM InvoiceNumber,INVOICENO from InvoiceItems WHERE InvoiceItems.InvoiceNumber=TAXINVOICE.INVOICENO and InvoiceNumber=" + txtInvoice.ToString() + "", conn);
               conn.Open();
               //dataGridView1.AutoGenerateColumns = false;
               cmd.ExecuteNonQuery();
               MessageBox.Show("Deleted Entry");
               conn.Close();
               DisplayData();
               ClearData();
           }
           catch (OleDbException ex)
           {
               MessageBox.Show("ALL rows are inevitable");
               throw(ex);
           }

推荐答案

如果我理解你的问题，你的陈述会有一些问题。



首先更新，尝试使用以下语法

UPDATE tablename

If I understand your question correctly, you have a few problems with the statements.

First the update, try using following syntax
UPDATE tablename

SET column = value,
    column = value,
    column = value...
WHERE conditions

因此在示例中它可能类似于

So in the example it could be something like

UPDATE InvoiceItems
SET Description = ?,
    Quantity = ?,
    UOM = ? ...
WHERE InvoiceItemKey = ?

另请注意，您不应将值连接到声明，而是使用 OleDbParameter类（System.Data .OleDb） [ ^ ]



在delete语句中你不能删除列，你总是删除行所以语法是

Also note that you should not concatenate the values to the statement but instead use OleDbParameter Class (System.Data.OleDb)[^]

In the delete statement you cannot delete columns, you always delete rows So the syntax is

DELETE FROM tablename WHERE conditions

例如

For example something like

DELETE FROM InvoiceItems 
WHERE InvoiceNumber = ?

还有这个语句，使用参数。

And also with this statement, use parameters.

查询错误。它应该是这样的：

The query is wrong. It should be something like:

UPDATE InvoiceItems SET <<fields>> FROM InvoiceItems, Products where <<where conditions>>

我想你必须更新InvoiceItems表，记住你一次只能更新一张表。



注意：我认为DELETE命令查询也是错误的。检查语法！



再见

I guess you have to update the InvoiceItems table, remember you can update only a table at a time.

Note: I think that also the DELETE command query is wrong. Check the syntax!

Bye

这篇关于我想从这个声明中得到OLEDB异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！