“参数的数量与存储过程的值的数量不匹配” System.Data.Common.DbCommand 的细微差别
这可能是 SQL Server 数据库设置问题，但我不确定从哪里开始寻找。
我有一个存储过程：
CREATE PROCEDURE aStoredProcedure
@dteSince DATETIME = null
AS
...
调用该存储过程的 C# 代码是：
using (IDataReader dr = database.ExecuteReader("aStoredProcedure"))
{
...
C# 代码在生产环境中运行良好。 这里，dteSince 将被设置为 null，因为它没有作为输入参数提供。
问题是当我在 UAT 环境中运行它时，它返回标题中显示的消息 - 参数数量错误...
我可以通过将代码更改为来解决此问题：
using (IDataReader dr = database.ExecuteReader (CommandType.StoredProcedure, "aStoredProcedure"))
{
...
修复只是掩盖了真正的问题，即代码如何在一种环境下失败而在另一种环境下正常工作。
我已经删除并重新创建了存储过程，但没有运气。
使用的技术是 C# 2.0 & SQL Server 2005。
This could be an sql server database setup issue, but I am not to sure where to start looking.
I have a stored procedure:
CREATE PROCEDURE aStoredProcedure
@dteSince DATETIME = null
AS
...
The C# code to call the stored procedure is:
using (IDataReader dr = database.ExecuteReader("aStoredProcedure"))
{
...
The C# code works fine on the Production environment. Here, dteSince will be set to null as it is not supplied as a input parameter.
Problem is when I run this in the UAT environment, it returns the message shown in the title - wrong number of parameters...
I can fix this by changing the code to:
using (IDataReader dr = database.ExecuteReader (CommandType.StoredProcedure, "aStoredProcedure"))
{
...
The fix just masks the real problem as to how can the code fails on one environment and works fine on another.
I have dropped and recreated the stored procedure, with no luck.
Technologies used C# 2.0 & Sql Server 2005.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
恕我直言，无论如何都应该指定 CommandType - 显式总是优于隐式。 很明显，在两种环境中，幕后发生的事情是不同的，但是当您明确命令类型时，代码会按预期工作。
我唯一能想到的 - 数据库设置是否完全相同 - 特别是在处理 NULL 时？
编辑
可能值得检查
SET ANSI_NULLS ON/OFF和SET CONCAT_NULL_YIELDS_NULLON/OFF - 您可以查看两个数据库的属性，看看是否设置匹配。IMHO the CommandType should always be specified anyway - explicitness is always preferable to implicitness. It is obvious that something is happening under the hood that is different in both environments but when you are explicit about the command type the code works as expected.
The only thing I can think of - are the database settings exactly the same -particularly in the treatment of NULL's?
EDIT
May be worth checking
SET ANSI_NULLS ON/OFFandSET CONCAT_NULL_YIELDS_NULLON/OFF - you can look at the properties of both databases and see if the setting match.以下是我通常尝试的一些故障排除技巧。
一切仍然不好，干净并重新构建解决方案
侧面评论：Beh，我不认为“System.Data.IDataReader”包含在所使用的技术中；）
Here are some of the troubleshooting tips I usually try.
And all's still not well, clean and Rebuild solution
side comment: Beh, I don't think "System.Data.IDataReader" is to be included in technology used ;)
您可以使用 SQL Profiler 从 SQL Server 角度查看发生的情况。
理想的情况是让某人从生产中捕获痕迹，以便进行比较。
You can use SQL Profiler to see what is happening on the SQL Server perspective.
Ideally getting someone to capture a trace from production as well so they can be compared.