Oledbcommand Parameters Order and Priority

OleDbCommand parameters order and priority

According to http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx OleDbCommand does not support named parameter

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. For example:

SELECT * FROM Customers WHERE CustomerID = ?

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.

So order of parameter is important.

OleDBCommand, multiple commands

The way this would normally be handled in a database environment is with a transaction. This would cause a series of commands to succeed or fail as a block, and if an error occurs in the middle it rolls back everything to the initial state.

I've never done this personally with Access (done it many times with SQL Server, MySql, Postgres, etc), but there is an OleDbConnection.BeginTransaction. There is a very nice example on this page showing how to use it.

Based on a comment in the MSDN forums, it appears this works with Access' Jet database engine:

Hi,

Indeed Microsoft Jet doesn't support System.Transactions and TransactionScope.

If you still want to use transactions, you can use native OleDB transactions: http://msdn2.microsoft.com/en-us/library/93ehy0z8.aspx

Cheers,

Florin

(credit Florin Lazar - MSFT)

An alternative solution...

would be to do this operation in a single update command, instead of a delete followed by an insert. Something like this:

cmd.CommandText = "UPDATE IncrementingNumberTable set [Num] = [Num] + 1 WHERE [Num]=" + curr + ";";

Why isn't OleDbCommand and OleDbType.Date not working, and no error?

You've got quotes round most of your parameters in the SQL, which means the other parameters won't be where you expect them to be. Your SQL should be:

UPDATE Loads
SET Customer=?, FinishTime=?, Carrier=?, Reference=?, Tags=?, Status='Received'
WHERE LoadID = ?

Additionally, I would strongly advise against passing all the data around as strings. Don't convert the DateTime values into strings at all if you can help it, and certainly not in the SQL statement. You've currently got:

cmd.Parameters.Add(new OleDbParameter("?", OleDbType.Date) { Value = tmp.ToString() });

This would be better as:

cmd.Parameters.Add("?", OleDbType.Date).Value = tmp;

... but it would be better if you didn't even need to parse the string to get tmp to start with. (It's not really clear where the date is coming from, and whether it has to be as a string there.)

The more date/string conversions you have, the more room there is for the format and culture to mess things up - you only need one step to format the date in a way that the next step parsing it doesn't expect for the whole thing to fail.

Parameter ?_1 has no default value error when ExecuteReader

The question in fact had a wrong assumption and that was that there was an error in the code.

The syntax of the SQL query was correct and the parameter was being inserted correctly. However the test data contained errors and therefore no result was being returned by a correctly formatted query.

Thanks all for input.

OleDbDataAdapter for MS Access updating table, why doesn't my update work?

Try reversing the order in which you are adding the parameters:

using (OleDbCommand updateCommand = con.CreateCommand()) 
{ 
   updateCommand.CommandType = CommandType.Text;
   updateCommand.CommandText = "update theses set filename = @newFileName where thesisID = @ID"; 
   updateCommand.Parameters.AddWithValue("@newFileName", newFileName);
   updateCommand.Parameters.AddWithValue("@ID", ID);   
   updateCommand.ExecuteNonQuery(); 
} 

The reason for this is that OleDb doesn't support named parameters, so the order in which you add them is important.

Note that it is often common to see OleDb queries expressed this way:

using (OleDbCommand updateCommand = con.CreateCommand()) 
{ 
   updateCommand.CommandType = CommandType.Text;
   updateCommand.CommandText = "update theses set filename = ? where thesisID = ?"; 
   updateCommand.Parameters.Add(new OleDbParameter("", "", ""...));
   updateCommand.Parameters.Add(new OleDbParameter("", "", ""...));
   updateCommand.ExecuteNonQuery(); 
} 

This emphasises that the order is important - the question marks are merely placeholders which get replaced in the order in which parameters are added to the command.

VisualBasic OleDb accessing Excel spreadsheet, can't set column in query using parameter?

I use combination of string methods and parameters, like this:

//replace field name in a query template
query = String.Format("select * from [Bid Summary$] where {0} LIKE ?", "@Gah1");

//set value (name is in OleDb parameter ignored, so it could be null)
MyCommand.SelectCommand.Parameters.AddWithValue(null, "%House%");

Note: There is possibility of a sql injection, so be sure about origin of field name (not from user input).

---

Related Topics