Is Order Of OleDbCommand.Parameters Important

01-08-2012

The order of the parameters collection of an OleDbCommand must match the order to which those parameters appear in the SQL statement.

Otherwise the .net framework will try to fill the parameters in your SQL statement according to the order of parameters in the OleDbCommand.Parameters collection.

This can cause an Data type mismatch in criteria expression. Exception if the data types mismatch or at worst, if those data types match, it will write wrong data to the wrong column.

Following is an example:


// CORRECT CODE (params are in the correct order)

    string sqlU = "update expense set Item = @item, Amount = @amount, Quantity = @quantity, UnitPrice = @uprice, ExpenseDate = @edate, Unit = @unit, SupplierId = @sid, Importance = @importance, CategoryId = @cid where Id = @id";

    Dictionary<string, object> parameters = new Dictionary<string, object>();  // don't worry about this dictionary as each keyvalue pair is converted to an OleDbParameter later.
    parameters.Add("item", this.Item);
    parameters.Add("amount", this.Amount);
    parameters.Add("quantity", this.Quantity);
    parameters.Add("uprice", this.UnitPrice);
    parameters.Add("edate", this.Date.ToShortDateString());
    parameters.Add("unit", this.Unit.ToString());
    parameters.Add("sid", this.Supplier.Id);
    parameters.Add("importance", this.Importance.ToString());
    parameters.Add("cid", this.Category.Id);
    parameters.Add("id", this.Id);


// WRONG CODE (in wrong order)

    string sqlU = "update expense set Item = @item, Amount = @amount, Quantity = @quantity, UnitPrice = @uprice, ExpenseDate = @edate,Unit = @unit, SupplierId = @sid, Importance = @importance, CategoryId = @cid where Id = @id";

    Dictionary<string, object> parameters = new Dictionary<string, object>();
    parameters.Add("amount", this.Amount);  // order is different
    parameters.Add("item", this.Item);    // order is different
    parameters.Add("quantity", this.Quantity);
    parameters.Add("uprice", this.UnitPrice);
    parameters.Add("edate", this.Date.ToShortDateString());
    parameters.Add("unit", this.Unit.ToString());
    parameters.Add("sid", this.Supplier.Id);
    parameters.Add("importance", this.Importance.ToString());
    parameters.Add("cid", this.Category.Id);
    parameters.Add("id", this.Id);

Menol
ILT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: