OleDbAdapter is not recognizing '@' in query

Question

The following code is used to read data from excel into a gridview.

String cusid1 = maskedTextBox1.Text.ToString();
string s = "provider=Microsoft.Jet.OLEDB.4.0;data source=c:xxx.xls;Extended Properties=Excel 8.0;";
OleDbConnection con = new OleDbConnection(s); // connection string
con.Open();
string strQuery = "select * from [test$] where cusid = @cusid1";
OleDbDataAdapter da = new OleDbDataAdapter(strQuery, con);

//Or Use OleDbCommand
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];

conn.Open();

But when specify a number in place of @cusid1 like select * from [test$] where cusid=1, I am getting the correct output. If I use @cusid in the query I get this error:

No value given for one or more required parameters.

You are not adding `@cusid1` parameter to your command anywhere — user2711965, Sep 24 '14 at 13:32

score 1 · Accepted Answer · answered Sep 24 '14 at 13:37

You should pass in the parameter. Also, named parameters don't do anything in OLEDB. They are ignored. Use ? instead.

MSDN:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement

The code you need:

 string strQuery = "select * from [test$] where cusid = ?";

 OleDbCommand command = new OleDbCommand(sqlQuery, conn);
 command.Parameters.AddWithValue("?", cusid);

 da.SelectCommand = command;

OleDbAdapter is not recognizing '@' in query

1 Answers1