SQL Update命令，在asp.net c#中执行计算

本文关键字：执行 计算 net asp Update 命令 SQL | 更新日期: 2023-09-27 17:54:57

我正试图计算数据库中未偿还发票的利率。

我正在使用微软访问数据库，但asp.net/C#不喜欢我的更新语句。

asp.net/C#告诉我有"附加信息:查询表达式中的语法错误(缺少操作符)"。

  "UPDATE s_transaction_enquiry 
   SET s_transaction_enquiry.interest_amount = DateDiff('d', s_transaction_enquiry.due_date, Now()) * (s_transaction_enquiry.payment_amount / 100 * sales_ledger.interest_rate) 
   FROM s_transaction_enquiry JOIN sales_ledger 
   ON s_transaction_enquiry.account_number=sales_ledger.account_number 
   WHERE s_transaction_enquiry.status = 'OPEN';

谁能帮忙纠正这个说法??我想问题可能出在这一行:

// Im trying to calculate the number of days between today's date and the date in the database.
DateDiff('d', s_transaction_enquiry.due_date, Now())

SQL Update命令，在asp.net c#中执行计算

试试这个:

UPDATE s_transaction_enquiry 
INNER JOIN sales_ledger ON s_transaction_enquiry.account_number = sales_ledger.account_number 
SET s_transaction_enquiry.interest_amount = (DateDiff("d",[s_transaction_enquiry]![due_date],Now())*([s_transaction_enquiry]![payment_amount]/100*[sales_ledger]![interest_rate]))
WHERE (([s_transaction_enquiry]![status]='Open'));

我认为在MS Access中，当涉及多个表时，SET部分在JOIN之后。

Try doing this

"UPDATE s_transaction_enquiry 
JOIN sales_ledger 
ON s_transaction_enquiry.account_number=sales_ledger.account_number 
SET s_transaction_enquiry.interest_amount = DateDiff('d', s_transaction_enquiry.due_date, Now()) * (s_transaction_enquiry.payment_amount / 100 * sales_ledger.interest_rate) 
WHERE s_transaction_enquiry.status = 'OPEN';"
相关文章：