OleDb query with positioned parameters

[melryin]

Hi,

I'm a newbie in C#, I wrote an OLEDb query with positioned parameter. It seems not working. Can anyone please point out where I've done wrong? Thanks in advance.

Here is my code:

using (OleDbConnection conn = new OleDbConnection(connString))
{
             String key = textbox1.text;
             string cmd = "Select * from [" + filename + "] Where " + fieldname + " like ?";
             OleDbCommand command= new OleDbCommand(cmd, conn);
             OleDbParameter parameter = new OleDbParameter("@key",OleDbType.VarChar,30);
             parameter.Value = "'%"+key+"%'";
             command.Parameters.Add(parameter);
             conn.Open(); 
             OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd, conn);
             OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
             DataTable table = new DataTable();         
             table.Locale = System.Globalization.CultureInfo.InvariantCulture;
             dataAdapter.Fill(table);
}

[mldisibio]

Try your code without creating the CommandBuilder. Comment out:

OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);

I am not positive, but I think that statement will overwrite your original command text.

[Curtis Rutland]

Well, the CommandBuilder line is actually doing nothing...well, nothing useful. All you are doing is instantiating a commandbuilder object, not actually using it in any way. So you should remove it.

I think that the parameter may be your problem. Try building your command like this:
Replace line 4 with this:

string cmd = String.Format("Select * from [{0}] where {1} like '%{2}%'", filename, fieldname, key);

This should take care of the filename, fieldname, and key, all at once. That will let you eliminate lines 6, 7, and 8.

[melryin]

@insertAlias
You are absolutely correct, the parameter is my problem. I tried your way.It works =D

But I use parameter method is to escape escape-characters in key, which is input by user. If I do it in this way, it won't escape.

[Curtis Rutland]

Try using named parameters instead of the ? symbols.

string sql = "select field from table where param1 = @param1"

Use the correct parameter name in the query.