ASP.NET和SQL Server:'?'附近语法不正确
问题描述:
我收到一个错误ASP.NET和SQL Server:'?'附近语法不正确
附近的语法不正确?
当试图使用更新查询功能。代码来自SagePay http://www.sagepay.co.uk/file/12136/download-document/DotNetkit%201.2.6.7%20-%202014-08-14.zip?token=BJFwtM7qNnnm5ZCc_l_dOhq4INB0cQTPCxCd5JOpeh4
并涉及其服务器InFrame实现。
据我所见,顺序被正确传递,字段列表匹配数据库,只是不理解为什么我看到这个错误。该代码最初是为MySQL创建的,但必须适应SQL Server。
我试图调试,但实际上并不能看到正在致力于从cmd.ExecuteNonQuery();
在SQL Server中的任何帮助,将不胜感激,这里是代码:
private static readonly List<String> FieldNames = new List<String>
{
VendorTxCodeField, AddressResultField, AddressStatusField, AmountField, AvsCv2Field, BankAuthCodeField, BasketField,
BillingFirstnamesField, BillingSurnameField, BillingPhoneField, BillingAddress1Field, BillingAddress2Field, BillingCityField,
BillingPostCodeField, BillingStateField, BillingCountryField, DeclineCodeField, DeliveryFirstnamesField, DeliverySurnameField, DeliveryPhoneField,
DeliveryAddress1Field, DeliveryAddress2Field, DeliveryCityField, DeliveryPostCodeField, DeliveryStateField, DeliveryCountryField,
CapturedAmountField, CardTypeField, CavvField, CurrencyField, CustomerEmailField, Cv2ResultField, ExpiryDateField, FraudResponseField,
GiftAidField, Last4DigitsField, LastUpdatedField, PayerIdField, PayerStatusField, PostCodeResultField,
RelatedVendorTxCodeField, SecurityKeyField, StatusField, StatusMessageField, SurchargeField, ThreeDSecureStatusField,
TransactionTypeField, TxAuthNoField, TokenIdField, VpsTxIdField
};
public static bool UpdateOrder(Order order, string vendorTxCode)
{
var result = false;
SqlConnection conn = null;
try
{
conn = new SqlConnection(ConnectionString);
conn.Open();
var cmd = new SqlCommand
{
Connection = conn, CommandText = "UPDATE Orders SET " + string.Join(",", FieldNames.Select(field => field + "=?" + field).ToList()) + " WHERE " + VendorTxCodeField + " =?" + VendorTxCodeField
};
cmd.Prepare();
AddOrderParameters(cmd, order);
cmd.ExecuteNonQuery();
result = true;
}
catch (SqlException ex)
{
Console.WriteLine("Error: {0}", ex);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return result;
}
private static void AddOrderParameters(SqlCommand command, Order order)
{
command.Parameters.AddWithValue(VendorTxCodeField, order.VendorTxCode);
command.Parameters.AddWithValue(AddressResultField, order.AddressResult);
command.Parameters.AddWithValue(AddressStatusField, order.AddressStatus);
command.Parameters.AddWithValue(AmountField, order.Amount);
command.Parameters.AddWithValue(AvsCv2Field, order.AvsCv2);
command.Parameters.AddWithValue(BankAuthCodeField, order.BankAuthCode);
command.Parameters.AddWithValue(BasketField, order.Basket);
command.Parameters.AddWithValue(BillingAddress1Field, order.BillingAddress1);
command.Parameters.AddWithValue(BillingAddress2Field, order.BillingAddress2);
command.Parameters.AddWithValue(BillingCityField, order.BillingCity);
command.Parameters.AddWithValue(BillingCountryField, order.BillingCountry);
command.Parameters.AddWithValue(BillingFirstnamesField, order.BillingFirstnames);
command.Parameters.AddWithValue(BillingPhoneField, order.BillingPhone);
command.Parameters.AddWithValue(BillingPostCodeField, order.BillingPostCode);
command.Parameters.AddWithValue(BillingStateField, order.BillingState);
command.Parameters.AddWithValue(BillingSurnameField, order.BillingSurname);
command.Parameters.AddWithValue(CapturedAmountField, order.CapturedAmount);
command.Parameters.AddWithValue(CardTypeField, order.CardType);
command.Parameters.AddWithValue(CavvField, order.Cavv);
command.Parameters.AddWithValue(CurrencyField, order.Currency);
command.Parameters.AddWithValue(CustomerEmailField, order.CustomerEmail);
command.Parameters.AddWithValue(Cv2ResultField, order.Cv2Result);
command.Parameters.AddWithValue(DeclineCodeField, order.DeclineCode);
command.Parameters.AddWithValue(DeliveryAddress1Field, order.DeliveryAddress1);
command.Parameters.AddWithValue(DeliveryAddress2Field, order.DeliveryAddress2);
command.Parameters.AddWithValue(DeliveryCityField, order.DeliveryCity);
command.Parameters.AddWithValue(DeliveryCountryField, order.DeliveryCountry);
command.Parameters.AddWithValue(DeliveryFirstnamesField, order.DeliveryFirstnames);
command.Parameters.AddWithValue(DeliveryPhoneField, order.DeliveryPhone);
command.Parameters.AddWithValue(DeliveryPostCodeField, order.DeliveryPostCode);
command.Parameters.AddWithValue(DeliveryStateField, order.DeliveryState);
command.Parameters.AddWithValue(DeliverySurnameField, order.DeliverySurname);
command.Parameters.AddWithValue(ExpiryDateField, order.ExpiryDate);
command.Parameters.AddWithValue(FraudResponseField, order.FraudResponse);
command.Parameters.AddWithValue(GiftAidField, order.GiftAid);
command.Parameters.AddWithValue(Last4DigitsField, order.Last4Digits);
command.Parameters.AddWithValue(LastUpdatedField, order.LastUpdated);
command.Parameters.AddWithValue(PayerIdField, order.PayerId);
command.Parameters.AddWithValue(PayerStatusField, order.PayerStatus);
command.Parameters.AddWithValue(PostCodeResultField, order.PostCodeResult);
command.Parameters.AddWithValue(RelatedVendorTxCodeField, order.RelatedVendorTxCode);
command.Parameters.AddWithValue(SecurityKeyField, order.SecurityKey);
command.Parameters.AddWithValue(StatusField, order.Status);
command.Parameters.AddWithValue(StatusMessageField, order.StatusMessage);
command.Parameters.AddWithValue(SurchargeField, order.Surcharge);
command.Parameters.AddWithValue(ThreeDSecureStatusField, order.ThreeDSecureStatus);
command.Parameters.AddWithValue(TokenIdField, order.TokenId);
command.Parameters.AddWithValue(TransactionTypeField, order.TransactionType);
command.Parameters.AddWithValue(TxAuthNoField, order.TxAuthNo);
command.Parameters.AddWithValue(VpsTxIdField, order.VpsTxId);
}
答
你必须使用@
为SQL α参数。也许这可以解决你的问题,但我必须承认,我不理解查询,因为列名与值相同。然而...
string sql = @"UPDATE Orders SET {0}
Where {1}[email protected]{1};";
sql = string.Format(sql
, string.Join(",", FieldNames.Select(field => string.Format("{0}[email protected]{0}", field)))
, VendorTxCodeField);
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
for (int i = 0; i < FieldNames.Count; i++)
{
cmd.Parameters.AddWithValue(FieldNames[i], FieldNames[i]);
}
// open connection and execute the command...
}
+0
代码来自SagePay我只是试图让它与我们的SQL Server一起工作,一旦我离开这次会议,我会尝试您的建议,谢谢。 – iggyweb 2014-10-31 10:15:44
您最后会遇到类似'SET VendorTxCodeField =?VendorTxCodeField'的声明,这是无效的。将'?'更改为'@',以便最终生成'SET VendorTxCodeField = @ VendorTxCodeField'。您还应该谨慎使用[AddWithValue](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/),明确指出类型(例如'Parameters.Add(“@ Name”,SqlDbType.VarChar,50).Value =“Some Parameter”)'虽然更详细,但更安全。 – GarethD 2014-10-31 09:48:11
我现在正在收到以下错误:'参数化查询'(@VendorTxCode nvarchar(36),@AddressResult nvarchar(4000),@Address'期望参数'@AddressResult',它没有提供。“'所以我正确在说我需要用适当的数据类型修改'AddOrderParameters'? – iggyweb 2014-10-31 10:05:44
如果你像'Parameters.AddWithValue(“@ Test”,null)','@ Test'这样做的话,通常这是一个空参数值。你需要检查空参数,并可能做类似“Parameters.Add(”@ Test“,SqlDbType.VarChar,50).Value = String.IsNullOrEmpty(order.AddressResult)?DbNull .Value:(object)order.AddressResult;'或者在需要的时候添加一个空字符串 – GarethD 2014-10-31 10:12:14