Click here to Skip to main content
Click here to Skip to main content

MySqlCommand, MySqlParameter and "LIKE" with Percent Symbol

, 5 Aug 2013
Rate this:
Please Sign up or sign in to vote.
How to search text with LIKE with MySQLParameter in MySQL .NET / Connector

Introduction

Using GetSqlStringCommand with a text comparative, with LIKE, in ADO.NET and the MySqlParamenter gets you different results between executing by hand the command in a MySQL client and executing it through ADO.NET.

Background

This occurs when you write a command like this "SELECT * FROM users WHERE name LIKE '%John%'", this will return:

John Frank
Johnny Philips
H. F. John

But for ADO.NET if you set a var, like "@name" and update the command like this "SELECT * FROM users WHERE name LIKE '%@name%'", ADO.NET treats it as the string '@name' you will return 0 result, because there does not exist any user with the name @name or the email @name, but yes someone with an email of the domain "name.com", like alberto@name.com, but this is a casualty and not, what we expect.

So you need to remove the simple quota and set the value appending and preceding with "%".

Using the Code

//Wrong way
MySqlCommand cmd = oldDb.GetSqlStringCommand(CommandType.Text,"SELECT * _
FROM users WHERE name LIKE '%@name%'");
MySqlParameter nameParameter= cmd.CreateParameter();
nameParameter.DbType = DbType.String;
nameParameter.ParameterName = "@name";
nameParameter.Value = "John";

//Good way
MySqlCommand cmd = oldDb.GetSqlStringCommand(CommandType.Text,"SELECT * _
FROM users WHERE name LIKE @searchText");
MySqlParameter nameParameter= cmd.CreateParameter();
nameParameter.DbType = DbType.String;
nameParameter.ParameterName = "@searchText"
nameParameter.Value = "%John%"; 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

AlbertoLeon CSharpMan
Technical Lead Get Me An Apartment
Spain Spain
I'm developer fall in love with C#, .NET and Mono.
Designing and programming websites since 2001.
I like literature (to read and to write), playing football (soccer)... and of course I'm great fan of La Roja (Spain football team)
 
At now I work in GetMeAnApartment, I do Research and Development features to upgrade our website (asp.net, MVC, MySQL) and business model (rental short stay apartments)
 

My Knowledge:
LANGUAGES: C#, XML, HTML, JavaScript, ActionScript, php, VisualBasic Script
 
TECHNOLOGIES: asp.net, asp.net mvc, php, flash, GTK#
 
APPLICATIONS: wordpress, wikimedia, I programmed modules for Macromedia Flash MX for myself
 
FRAMEWORKS: .Net Framework, Mono
 
SERVERS: IIS, apache2
 
SO: Windows, Debian
 
SOFTWARE: Visual Studio 2010, 2008. Monodevelop. Glade. Illustrator. Indesign. Photoshop. Dreamweaver.
 

Employment:
 
GetMeAnApartment
IT Director and website developer, 2010 - present
 
Círculo Rojo Publicidad y Comunicación
IT Director and websites developer, 2007 - 2010
 
Dédalo Creative
Co-founder,Programmer and Developer, Graphical designer 2006 - 2007
 
Costa Mediterránea Comunicación
Chief writer, 2004 - 2006
 
Stalker Creacion
Web designer and developer, 2001 - 2003
 
Education
Myself
Designer and Programmer, 2001 - present
 
Jaime I, University
Advertising, 2000 - 2005
Follow on   Twitter

Comments and Discussions

 
Question@name >> @searchText Pinmvpadriancs4-Aug-13 17:28 
AnswerRe: @name >> @searchText PinmemberAlbertoLeon CSharpMan4-Aug-13 22:26 
QuestionMistype Pinmemberfunnieguy4-Aug-13 7:15 
AnswerRe: Mistype PinmemberAlbertoLeon CSharpMan4-Aug-13 7:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 5 Aug 2013
Article Copyright 2013 by AlbertoLeon CSharpMan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid