访问中的更新查询出错，C＃ [英] Error in update query in access,C#

问题描述

当我使用sql数据库时，这个查询完全给出了输出，但是访问时会出现错误，即UPDATE语句中的语法错误

this query was completely gave output when I used sql database,but with access this give error that is "Syntax error in UPDATE statement"

"update Client_Payment_TB set Pay_Mode ='" + Pay_Modes.ToString() + "', Cheque_DD_No ='" + txt_Chaque_No.Text + "', Bank ='" + txt_Bank.Text + "', Cheque_Date ='" + dateTimePicker_Chaque.Value.ToString("dd/MM/yyyy") + "', Current_Date ='" + dateTimePicker_Current_date.Value.ToString("dd/MM/yyyy") + "', Discount_Amount ='" + txt_Discount.Text + "', Paid ='" + dpp.ToString() + "', Remaining='" + txt_Remaining.Text + "' where Bill_ID='" + txt_Bill_No.Text + "'";

请帮助我.... !!

Please help me....!!

推荐答案

首先，不要连接字符串来构建一个SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

无论如何，修复它可能会解决你的问题！

First off, Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
The chances are that fixing that will get rid of your problem anyway!

string sql = "UPDATE Client_Payment_TB SET Pay_Mode=@PM, Cheque_DD_No=@CDN, Bank=@BK, Cheque_Date=@CDAT, Current_Date=@NOW, Discount_Amount=@DA, Paid=@PD, Remaining=@RM WHERE Bill_ID=@ID";
using (SqlCommand cmd = new SqlCommand(sql, con))
   {
   cmd.AddWithValue("@PM", Pay_Modes);
   cmd.AddWithValue("@CDN", txt_Chaque_No.Text);
   cmd.AddWithValue("@BK", txt_Bank.Text);
   cmd.AddWithValue("@CDAT", dateTimePicker_Chaque.Value);
   cmd.AddWithValue("@NOW", dateTimePicker_Current_date.Value);
   cmd.AddWithValue("@DA", txt_Discount.Text);
   cmd.AddWithValue("@PD", dpp);
   cmd.AddWithValue("@RM", txt_Remaining.Text);
   cmd.AddWithValue("@ID", txt_Bill_No.Text);
   ...
   }

在查询中尝试指定＃而不是'。



例如。 ChequeDate =＃+ dateTimePicker_Chaque.Value.ToString（yyyy / MM / dd）+＃

Try specifying "#" instead of " ' " in the query.

For eg. " ChequeDate=#" + dateTimePicker_Chaque.Value.ToString("yyyy/MM/dd") + "#"

如果我将您的字符串更改为格式化字符串，您将获得下一个字符串：

If I change your string to a formatted one you get the next:

String.Format("update Client_Payment_TB set Pay_Mode ='{0}', Cheque_DD_No ='{1}',Bank ='{2}', Cheque_Date ='{3}', Current_Date='{4}', Discount_Amount ='{5}'
, Paid ='{6}, Remaining='{7}' where Bill_ID='{8}'",Pay_Modes, txt_Cheque_No.Text, txt_Bank.Text, dateTimePicker_Chaque.Value, dateTimePicker_Current_date.Value,
txt_Discount.Text, dpp, txt_Remaining.Text, txt_Remaining.Text )

出现的问题：

- 为什么将money / decimal值设置为字符串值？所有转换都进展顺利吗？

- 为什么你的SQL语句没有以a结尾;那;访问需要！ SQL不会使用;

The question that arise:
- Why are money/decimal values set as string values? Are all conversions going well?
- Why you have your SQL statment not ended with a ; That ; is needed for Access! SQL doesn't bather the;

这篇关于访问中的更新查询出错，C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！