由于最近一个小项目,用户指定要Access,所以没发,只得Access了。显然采用OleDb那连接。在今天的开发过程中,遇见了一个问题:
问题:
在dal层的数据sql采用参数化形式,在Access数据库查询界面执行时候,没有错误,执行正常。但是在程序执行,就会出现程序不报任何错误,但是Update不成功,受影响的行数为0.
在我网上查询了许久,http://blog.sina.com.cn/s/blog_4668898b010008io.html( OLEDB传递参数给Access要注意的问题)
解决:
其实问题病因在于:你的sql里面参数顺序和在oledb参数添加的顺序不一致。ok。我原来的code: 注意:UserIdentity的出现位置。
我原来的code:
注意:UserIdentity的出现位置。
代码
string
sql
=
@"
update UserInfo set
[RoleID] = @RoleID,
[UserName] = @UserName,
[PWD] = @PWD,
[Email] = @Email
where [UserIdentity] = @UserIdentity " ;
DbCommand cmd = dbHelper.GetSqlStringCommond(sql
);
dbHelper.AddParameterCollection(cmd,
new OleDbParameter( " UserIdentity " , user.UserIdentity), // 注意UserIdentity放置位置
new OleDbParameter( " RoleID " , user.RoleID),
new OleDbParameter( " UserName " , user.UserName),
new OleDbParameter( " PWD " , user.PWD),
new OleDbParameter( " Email " , user.Email)
);
[RoleID] = @RoleID,
[UserName] = @UserName,
[PWD] = @PWD,
[Email] = @Email
where [UserIdentity] = @UserIdentity " ;
DbCommand cmd = dbHelper.GetSqlStringCommond(sql
);
dbHelper.AddParameterCollection(cmd,
new OleDbParameter( " UserIdentity " , user.UserIdentity), // 注意UserIdentity放置位置
new OleDbParameter( " RoleID " , user.RoleID),
new OleDbParameter( " UserName " , user.UserName),
new OleDbParameter( " PWD " , user.PWD),
new OleDbParameter( " Email " , user.Email)
);
修改后的Code(运行成功):
代码
string
sql
=
@"
update UserInfo set
[RoleID] = @RoleID,
[UserName] = @UserName,
[PWD] = @PWD,
[Email] = @Email
where [UserIdentity] = @UserIdentity " ;
DbCommand cmd = dbHelper.GetSqlStringCommond(sql
);
dbHelper.AddParameterCollection(cmd,
new OleDbParameter( " RoleID " , user.RoleID),
new OleDbParameter( " UserName " , user.UserName),
new OleDbParameter( " PWD " , user.PWD),
new OleDbParameter( " Email " , user.Email),
new OleDbParameter( " UserIdentity " , user.UserIdentity) // 注意UserIdentity放置位置
);
[RoleID] = @RoleID,
[UserName] = @UserName,
[PWD] = @PWD,
[Email] = @Email
where [UserIdentity] = @UserIdentity " ;
DbCommand cmd = dbHelper.GetSqlStringCommond(sql
);
dbHelper.AddParameterCollection(cmd,
new OleDbParameter( " RoleID " , user.RoleID),
new OleDbParameter( " UserName " , user.UserName),
new OleDbParameter( " PWD " , user.PWD),
new OleDbParameter( " Email " , user.Email),
new OleDbParameter( " UserIdentity " , user.UserIdentity) // 注意UserIdentity放置位置
);