与参数名称的OleDbParameter不受此OleDbParameterCollection载 [英] An OleDbParameter with ParameterName is not contained by this OleDbParameterCollection

问题描述

非常简单的形式就像一个十余人，我为内部工具进行的，但是这一次踢回错误与参数名称'@lookup不受此OleDbParameterCollection包含了一个OleDbParameter。

Very simple form like a dozen others I've made for internal tools, but this one kicks back the error "An OleDbParameter with ParameterName '@lookup' is not contained by this OleDbParameterCollection."

唯一的区别，而且它可能是关键，但我很害怕，我不知道答案，是连接字符串。我一直使用的是Windows 2008 R2服务器上的SQL 2008服务器，所以我我的连接字符串已经很容易地在我的Visual Studio 2008的配置如下图所示：

The only difference, and it's probably the key to this, but I'm afraid I don't know the answer, is the connection string. I have been using a SQL 2008 server on a Windows 2008 R2 server so I my connection string has been easily configured in my Visual Studio 2008 like below:

<connectionStrings>
    <add name="DatasourceName" connectionString="server=servername;database=databasename;user=username;password=password;" providerName="System.Data.SqlClient"/>
</connectionStrings>

不过，我仍然使用VS 2008，但我连接到SQL 2012服务器的数据源我，所以我不得不改变连接字符串：

However, I'm still using VS 2008 but I'm connecting to a SQL 2012 server for my datasource so I had to change the connection string to:

    <connectionStrings>
        <add name="server2012name" connectionString="Provider=SQLNCLI11.1;Data Source=ServerName;Persist Security Info=True;Password=password;User ID=userid;Initial Catalog=catalog"
        providerName="System.Data.OleDb" />
    </connectionStrings>

所以，因为我从来没有见过这个错误，我不知道code我使用不因某些原因OLEDB连接工作。违规件code是在codebehind：

So since I've never seen this error, I wonder if the code I'm using does not work with an oledb connection for some reason. The offending piece of code is in the codebehind:

    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["@lookup"].Value = lookup.Text.ToString();

}

下面是ASPX code：

Here is the ASPX code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MyString %>" 
        ProviderName="<%$ ConnectionStrings:MyString.ProviderName %>" 
        SelectCommand="SELECT [Beneficiary First Name] AS firstname
                            ,[Beneficiary Last Name] AS lastname
                            ,[Beneficiary Date of Birth] AS dob
                            ,[Address Line 1] AS [address]
                            ,[Beneficiary Encounter Date] AS encdate
                            ,[Beneficiary Data Sharing Preference Code] AS shareprefcode
                            ,[Beneficiary Data Sharing Decision Mechanism Code] AS decmechcode
                            ,[Beneficiary Alcohol and Drug Treatment Data Sharing Mechanism Co] AS damechcode
                            ,[MRN]
                            ,[Beneficiary HICN] AS hicn
                       FROM [database].[dbo].[table]
                       WHERE [MRN] = @lookup"

           onselecting="SqlDataSource1_Selecting"
           >
         <SelectParameters>
             <asp:Parameter Name="lookup" />
         </SelectParameters>
         </asp:SqlDataSource>

我需要一些其他的组件包括在内？难道我用不同的命令来添加参数？

Do I need some other assembly included? Do I use a different command to add that parameter?

我GOOGLE了这几个小时，通过什么我发现读，但没有它似乎涉及到这一点。如果我赞扬这个e.Command.Parameters线路输出，错误消失，但查询将不会返回任何东西。

I've Googled this for hours and read through what I found, but none of it seemed to pertain to this. If I commend this e.Command.Parameters line out, the error goes away, but the query won't return anything.

非常感谢任何指导。

编辑
不幸的是，它看起来像这样人会去解答。我万一有人谁知道这个是什么都在这里增添了几分一起约来了。

EDIT Unfortunately, it looks like this one's going to go unanswered. I'll add a little more here in case someone who knows what this is all about comes along.

我一直在阅读所有关于OleDbParameters这似乎大致在正确的轨道。我已经改变了codebehind约50的这个变化：

