Oledbparameters and Parameter Names

OleDbParameters and Parameter Names

Parameter NAMES are generic in the SQL support system (i.e. not OleDb specific). Pretty much ONLY OleDb / Odbc do NOT use them. They are there because OleDb is a specific implementation of the generic base classes.

OleDb Parameters Array Format

The code in the Microsoft Example is wrong and not compilable. I have already sent a feedback about it. Here a more correct version with annotations about what to change.

public void CreateMyOleDbCommand(OleDbConnection connection,
string queryString, OleDbParameter[] parameters)
{
OleDbCommand command = new OleDbCommand(queryString, connection);

// This destroys the commandtext set by querystring in the constructor.
// command.CommandText =
// "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";

// This cannot work, you can't add an array with Add but only one element
// command.Parameters.Add(parameters);
command.Parameters.AddRange(parameters);

// After AddRange this loop is pointless and creates possible errors with
// too many parameters added to the collection
// for (int j=0; j<parameters.Length; j++)
//{
// command.Parameters.Add(parameters[j]) ;
//}

string message = "";
for (int i = 0; i < command.Parameters.Count; i++)
{
message += command.Parameters[i].ToString() + "\n";
}
Console.WriteLine(message);
}

Now to call this function you have

string cmdText = @"SELECT CustomerID, CompanyName 
FROM Customers
WHERE Country = ? AND City = ?";
OleDbParameter[] parameters = new OleDbParameter[]
{
new OleDbParameter{ ParameterName = "?",OleDbType = OleDbType.VarWChar, Value = "UK" },
new OleDbParameter{ ParameterName = "?",OleDbType = OleDbType.VarWChar, Value = "London"},
};
CreateMyOleDbCommand(myConnection, cmdText, parameters);

An OleDbParameter with ParameterName is not contained by this OleDbParameterCollection

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

Oledb update-command with parameters not working on access-db

Your parameters are not in the correct order. These 2 loops should be switched in the order they appear in the code.

This matters, the OleDbCommand does not use named parameters. They are ordinal based on the position they occur in the sql statement.

Documentation - OleDbCommand.Parameters

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

...

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text


Your code:

// should occur 2nd
foreach (var whereParameter in whereParameterizer.GetParameters())
{
whereBuilder.Append($" and {whereParameter.ParameterName} = @{whereParameter.ParameterName}");
parameterList.Add(whereParameter);
}

// should occur 1st
foreach (var updateParameter in updateParameterizer.GetParameters())
{
updateBuilder.Append($", {updateParameter.ParameterName} = @{updateParameter.ParameterName}");
parameterList.Add(updateParameter);
}


Related Topics



Leave a reply



Submit