Using Parameters with OleDbDataAdapter in C#

Question

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

Are you sure your "A" column is a text field, not numeric? Your D1 and D2 parameter values should probably be dates, not strings. — LarsTech
– LarsTech, Commented Nov 14, 2011 at 15:20

C.Evenhuis · Accepted Answer · 2011-11-14 15:46:39Z

5

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.

answered Nov 14, 2011 at 15:46

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

LarsTech Over a year ago

That's true, but the parameters are in order. It should still work.

C.Evenhuis Over a year ago

The documentation says you should use question marks, so according to that, it shouldn't.

LarsTech Over a year ago

I've done it a million times. Named parameters will work on the surface, but behind the scenes, they are question marks, so the "order" is what is important. Microsoft goofed that one up pretty good.

C.Evenhuis Over a year ago

I am familiar with the Odbc and Oracle providers, they don't accept @parameter. I assumed by reading the documentation that this would be the same for OleDb, and even if it somehow works, it is never a good idea to use something that does not comply to the documentation. The next version may cause your application to crash, leaving you with a nasty bug to track.

Justin · Accepted Answer · 2018-08-27 16:30:37Z

0

For those looking for help when their query doesn't even have a parameter, check if your column names are valid.

see @TheTerribleProgrammers answer at OleDbException: no value given for parameters when no parameters are defined

answered Aug 27, 2018 at 16:30

Justin

1,5831 gold badge19 silver badges35 bronze badges

Comments

j0k · Accepted Answer · 2012-09-18 08:25:23Z

-1

public static DataTable getDataGridList(string strCmd)
    {

        openConnection(conn);
        OleDbDataAdapter DADet = new OleDbDataAdapter(strCmd, conn);
        DataTable DTDet = new DataTable();
        DADet.Fill(DTDet);
        closeConnection(conn, null);
        return DTDet;
    }

edited Sep 18, 2012 at 8:25

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Sep 18, 2012 at 7:37

aarti

1

1 Comment

Olle Sjögren Over a year ago

Welcome to SO. Please try to explain your answer in words, not just in code. What does you suggestion do and how does it differ from the code in the question?

Collectives™ on Stack Overflow

Using Parameters with OleDbDataAdapter in C#

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related