I've been reading all about OleDbParameters and that seems to be roughly the right track. I've changed the codebehind to about 50 variations of this:

    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    OleDbParameter fnParam = new OleDbParameter("@lookup", OleDbType.VarChar, 20);
    fnParam.Value = lookup.Text;
    e.Command.Parameters.Add(fnParam);
}

和我已成功地移过原来的错误。我已经改变了ASPX去除参数声明，没有去。我在SQL查询到一个问号，没有去改变参数。在这一点上，我得到任何形式的错误或消息。在我的SQL事件探查器跟踪，它不打的SQL Server在所有。它只是没有做任何事情。

And I have managed to move past the original error. I've changed the ASPX to remove the parameter declaration, no go. I've changed the parameter in the SQL query to a question mark, no go. At this point, I get no error or message of any kind. In my SQL Profiler trace, it's not hitting the SQL server at all. It's just not doing anything.

在我的一些变化，它实际上给了我数据类型不匹配错误，但我从来没有弄清楚如何改变文本框的值是正确的为OleDbType的数据类型。

In some of my variations, it actually gave me errors for data type mismatches, but I never could figure out how to alter the textbox value to be the right kind of data type for the OleDbType.

推荐答案

根据您的更新，我怀疑你是非常接近，做正确的事情。你拥有的主要问题是，您正在使用的OLE DB提供程序，它不支持在其查询命名参数。相反，你必须使用位置paramters，并明确指定，以正确的顺序，因为适当的参数集合中的ASP的一部分。

Based on your updates, I suspect you are very close to doing the correct thing. The main problem you have is that you are using the OLE DB provider, which doesn't support named parameters in its queries. Instead, you have to use positional paramters, and specify them explicitly, in the correct order, as part of the appropriate parameters collection in ASP.

它应该是这个样子：

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:MyString %>" 
    ProviderName="System.Data.OleDb" 
    SelectCommand="SELECT list_of_columns
                   FROM [database].[dbo].[table]
                   WHERE column = ?"
    OnSelecting="SqlDataSource1_Selecting">
     <SelectParameters>
         <asp:Parameter Name="lookup" Type="Int32" />
     </SelectParameters>
 </asp:SqlDataSource>


protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["@lookup"].Value = 1234;
}

请注意以下几点：

• 查询中的参数仅仅是一个？。这些位置：他们是为了通过您稍后定义的参数代替
。

• 的 SelectParameters 收藏有相同数量的条目，以相同的顺序，如查询有？占位符。在这种情况下，只有一个，但如果你有多个参数，你会为了定义它们。如果你想多次使用相同的值，你仍然需要指定多个参数，并分别设置它们。


• 您使用查找参数集合中的名称是您给它 SelectParameters 集合中的名字，以@后缀。


• 据我所知，您当你做到这一点，必须指定正确的参数类型。我总是有，至少，它的为我工作。

• The parameter in the query is just a '?'. These are positional: they are replaced in order by the parameters you define later.
• The SelectParameters collection has the same number of entries, in the same order, as the query has '?' placeholders. In this case, there's just one, but if you had multiple parameters you'd define them in order. If you want to use the same value multiple times, you still need to specify multiple parameters and set them all individually.
• The name you use to look up the parameter in the collection is the name you give it in the SelectParameters collection, with an "@" appended.
• As far as I know, you must specify the correct parameter type when you do this. I always have, at least, and it's "worked for me".

看起来，在某些时候，你尝试过这样的事情，但没有看到$ C $的每一次迭代c您试过，我不能肯定。无论如何，这是OLE DB如何参数化查询工作，所以这应该是你所需要的。如果没有，请与尝试这种出来的结果更新你的问题。

It looks like, at some point, you tried something like this, but without seeing every iteration of code you tried, I can't be sure. At any rate, this is how OLE DB parameterized queries work, so this should be what you need. If not, please update your question with the results of trying this out.

有关更多信息，请参见：

For more information, see:

http://msdn.microsoft。 COM /美式英文/库/ vstudio / z72eefad（v = VS.100）的.aspx

这篇关于与参数名称的OleDbParameter不受此OleDbParameterCollection载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！