By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,808 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,808 IT Pros & Developers. It's quick & easy.

OleDb query with positioned parameters

P: 5

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:

Expand|Select|Wrap|Line Numbers
  1. using (OleDbConnection conn = new OleDbConnection(connString))
  2. {
  3.              String key = textbox1.text;
  4.              string cmd = "Select * from [" + filename + "] Where " + fieldname + " like ?";
  5.              OleDbCommand command= new OleDbCommand(cmd, conn);
  6.              OleDbParameter parameter = new OleDbParameter("@key",OleDbType.VarChar,30);
  7.              parameter.Value = "'%"+key+"%'";
  8.              command.Parameters.Add(parameter);
  9.              conn.Open(); 
  10.              OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd, conn);
  11.              OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
  12.              DataTable table = new DataTable();         
  13.              table.Locale = System.Globalization.CultureInfo.InvariantCulture;
  14.              dataAdapter.Fill(table);
  15. }
Jan 5 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 190
Try your code without creating the CommandBuilder. Comment out:
Expand|Select|Wrap|Line Numbers
  1. OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
I am not positive, but I think that statement will overwrite your original command text.
Jan 5 '09 #2

Curtis Rutland
Expert 2.5K+
P: 3,256
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 5 '09 #3

P: 5
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.
Jan 6 '09 #4

Curtis Rutland
Expert 2.5K+
P: 3,256
Try using named parameters instead of the ? symbols.

Expand|Select|Wrap|Line Numbers
  1. string sql = "select field from table where param1 = @param1"
Use the correct parameter name in the query.
Jan 6 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.