解决在Access中参数化SQL语句更新数据库失败的问题

Access数据库真是一个古董数据库，它的SQL语句语法跟标准的SQL server的ＳＱＬ语句有许多不同之处（例如Access中是用#包括日期参数，其它数据库是用单引号包括日期参数）．最近又发现了一个问题：在Access中参数化SQL更新数据库总是失败！认真检查了，没有任何语法错误，看下面代码：

 

后来请教别人才知道，Access数据库参数化SQL语句竟然要求非常严格的？！ＳＱＬ语句里的参数顺序要跟程序代码里调用的顺序是完全一致！就如我上面举的代码例子，参数CompanyId是第一个加入了Parameter列表中，但是在它SQL语句中的位置是最末端（where CompanyId=@CompanyId），因此我们这样执行语句就不正确了，执行后的影响条数总是0．

解决方案是，将红色语句删掉，留下绿色语句，执行就正确了．

 1 /// <summary>
 2         /// 更新一条数据
 3         /// </summary>
 4         public void Update(Company model)
 5         {
 6             StringBuilder strSql = new StringBuilder();
 7             strSql.Append("update t_Company set ");
 8             strSql.Append("CompanyName=@CompanyName,");
 9             strSql.Append("Address1=@Address1,");
10             strSql.Append("Address2=@Address2,");
11             strSql.Append("Address3=@Address3,");
12             strSql.Append("Address4=@Address4,");
13             strSql.Append("FacSimile=@FacSimile,");
14             strSql.Append("Website=@Website,");
15             strSql.Append("Origin=@Origin,");
16             strSql.Append("CreateDate=@CreateDate");
17             strSql.Append(" where CompanyId=@CompanyId ");
18             Database db = DatabaseFactory.CreateDatabase();
19             DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
20             db.AddInParameter(dbCommand, "CompanyId", DbType.Int32, model.CompanyId);
21             db.AddInParameter(dbCommand, "CompanyName", DbType.AnsiString, model.CompanyName);
22             db.AddInParameter(dbCommand, "Address1", DbType.AnsiString, model.Address1);
23             db.AddInParameter(dbCommand, "Address2", DbType.AnsiString, model.Address2);
24             db.AddInParameter(dbCommand, "Address3", DbType.AnsiString, model.Address3);
25             db.AddInParameter(dbCommand, "Address4", DbType.AnsiString, model.Address4);
26             db.AddInParameter(dbCommand, "FacSimile", DbType.AnsiString, model.FacSimile);
27             db.AddInParameter(dbCommand, "Website", DbType.AnsiString, model.Website);
28             db.AddInParameter(dbCommand, "Origin", DbType.AnsiString, model.Origin);
29             db.AddInParameter(dbCommand, "CreateDate", DbType.DateTime, model.CreateDate);
30             //db.AddInParameter(dbCommand, "CompanyId", DbType.Int32, model.CompanyId);
31             db.ExecuteNonQuery(dbCommand);
32         }

转载于:https://www.cnblogs.com/techmango/archive/2011/05/23/Access_SQL_parameter_execute_can_not_update_database.html