更新查询中出现语法错误

本文关键字：语法 错误 查询 更新 | 更新日期: 2023-09-27 18:18:53

语法错误。

字符字符串'OKWhere '后的未闭引号UerName =山姆"。IncorrectSysntax near 'OKWhere UserName=sam'.

代码:

cmd.CommandText = "UPDATE SystemInfo SET" + " UserName='" + UserName + "',
UserDomainName='" + UserDomainName + "',UserMachineName='" + UserMachineName 
+"',UserIP='" + UserIP + "', UserOsVersion='" + UserOsVersion + 
"',UserSystemDirectory='" + UserSystemDirectory + "',UserCurrentDirectory='" + 
UserCurrentDirectory + "', ProcessorName='" + ProcessorName + "', 
ProcessMnufacturer='" + ProcessMnufacturer + "',ProcessorID='" + ProcessorID + 
"',ProcessorDescription='" + ProcessorDescription + "',ProcessorVersion='" + 
ProcessorVersion + "',ProcessorStatus='" + ProcessorStatus + "',ProcessorDeviceId='" + 
ProcessorDeviceId + "', OSCaption='" + OSCaption + "',OSSerialNumber='" +  
SSerialNumber + "',OSManufacturer ='" + OSManufacturer + "',OSVersion='" + OSVersion + 
"', OSStatus='" + OSStatus + "',OSName='" + OSName + "', BiosName='" + BiosName + 
"',BiosVersion='" + BiosVersion + "',BiosSerialNumber='" + BiosSerialNumber + "', 
BiosManufacturer='" + BiosManufacturer + "',BiosCurrentlanguage='" + 
BiosCurrentlanguage + "', BiosStatus='" + BiosStatus + "Where UserName=" + 
UserName.ToString ();

更新查询中出现语法错误

BiosStatus + "Where UserName="

在WHERE子句之前需要一个空格，并在UserName周围添加引号，它应该看起来像:

BiosStatus + " Where UserName='" + UserName.ToString() + "'"

作为额外的注意事项，尝试使用参数化查询。这将防止SQL Injection攻击。您可以通过执行以下操作来实现此目的:

command.CommandText = "UPDATE TABLE " +  
         "SET BiosStatus = $BiosStatus, BiosManufacturer = $BiosManufacturer " +  
         "WHERE UserName = $UserName";  
 command.Parameters.AddWithValue("$BiosStatus", BiosStatus);  
 command.Parameters.AddWithValue("$BioManufacturer", BiosManufacturer);  
 command.Parameters.AddWithValue("$UserName", UserName);

参数。总是总是参数。实际上，由于所有的输入似乎都是当前实例(this)的属性，像"dapper"这样的工具可以真的在这里帮助您:

conn.Execute(@"UPDATE SystemInfo SET
UserName=@UserName,
UserDomainName=@UserDomainName,
UserMachineName=@UserMachineName,
UserIP=@UserIP,
-- ...lots skipped...
BiosCurrentlanguage=@BiosCurrentlanguage,
BiosStatus=@BiosStatus
Where UserName=@UserName", this);

this作为第二个参数使用当前实例的所有属性，这些属性存在于SQL中来添加参数，因此它将添加@UserName, @UserDomainName等…

当然，您可以对原始ADO手动执行相同的操作。NET -这只是更多的工作:

cmd.CommandText = @"UPDATE SystemInfo SET
UserName=@UserName,
UserDomainName=@UserDomainName,
UserMachineName=@UserMachineName,
UserIP=@UserIP,
-- ...lots skipped...
BiosCurrentlanguage=@BiosCurrentlanguage,
BiosStatus=@BiosStatus
Where UserName=@UserName";
cmd.Parameters.AddWithValue("UserName", ((object)UserName) ?? DBNull.Value);
cmd.Parameters.AddWithValue("UserDomainName", ((object)UserDomainName) ?? DBNull.Value);
// ...lots skipped...
cmd.Parameters.AddWithValue("BiosCurrentlanguage", ((object)BiosCurrentlanguage) ?? DBNull.Value);
cmd.Parameters.AddWithValue("BiosStatus", ((object)BiosStatus) ?? DBNull.Value);
cmd.ExecuteNonQuery();

尝试更正

形式
BiosStatus + "Where UserName="


BiosStatus + " Where UserName='"+  UserName.ToString ()+ "'";
相关文章：