Using Parameters with OleDbDataAdapter in C#

c# oledb oledbparameter
Dan Champagne picture Dan Champagne · Nov 14, 2011 · Viewed 8.8k times · Source

I'm using OleDb to populate a DataTable. I'm trying to use a parameterized query, but it doesn't seem to work with a OleDbDataAdapter. Anyone have any suggestions?

cmd.CommandText = "SELECT A,B,C,D FROM someTable WHERE A=@A AND D BETWEEN @D1 AND @D2";
cmd.Parameters.Add("@A", OleDbType.VarChar).Value = "1234567";
cmd.Parameters.Add("@D1", OleDbType.DBDate).Value = "02/01/2011";
cmd.Parameters.Add("@D2", OleDbType.DBDate).Value = "01/31/2012";

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException (0x80040E11): [DB2] SQL0206N  "@A" is not valid in the context where it is used.  SQLSTATE=42703

Answer

C.Evenhuis picture C.Evenhuis · Nov 14, 2011

See http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx:

"The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters."

So you cannot use the @Parameter syntax, you have to indicate parameters with question marks, and assign your parameter values in the exact same sequence as they appear in the query.

Related questions

OleDbParameters and Parameter Names

I have an SQL statement that I'm executing through OleDb, the statement is something like this: INSERT INTO mytable (name, dept) VALUES (@name, @dept); I'm adding parameters to the OleDbCommand like this: OleDbCommand Command = new OleDbCommand(); Command.Connection = Connection; OleDbParameter …

Read More
Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

I created a windows application developed in .NET 3.5 in a 32 bit Windows 2008 server. When deployed the application in a 64 bit server it shows the error "Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine ". So as a …

Read More
Optimal way to Read an Excel file (.xls/.xlsx)

I know that there are different ways to read an Excel file: Iterop Oledb Open Xml SDK Compatibility is not a question because the program will be executed in a controlled environment. My Requirement : Read a file to a DataTable / …

Read More