An OleDbParameter with ParameterName is not contained by this OleDbParameterCollection

Question

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."

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>

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>

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();

}

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?

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.

Thanks so much for any guidance.

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.

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);
}

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.

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.

Answer 1

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.

It should look something like this:

<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;
}

Note a few things:

• 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".

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/enus/library/vstudio/z72eefad(v=vs.100).aspx