我需要正确的内连接条件查询构建，以便删除两个表中的记录：请帮助 [英] I need correct inner join condition query build in order to delete records in two tables: please help

问题描述

我正在尝试删除InvoiceItems和TAXINVOICE表中的记录。但是在写入Ole db语句时发生了一些查询分心。我正在尝试这样.MS访问中的确切表是InvoiceItems和TAXINVOICE。 InvoiceItems具有以下字段：InvoiceItem，InvoiceNumber，ProductId，Quantity，UOM，UnitPrice，GrossAmount，VAT，VatAmount，

总计

和TAXINVOICE有INVOICENO，MOBILENUMBER字段，CUSTOMERNAME，ADDRESS1，ADDRESS2，TINNO，PONO，TRANSPORT，BILLDATE，

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

ROUNDOFF和GRANDTOTAL



我尝试了什么：



cmd = new OleDbCommand（ DELETE InvoiceNumber，TAXINVOICE FROM InvoiceItems INNER JOIN TAXINVOICE WHERE InvoiceItems.InvoiceNumber = TAXINVOICE.INVOICENO和InvoiceNumber =+ txtInvoice.ToString（），conn）;

I am trying to delete the records in InvoiceItems and TAXINVOICE tables.But there is some query distraction occuring while writing to Ole db statement.I am trying like this.The exact tables in MS access are InvoiceItems and TAXINVOICE. The InvoiceItems has the following fields as InvoiceItem,InvoiceNumber,ProductId,Quantity,UOM,UnitPrice,GrossAmount,VAT,VatAmount,
Total
and TAXINVOICE has fields of INVOICENO,MOBILENUMBER,CUSTOMERNAME,ADDRESS1,ADDRESS2,TINNO,PONO,TRANSPORT,BILLDATE,
InvoiceType,PODATE,totalGrossAmt,totalVatAmt,BILLAMT,TRANSPORTAMT,OTHERAMT,FINALAMT,
ROUNDOFF and GRANDTOTAL

What I have tried:

cmd = new OleDbCommand("DELETE InvoiceNumber,TAXINVOICE FROM InvoiceItems INNER JOIN TAXINVOICE WHERE InvoiceItems.InvoiceNumber=TAXINVOICE.INVOICENO and InvoiceNumber=" + txtInvoice.ToString() , conn);

推荐答案

如果你设置适当的外键，你可以从一个表中删除，数据库将删除另一个表中的相关行，然后你就不必使用过于复杂的查询了。

If you set the appropriate foreign keys, you could delete from one table, and the database will delete related rows in the other table, and you then you won't have to use overly complex queries.

如果需要从两个表中删除行，尝试分开。例如

If you need to delete rows from both tables, try to do it separately. For example

"DELETE FROM InvoiceItems 
WHERE InvoiceNumber=TAXINVOICE.INVOICENO 
and InvoiceNumber=" + txtInvoice.ToString() 

"DELETE FROM TaxInvoice
WHERE InvoiceNo=" + txtInvoice.ToString()

但是，为了安全起见从SQL注入你真的应该使用 OleDbParameter Class（System .Data.OleDb） [ ^ ]。换句话说

However, in order to be safe from SQL injections you really should use OleDbParameter Class (System.Data.OleDb)[^]. In other words

"DELETE FROM InvoiceItems 
WHERE InvoiceNumber=TAXINVOICE.INVOICENO 
and InvoiceNumber=?"

"DELETE FROM TaxInvoice
WHERE InvoiceNo=?"

如解决方案1中所述，最简单的方法是定义外键并让数据库处理子行。请查看 CONSTRAINT子句（Microsoft Access SQL） [ ^ ]

这篇关于我需要正确的内连接条件查询构建，以便删除两个表中的记录：请帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！