1

Hi in the OleDB documentation (linked below) they have a parameter called OleDbParameter[] parametersin a functions, a loop then iterates over that array to insert the parameters into the SQL.

I can't find in the documentation how you are supposed to format that array?

Docs: https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-3.1

5
  • 1
    how you are supposed to format that array? ← What do you mean by format? Commented May 7, 2020 at 17:02
  • If I ran the function CreateMyOleDbCommand from the documentation, what would I need to put into the OleDbParameter[] parameters parameter. For example what would I need to do to put UK and London into that SQL query in the example in the docs Commented May 7, 2020 at 17:07
  • It is an array of OleDbParameter. You declare a variable of that type with the size you need then you build the parameters one by one and insert them in the array, then pass the array to the function Commented May 7, 2020 at 17:10
  • ^-- What Steve said and keep in mind that the order of the parameters in the array must match the order they appear in the sql statement. Commented May 7, 2020 at 17:11
  • 1
    By the way that example is wrong and not compilable. You cannot add an array to a Parameters collection with Add, but you should use AddRange (and this makes the following loop useless) Commented May 7, 2020 at 17:13

1 Answer 1

2

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);
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.