CODEWITHSUNDEEP
c#oledbsql-like
Daniel Dutra
Daniel Dutra

Reputation: 286

C# Oledb like statement not returning any results

I'm making a simple asp.net/c# application and everything with the Oledb worked just fine until now. The like statement is just not working through c#, it worked as a SQL Query in Access. I also tried just using '*a*' instead of '*@uname*' but it still didn't return anything.

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
    "SELECT accounts.ID, uname, firstname, lastname, description FROM accounts, profiles " +
    "WHERE accounts.ID = profiles.ID AND uname like '*@uname*'", connection);
dataAdapter.SelectCommand.Parameters.Add("@uname", OleDbType.VarChar).Value = tbxFilter.Text;

Upvotes: 2

Views: 7204

Answers (3)

nock
nock

Reputation: 111

Something like this works for me in my DB.

dataAdapter
    .SelectCommand
    .Parameters
    .Add(new OleDbParameter("uname", "?" + tbxFilter.Text + "?"));

Upvotes: 0

Vladimir
Vladimir

Reputation: 3689

Well, from here I can see a fast way to fix it:

WHERE accounts.ID = profiles.ID AND uname like @uname

and then your parameter should be defined like this:

dataAdapter.SelectCommand.Parameters.Add("@uname", OleDbType.VarChar).Value = "%" + tbxFilter.Text + "%"

or 

dataAdapter.SelectCommand.Parameters.Add("@uname", OleDbType.VarChar).Value = "*" + tbxFilter.Text + "*".

A side note: if I were you, I would not include the tbxFilter.Text directly. Instead, you should use this:

tbxFilter.Text.Replace("'", "''")

since a ' sign in your parameter will hurt your SQL query if not doubled. Either that or you perform this safety check on your text control's handlers.

Upvotes: 6

Oskar Kjellin
Oskar Kjellin

Reputation: 21860

The problem is that you're not using the correct wildcard character. Access can use either * or %, but most other use only %

Upvotes: 3

Related Questions