Resolved Select where

PDS8475

Active member
Joined
Jun 25, 2019
Messages
41
Programming Experience
Beginner
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
 
Last edited:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
2,616
Location
Sydney, Australia
Programming Experience
10+
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

Active member
Joined
Jun 25, 2019
Messages
41
Programming Experience
Beginner
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.
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
844
Location
UK
Programming Experience
10+
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

Active member
Joined
Jun 25, 2019
Messages
41
Programming Experience
Beginner
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

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
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:
SQL:
"Select EquipmentMake, EquipmentModel, EquipmentSerial FROM Repairs WHERE UPPER(CustomerName) = @Name";
And then the parameter would be prepared like:
C#:
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...
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
844
Location
UK
Programming Experience
10+
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

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
Good find! Apparently the documentation MS provides is incorrect.
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
844
Location
UK
Programming Experience
10+
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

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
609
Location
Norway
Programming Experience
10+
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.
 
Top Bottom