c# - 获取更新查询的 sql 语法错误

Question

我正在尝试更新一些客户详细信息，但出现错误:错误:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在附近使用的正确语法
PaperWorkRequired = 1、BookingInRequired = 0、TailLiftRequired = 1、OpeningTimes 在第 1 行

文书工作、预订和提尾是复选框，开放时间是一个文本框。这些选项不需要填写(由于复选框为空，因此 BookingInRequired 等于零)。

public static void UpdateCustomer(int CustomerID, string Name, int AccountType, string AccountCode, string Add1, string Add2, string Add3, string Town, int TownID, int CountryID, int CurrencyType, int CountyID, string PostCode, string ContactName, string Phone, string Email, int IsActive, int ModifiedByUser, string Website, string VATNo, int PORequired, int CreditTerms, int CreditDays, int VATCodeID, int COD, string VATAuthNo, int PrintBankDetails, int VATExempt, DateTime VATExpiry, 
    string SignedCreditApplcation, DateTime FinancialYearEnd, string FinancialSummary, DateTime CreditReviewDate, string CreditReviewComments, string DefaultInvoiceType, string DownloadToAccountCode, int PODRequired, decimal FuelSurcharge,
    string InvoiceInstructions, string DeliveryInstructions, int DeliveryInstructionsField, string CollectionInstuctions, int CollectionInstructionsField, string SpecialInstructions, int SpecialInstructionsField, int FOC, string DefaultPONumber, int OnHold, int PrintVAT, int PickByOrder, int IncludeInSelectStock, string GroupAccountCode, DateTime AccreditationExpiry, string AccreditedBy, DateTime AccreditedDate, int SendToWeb, int Rank, string SalesRep, int ShowProductionNote,
    string WebsiteCheckDate, string WebsiteComments, int Facebook, int PaperRecordOnFile, int FSCReq, string QuoteType, int PalletNetwork, string CustomerRequirement, int CustomerRequirementField, int ServiceLevel, int DefaultCreditorID, int ManualLotNo, DateTime DiscountReviewDate, decimal CreditLimitAmount, DateTime? AccountDisabledDate, string AccountDisabledMsg,
    int PaperWorkRequired, int BookInRequired, int TailLiftRequired, string OpeningTimes, string EquipmentRequired, string WeighInOut)
{
    string sql = "proc_UpdateCustomer";

    using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionStrings["TAT"]))
    {
        conn.Open();
        using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE customer SET Name = '" + Name.Replace("'", "''") + "', " +
                                                  "AccountType = " + AccountType + ", " +
                                                  "AccountCode = '" + AccountCode.Replace("'", "''") + "', " +
                                                  "Add1 = '" + Add1.Replace("'", "''") + "', " +
                                                  "Add2 = '" + Add2.Replace("'", "''") + "', " +
                                                  "Add3 = '" + Add3.Replace("'", "''") + "', " +
                                                  "Town = '" + Town.Replace("'", "''") + "', " +
                                                  "TownID = " + TownID + ", " +
                                                  "CountryID = " + CountryID + ", " +
                                                  "CountyID = " + CountyID + ", " +
                                                  "CurrencyType = " + CurrencyType + ", " +
                                                  "PostCode = '" + PostCode.Replace("'", "''") + "', " +
                                                  "ContactName = '" + ContactName.Replace("'", "''") + "', " +
                                                  "Phone = '" + Phone.Replace("'", "''") + "', " +
                                                  "Email = '" + Email.Replace("'", "''") + "', " +
                                                  "IsActive = " + IsActive + ", " +
                                                  "ModifiedByUser = " + ModifiedByUser + ", " +
                                                  "ModifiedDate = NOW()," +
                                                  "Website = '" + Website.Replace("'", "''") + "', " +
                                                  "VATNo = '" + VATNo.Replace("'", "''") + "', " +
                                                  "CreditTermsID = " + CreditTerms + "," +
                                                  "CreditDays = " + CreditDays + "," +
                                                  "VATCodeID = " + VATCodeID + "," +
                                                  "COD = " + COD + ", " +
                                                  "VATAuthNo = '" + VATAuthNo.Replace("'", "''") + "', " +
                                                  "PrintBankDetails = " + PrintBankDetails + ", " +
                                                  "VATExempt = " + VATExempt + ",  " +
                                                  "VATExpiry = '" + VATExpiry.Year + "-" + VATExpiry.Month + "-" + VATExpiry.Day + "', " +
                                                  "FinancialYearEnd = '" + FinancialYearEnd.Year + "-" + FinancialYearEnd.Month + "-" + FinancialYearEnd.Day + "', " +
                                                  "FinancialSummary ='" + FinancialSummary.Replace("'", "''") + "', " +
                                                  "SignedCreditApplication = '" + SignedCreditApplcation.Replace("'", "''") + "', " +
                                                  "CreditReviewedDate = '" + CreditReviewDate.Year + "-" + CreditReviewDate.Month + "-" + CreditReviewDate.Day + "', " +
                                                  "CreditReviewComments = '" + CreditReviewComments.Replace("'", "''") + "', " +
                                                  "DefaultInvoiceType = '" + DefaultInvoiceType.Replace("'", "''") + "', " +
                                                  "DownloadToAccountCode ='" + DownloadToAccountCode.Replace("'", "''") + "', " +
                                                  "PODRequired = " + PODRequired + ", " +
                                                  "FuelSurcharge= " + FuelSurcharge + ", " +
                                                  "InvoiceInstructions = '" + InvoiceInstructions.Replace("'", "''") + "'," +
                                                  "DeliveryInstructions = '" + DeliveryInstructions.Replace("'", "''") + "', " +
                                                  "DeliveryInstructionsField = " + DeliveryInstructionsField + ", " +
                                                  "CollectionInstructions ='" + CollectionInstuctions.Replace("'", "''") + "', " +
                                                  "CollectionInstructionsField = " + CollectionInstructionsField + ", " +
                                                  "SpecialInstructions ='" + SpecialInstructions.Replace("'", "''") + "', " +
                                                  "SpecialInstructionsField = " + SpecialInstructionsField + ", " +
                                                  "FOC = " + FOC + ",  " +
                                                  "DefaultPO = '" + DefaultPONumber.Replace("'", "''") + "'," +
                                                  "PORequired = " + PORequired + ", " +
                                                  "OnHold = " + OnHold + ", " +
                                                  "PrintVAT = " + PrintVAT + ", " +
                                                  "IncludeInSelectStock = " + IncludeInSelectStock + ", " +
                                                  "PickByOrder = " + PickByOrder + ", " +
                                                  "AccreditationExpiry = '" + AccreditationExpiry.Year + "-" + AccreditationExpiry.Month + "-" + AccreditationExpiry.Day + "'," +
                                                  "AccreditedDate = '" + AccreditedDate.Year + "-" + AccreditedDate.Month + "-" + AccreditedDate.Day + "'," +
                                                  "AccreditedBy = '" + AccreditedBy.Replace("'", "''") + "'," +
                                                  "GroupAccountCode = '" + GroupAccountCode.Replace("'", "''") + "', " +
                                                  "SendToWeb = " + SendToWeb + ", " +
                                                  "SalesRep = '" + SalesRep.Replace("'", "''") + "'," +
                                                  "ShowProductionNote = " + ShowProductionNote + ", " +
                                                  "WebsiteCheckDate = '" + WebsiteCheckDate.Replace("'", "''") + "', " +
                                                  "WebsiteComments = '" + WebsiteComments.Replace("'", "''") + "', " +
                                                  "Facebook = " + Facebook + ", " +
                                                  "PaperRecordOnFile = " + PaperRecordOnFile + ", " +
                                                  "FSCReq = " + FSCReq + ", " +
                                                  "QuoteType = '" + QuoteType.Replace("'", "''") + "', " +
                                                  "Rank = " + Rank + ", " +
                                                  "PalletNetwork = " + PalletNetwork + ", " +
                                                  "CustomerRequirement ='" + CustomerRequirement.Replace("'", "''") + "', " +
                                                  "CustomerRequirementField = " + CustomerRequirementField + ", " +
                                                  "ServiceLevel = " + ServiceLevel + ", " +
                                                  "ManualLotNo = " + ManualLotNo + ", " +
                                                  "DiscountReviewDate = '" + DiscountReviewDate.Year + "-" + DiscountReviewDate.Month + "-" + DiscountReviewDate.Day + "', " +
                                                  "CreditLimitAmount = " + CreditLimitAmount + ", " +
                                                  "DefaultCreditorID = " + DefaultCreditorID + ", " +
                                                  "AccountDisabledDate = ?AccountDisabledDate," +
                                                  "AccountDisabledMsg = ?AccountDisabledMsg " +
                                                  "PaperWorkRequired = " + PaperWorkRequired + ", " +
                                                  "BookingInRequired = " + BookInRequired + ", " +
                                                  "TailLiftRequired = " + TailLiftRequired + ", " +
                                                  "OpeningTimes ='" + OpeningTimes.Replace("'", "''") + "', " +
                                                  "EquipmentRequired ='" + EquipmentRequired.Replace("'", "''") + "', " +
                                                  "WeighInOut ='" + WeighInOut.Replace("'", "''") +
                                                  "WHERE ID = " + CustomerID;
            cmd.Parameters.Add(new MySqlParameter("AccountDisabledDate", AccountDisabledDate));
            cmd.Parameters.Add(new MySqlParameter("AccountDisabledMsg", AccountDisabledMsg));
            cmd.ExecuteNonQuery();
        }
    }
}

Answer 1

在您的查询构建中，您缺少逗号
在 ?AccountDsibaledMsg 之后如下

AccountDisabledMsg = ?AccountDisabledMsg " + 
"PaperWorkRequired = " + PaperWorkRequired + 

打个逗号试试

 AccountDisabledMsg = ?AccountDisabledMsg ," + 
"PaperWorkRequired = " + PaperWorkRequired +

但总是尝试参数化查询，因为它会让你调试更容易以及避免 sql 注入(inject)问题