参数"@名称"；在集合中找不到 [英] Parameter "@Name" not found in the collection

问题描述

我最近遇到过这个解决方案，这是我尝试从多个表中选择在三个字段上输入可变或空值的另一个问题的一部分. 这是原始帖子.

I have come across this solution recently as part of another problem I was having when trying to select from multiple tables which variable or null input on three fields. Here is the original post.

不幸的是，我仍然对ADO.NET有点陌生，并且在运行代码时遇到此错误:

Unfortunately I am still a little unfamiliar with ADO.NET and I am getting this error when I run my code:

Parameter "@AreaName" not found in the collection

以下是查询的代码:

mySqlConnect.Open();

mySqlCommand = mySqlConnect.CreateCommand();
mySqlCommand.CommandText = "SELECT * FROM PlantAreaCodes WHERE (@AreaCode IS NULL OR AreaCode LIKE " + PModel.AreaCode + " OR @AreaName IS NULL OR LIKE " + PModel.AreaName + " OR @Comments IS NULL OR Comments LIKE " + PModel.Comments + ";";
mySqlCommand.Parameters["@AreaCode"].Value = (string.IsNullOrEmpty(Convert.ToString(PModel.AreaCode)) ? (object)DBNull.Value : PModel.AreaCode);
mySqlCommand.Parameters["@AreaName"].Value = (string.IsNullOrEmpty(PModel.AreaName) ? (object)DBNull.Value : PModel.AreaName);
mySqlCommand.Parameters["@Comments"].Value = (string.IsNullOrEmpty(PModel.Comments) ? (object)DBNull.Value : PModel.Comments);
mySqlReader = mySqlCommand.ExecuteReader();

传入的值是:

PModel.AreaCode = 110;
AreaName = null;
Comments = null;

我之前已经完成了一次，但是不幸的是我最近丢失了一段代码，不得不重新构建.

I have completed this once before, however I unfortunately lost a segment of my code recently and have had to rebuild.

推荐答案

您需要先添加参数，然后再指定值:

You need to add the parameters before specifying a value:

mySqlCommand.Parameters.Add("@AreaName", SqlDbType.VarChar);

mySqlCommand.Parameters["@AreaName"].Value =
    (string.IsNullOrEmpty(Convert.ToString(PModel.AreaName))
        ? (object)DBNull.Value : PModel.AreaName));

考虑使用以下备用语法:

Consider using this alternate syntax:

mySqlCommand.Parameters.AddWithValue("@AreaName",
    (string.IsNullOrEmpty(Convert.ToString(PModel.AreaName))
        ? (object)DBNull.Value : PModel.AreaName));

您可以在此外，您还对查询字符串做了一些时髦的事情.这应该解决它:

Also, you've done something funky with the query string. This should fix it:

mySqlCommand.CommandText =
    "SELECT * FROM PlantAreaCodes WHERE (AreaCode IS NULL OR AreaCode LIKE @AreaCode OR AreaName IS NULL OR AreaName LIKE @AreaName OR Comments IS NULL OR Comments LIKE @Comment;";

要使LIKE正常工作，添加参数时，可能必须在参数值周围添加%符号:

To get the LIKEs to work correctly, you'll probably have to add % symbols around your parameter values when you're adding them:

var areaName = (string.IsNullOrEmpty(Convert.ToString(PModel.AreaName))
                   ? (object)DBNull.Value : PModel.AreaName)

mySqlCommand.Parameters.AddWithValue("@AreaName", "%" + areaName + "%");

这篇关于参数"@名称"；在集合中找不到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！