CODEWITHSUNDEEP
c#oledboledbparameter
Dan Champagne
Dan Champagne

Reputation: 920

Using Parameters with OleDbDataAdapter in C#

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

Upvotes: 2

Views: 9906

Answers (3)

Justin
Justin

Reputation: 1503

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

Upvotes: 0

aarti
aarti

Reputation: 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;
    }

Upvotes: -1

C.Evenhuis
C.Evenhuis

Reputation: 26446

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.

Upvotes: 5

Related Questions