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, Nov 14 '11 at 15:20

score 5 · Accepted Answer · answered Nov 14 '11 at 15:46

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 '11 at 15:46

C.Evenhuis

25,996
2
58
72

That's true, but the parameters are in order. It should still work. – LarsTech Nov 14 '11 at 15:53
The documentation says you should use question marks, so according to that, it shouldn't. – C.Evenhuis Nov 14 '11 at 15:55
1

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. – LarsTech Nov 14 '11 at 16:09
1

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. – C.Evenhuis Nov 14 '11 at 17:21

score 0 · Answer 2 · answered Aug 27 '18 at 16:30

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 '18 at 16:30

Justin

1,303
15
30

score -1 · Answer 3 · edited Sep 18 '12 at 08:25

-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 '12 at 08:25

j0k

22,600
28
79
90

answered Sep 18 '12 at 07:37

aarti

1

1

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? – Olle Sjögren Sep 18 '12 at 09:49

Using Parameters with OleDbDataAdapter in C#

3 Answers3

Linked