Resolved Select where

[PDS8475]

Hi

I have the following select statement
Cmd.CommandText = "Select EquipmentMake, EquipmentModel, EquipmentSerial FROM Repairs WHERE CustomerName like @Name";
but this only finds results if the Value @Name is entered exactly like it is in the database. How can I change it so the user could enter paul or Paul and it would find the same result?
I'm sure this will of been answered before but I can only find information about the use of % and _ meaning a specific character of specific minimum length.

Thanks in advance

 

[jmcilhinney]

Firstly, are you including one or more wildcards in the parameter value? If not, why are you using LIKE instead of =?

As for the issue, what database are you using? I'm not aware of a database that is case-sensitive by default when performing such comparisons. I'm not hugely experienced with every database though. maybe LIKE behaves differently to = as well.

 

[PDS8475]

Hi there are no wildcards in the parameter and I am using LIKE instead of =
I'm using a Microsoft access database.
Like you said I wasn't expecting it to be case sensitive and when I found it was case sensitive using =, I switched to LIKE thinking that would cure it. Maybe it is because the value CustomerName in the database is encrypted using AES 256bit. Therefore entering 'Paul' gives a different encrypted string to 'paul'. I hadn't thought about that and maybe I need to change it to standardize the input, So It automatically changes to 'Paul' if 'paul' is entered.

 

[NoUserHere]

Try use Cmd.CommandText = "Select EquipmentMake, EquipmentModel, EquipmentSerial FROM Repairs WHERE CustomerName like @l_name*@u_Name";
Each parameter will need to be set separate, one with capital letter, one without. The * suggests looking for either. I am no fan of Access DB, so I am unaware of any other way to do what you want. It's easier to do in modern professional DB such as MySQL DB's etc.

 

[PDS8475]

I used the line string n = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Detail_textBox.Text);
and passed n as the parameter. That works but I will have to do it on all the forms.
Thanks for the help.

 

[Skydiver]

I hate SQL and don't use it regularly, so take the following with huge grain of salt. When I used to actively use SQL and I wanted to do case insensitive matches, I would force to upper or lower case. So the SQL would be something like:

"Select EquipmentMake, EquipmentModel, EquipmentSerial FROM Repairs WHERE UPPER(CustomerName) = @Name";

And then the parameter would be prepared like:

cmd.Parameter.AddWithValue("@Name", Detarl_textBox.Text.ToUpper());

As a quick aside, I thought that Access's SQL didn't accept "@name" parameters. I thought that it only accepted "?" parameters and you needed to add the parameters in the order that the parameters are used. It's because you would typically use an OLEDB or ODBC connections to connect to MS Access databases.

As another aside, beware the Turkish I problem...

The Turkish İ Problem and Why You Should Care

Take a look at the following code.

haacked.com

 

[NoUserHere]

Skydiver you can use both, see : Using Parameters With MS Access/OleDb - VB.NET Snippets | Dream.In.Code

While it is VB.NET, the principle is the same regardless if for C#. So in the case of : Cmd.CommandText = "Select EquipmentMake, EquipmentModel, EquipmentSerial FROM Repairs WHERE CustomerName like ?*?"; which should be the same as :

Try use Cmd.CommandText = "Select EquipmentMake, EquipmentModel, EquipmentSerial FROM Repairs WHERE CustomerName like @l_name*@u_Name";

With the only difference being that the parameters need to be called in the order they are used.

Syntax should work as it is.

 

[Skydiver]

Good find! Apparently the documentation MS provides is incorrect.

 

[NoUserHere]

That is often the case why one needs to be smarter than the people writing the them docs. :/

In theory, you are also correct, since the documentation does state that the correct way; is to use nameless parameters ? and not named parameters @myParam. The difference being that both will work, but the correct preference is actually to use nameless parameters in ADO.Net, which such parameters should be executed in the order they are written in the statement. To further point out, that is irrelevant and unnecessary when using named parameters.

 

[JohnH]

Assigning a name to the parameter allow you to look that up later by its name in the collection, if you are writing code that modifies existing parameter values. That is also true for OleDbParameterCollection. The name is not used in the query as I understand, only its position.